List:General Discussion« Previous MessageNext Message »
From:hsv Date:August 23 2012 1:11am
Subject:Re: JOIN, JOIN, JOIN
View as plain text  
>>>> 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. 

Thread
help with correlated subqueryLarry Martell21 Aug
  • RE: help with correlated subqueryRick James21 Aug
    • Re: help with correlated subqueryLarry Martell22 Aug
  • Re: help with correlated subqueryhsv22 Aug
    • Re: help with correlated subqueryLarry Martell22 Aug
      • RE: help with correlated subqueryRick James22 Aug
      • Re: help with correlated subqueryLarry Martell23 Aug
        • Re: help with correlated subqueryLarry Martell23 Aug
Re: help with correlated subqueryLarry Martell22 Aug
RE: help with correlated subqueryMartin Gainty22 Aug
  • Re: help with correlated subqueryShawn Green22 Aug
    • Re: JOIN, JOIN, JOINhsv23 Aug