MENU


JOIN

table_references: escaped_table_reference [, escaped_table_reference] ...
escaped_table_reference:     table_reference
| { OJ table_reference }
table_reference:     table_factor
| join_table
table_factor: tbl_name [PARTITION (partition_names)]
[[AS] alias] [index_hint_list]
| table_subquery [AS] alias
| ( table_references )
join_table: table_reference [INNER | CROSS] JOIN table_factor
[join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON
conditional_expr
| table_reference {LEFT|RIGHT} [OUTER] JOIN
table_reference join_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN
table_factor
join_condition: ON conditional_expr
| USING (column_list)
index_hint_list:     index_hint [, index_hint] ...
index_hint:     USE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| IGNORE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
| FORCE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
index_list:     index_name [, index_name] ...
JOIN, CROSS JOIN, and INNER JOIN are the same in MySQL. They yield a Cartesian product between the specified tables. Each row in the first table is joined to each row in the second table. For instance:

SELECT * FROM t1 JOIN (t2, t3) ON(t2.a=t1.a AND t3.a=t2.a) results in a new table formed by joining t1, t2, and t3 where t1.a, t2.a, and t3.a are the same.
For a LEFT JOIN, if there is no matching row for the right table in the ON or USING part, a row with all columns set to NULL will be used for the right table. RIGHT JOIN is identical, but works in the other way round.
USING compares columns of the same name in both tables, and joins the rows when the values are the same.
A NATURAL [LEFT] JOIN is the same as an INNER JOINor LEFT JOIN with a USING clause that names all columns that exist in both tables.
The {OJ…} syntax is meant for compatibility with ODBC. The curly brace should be specified literally.
STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table.