MySQL in a Nutshell, 2nd Edition

Name

JOIN

Synopsis

SELECT...|UPDATE...|DELETE...    
table [INNER|CROSS] JOIN table [ON condition|USING (column[,...])] |
table STRAIGHT_JOIN table ON condition |
table LEFT [OUTER] JOIN table {ON condition|USING (column[,...])} |
table NATURAL [LEFT [OUTER]] JOIN table |
[OJ table LEFT OUTER JOIN table ON condition] |
table RIGHT [OUTER] JOIN table {ON condition|USING (column[,...])} |
table NATURAL [RIGHT [OUTER]] JOIN table

The JOIN clause is common to several SQL statements (SELECT, UPDATE, DELETE) and is complex; therefore, it is listed here as its own entry in the chapter. Use JOIN to link tables together based on columns with common data for purposes of selecting, updating, or deleting data. The JOIN clause is entered at the place in the relevant statement that specifies the tables to be referenced. This precludes the need to join the tables based on key columns in the WHERE clause.

The ON keyword is used to indicate the pair of columns by which the tables are to be joined (indicated with the equals sign operator). As an alternative method, the USING keyword may be given along with a comma-separated list of columns both tables have in common, contained within parentheses. The columns must exist in each table that is joined. To improve performance, you can also provide index hints to MySQL (see the last subsection of this clause definition, Index hints”).

Here is an example of a JOIN:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name FROM employees JOIN branches ON employees.branch_id ...