>>>> 2012/08/22 17:38 -0400, Shawn Green >>>>
MySQL does not have a simple OUTER JOIN command (some RDBMSes call this a FULL OUTER
JOIN). What we do have is the option to include the OUTER keyword into our LEFT or RIGHT
joins. For example, both of these are acceptable:
LEFT OUTER JOIN
LEFT JOIN
Also, you need a space between "inner" and "join" as in INNER JOIN.
>If you want only the most restricitive criteria that match resultsets from both select
> statements use INNER JOIN
>if you want all results from both resultsets (cartesian JOIN) use OUTER JOIN
Again, MySQL does not have a plain OUTER JOIN. If you want a full Cartesian product of two
tables, use the 'comma join' syntax with no criteria for matching the tables in the WHERE
clause.
SELECT ... FROM tableA, tableB WHERE ....
Syntax details are located here:
http://dev.mysql.com/doc/refman/5.5/en/join.html
<<<<<<<<
which last is the same as
SELECT ... FROM tableA JOIN tableB WHERE ....
; that is, if there is no join_condition that, too, becomes a cross-join.
From the webpage:
< In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can
replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an
ON clause, CROSS JOIN is used otherwise. >
That is, in MySQL neither INNER nor CROSS has any meaning. If there is no restriction
(ON/USING/NATURAL) it is CROSS JOIN; otherwise, something else. As for OUTER, in MySQL it
is a word that may appear between LEFT/RIGHT and JOIN; it is not really meaningful; as
Shawn Green said, MySQL has no full outer join (but Oracle has!).
Therefore, in case of syntactical parsimony, whereto, it seems to me, MySQL is headed, one
writes JOIN by itself with restrictions or not (for cross join), or after one of the words
LEFT, RIGHT(, OUTER when it is implemented) with restrictions.
In case of syntactical security (the standard?), one always qualifys JOIN: with CROSS if
one intends no restriction, else with INNER or OUTER, and qualifys the latter with LEFT
or RIGHT if one intends other than full outer join.
