Can anyone point me to documentation describing the concepts behind MySQL's different join types?
I've got a lot of experience with DB2 but I want to do four things:
a) verify that 'natural join', 'left outer join', and 'right outer join' mean the same in both MySQL and DB2.
b) determine the meaning of 'straight join', 'inner join', and 'cross join' in MySQL. We don't use these in DB2 or at least not by those names.
c) understand what it means to combine 'natural' with 'left', 'right', and 'outer' joins, e.g. determine the difference between LEFT OUTER JOIN and NATURAL LEFT OUTER JOIN.
d) determine how to write a FULL OUTER JOIN in MySQL.
In DB2, a natural join is a join where the result set contains the concatenation of rows in the right hand table whose non-null joining key matches joining keys in the left hand table. In DB2, a right (outer) join contains all the results of the natural join PLUS all the 'orphan' rows from the right hand table, i.e. those rows from the right hand table that didn't have a match in the left hand table.
In DB2, a left (outer) join contains all the results of the natural join PLUS all the 'orphan' rows from the left hand table, i.e. those rows from the left hand table that didn't have a match in the right hand table.
In DB2, a full (outer) join contains all the results of the natural join PLUS all the orphan rows from the right hand table PLUS all the orphan rows from the left hand table.
I've tried to find descriptions of these concepts in the MySQL manual to compare how these terms are used in MySQL but I can't find them; all I can find are discussions of the syntax for each. I'd like to clarify the terms rather than blindly assuming that MySQL uses these terms in exactly the same way as DB2. I'd also like to determine if inner joins, cross joins, and natural joins are synonyms for one another in MySQL.
By the way, I realize that both MySQL and DB2 are probably getting their join types from the same standard but:
a) I'm not sure of that
b) if it is true, I'm not sure which standard they are using
c) I'm not sure where any documentation of that standard is
d) I'm not sure if MySQL has its own extensions above and beyond the standard
Therefore, I'd really prefer to read MySQL documentation on this subject to be sure of exactly how MySQL works rather than how it a third party standard suggests that it should work.
http://www.mysql.com/doc/en/JOIN.html
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
