I believe that I have found the following SQL join "commands" ("command" is not the correct term I believe. What is (keyword??)?) to exist;
JOIN
CROSS JOIN
INNER JOIN
OUTER JOIN
NATURAL JOIN
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.

What type of join is JOIN?
Thanks

Are you looking for ANSI SQL answers?

For all implementations I know:
JOIN == INNER JOIN
LEFT\RIGHT\FULL JOIN == LEFT\RIGHT\FULL OUTER JOIN

pootle flump ur codings are working excelent.

in some versions of mysql, JOIN == CROSS JOIN, and in others, INNER JOIN == CROSS JOIN if no ON clause is specified

both of the above are non-standard

poots was correct, there are only these types of join:

CROSS JOIN
[NATURAL] INNER JOIN
[NATURAL] LEFT [OUTER] JOIN
[NATURAL] RIGHT [OUTER] JOIN
[NATURAL] FULL [OUTER] JOIN

NATURAL means no ON clause is used, but almost everyone advises against using NATURAL

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;
,
CROSS JOIN

** 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 JOIN
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;
FULL
LEFT
RIGHT

**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.

NATURAL JOIN means join on the foreign keys, not attributes with common names.

How an RDBMS handles exceptions depends on the RDBMS.

pootle flump ur codings are working excelent.

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.

Not so: it does indeed mean join on attributes with common names!

Oops - it's been a while - I misremembered. Apols

pootle flump ur codings are working excelent.

which is why it's so dangerous, and not to be trusted in any sane person's database

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 call it the ON clause

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

p.s. you've apparently missed out on the USING clause in joins -- but, like NATURAL, you shouldn't be using USING either

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.

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.

see http://www.dbforums.com/t997991.html for an example

OK, I'm convinced!

Nice archiving, big cat

pootle flump ur codings are working excelent.