MySQL in a Nutshell

Name

JOIN

Synopsis

SELECT...|UPDATE...|DELETE...   
   
table [INNER|CROSS] JOIN table [ON condition|USING (column,...)]
table STRAIGHT_JOIN table
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. You 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 condition by which the tables will be joined.

As an alternative method, the USING keyword may be given along with a comma-separated list of columns within parentheses. The columns must be contained in each table that is joined. Here is an example of a JOIN:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name
   FROM employees
   JOIN branches ON branch_id = branches.rec_id
   WHERE location = 'New Orleans';

This statement will display a list of employees from the employees table who are located in the New Orleans branch office. The problem being solved with the JOIN is that the employees table doesn't ...