I believe that I have found the following SQL join "commands" ("command" is not the correct term I believe. What is (keyword??)?) to exist;
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
When using the qualifiers LEFT, RIGHT or FULL can you leave the qualifier OUTER out as it is implied?
Is this list complete or are there other joins possible? Are there errors in my list? Please add to, delete or edit my list as appropriate.
Thanks a lot for your time. My experiments have yielded the following;
** The following are both equivalent in their effect. They both result in a cartesian multiplication of two relations. They can only be used without an ON clause;
** NATURAL joins look for attributes in each relation with the same name and type and join on this common attribute. If there is a common attribute, NATURAL joins then only join rows where a common value in the common attribute exists. If no commonly named and typed attributes exist, the NATURAL join performs a cartesian multiplication of the two relations. NATURAL joins cannot be used in conjunction with an ON clause. These are the possible NATURAL join variants;
NATURAL INNER JOIN
NATURAL LEFT JOIN
NATURAL LEFT OUTER JOIN
NATURAL RIGHT JOIN
NATURAL RIGHT OUTER JOIN
NATURAL FULL JOIN
NATURAL FULL OUTER JOIN
(I haven't yet worked out what happens if there are two or more common attributes)
** The following clauses must be used in combination with an ON clause. These clauses have equivalents as detailed;
LEFT JOIN == LEFT OUTER JOIN
RIGHT JOIN == RIGHT OUTER JOIN
FULL JOIN == FULL OUTER JOIN
JOIN == INNER JOIN
** The clauses JOIN and INNER JOIN cannot be combined with the clauses RIGHT, LEFT or FULL.
** It seems the OUTER clause is totally redundant. However if it is used it must be used in combination with one of the following clauses;
**Thus the clause OUTER JOIN when used without FULL, LEFT or RIGHT always results in an error
**The clause NATURAL OUTER JOIN always results in an error
** These "things" are not commands, they are clauses. Clauses are also keywords.
How am I doing? I am sure there are some mistakes. There is a lot more to JOIN clauses than first meets the eye.
PS - I am using postgreSQL, the most awkwardly named application I have yet to come across.
So NATURAL JOIN does nothing unless primary and foreign keys have been explicitly defined? I did some experiments where I created two simple tables each with 2 attributes. Each table had one attribute in common (same name and type). I defined no keys explicitly.The NATURAL JOIN seemed to automatically recognize the common attributes and created the join on it.
So it seems it is preferable to use the ON operator/clause (what are these "things" called?) and explicitly define what attributes joins are to be based upon. Where do keys come in? Even when using ON I believe you need to specify the attributes exactly. Can the use of keys somehow negate the need to explicitly define the attributes within the boolean expression that follows ON?
i don't understand your question "where do keys come in" because you are allowed to write joins to join on whatever columns you want
I think eneunu means, which JOIN type works by fuguring out approriate joins from the declared forieign key constraints. To which the answer is: none.
Originally Posted by r937
p.s. you've apparently missed out on the USING clause in joins -- but, like NATURAL, you shouldn't be using USING either
I'm totally with you on NATURAL being seriously dangerous - but why USING? You are explicitly naming the join columns, so there is no ambiguity. Not that I have used it more than a couple of times, ever.
II'm totally with you on NATURAL being seriously dangerous - but why USING? You are explicitly naming the join columns, so there is no ambiguity. Not that I have used it more than a couple of times, ever.