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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql