MySQL supports the following JOIN syntaxes for the table_references
part of SELECT statements and multiple-table DELETE and UPDATE
statements:

table_references:
    table_reference [, table_reference] ...

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [[AS] alias] [index_hint)]
  | ( table_references )
  | { OJ table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }

join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON condition
  | table_reference LEFT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
  | table_reference RIGHT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

join_condition:
    ON conditional_expr
  | USING (column_list)

index_hint:
    USE {INDEX|KEY} [FOR JOIN] (index_list)]
  | IGNORE {INDEX|KEY} [FOR JOIN] (index_list)]
  | FORCE {INDEX|KEY} [FOR JOIN] (index_list)]

index_list:
    index_name [, index_name] ...

A table reference is also known as a join expression.

The syntax of table_factor is extended in comparison with the SQL
Standard. The latter accepts only table_reference, not a list of them
inside a pair of parentheses.

This is a conservative extension if we consider each comma in a list of
table_reference items as equivalent to an inner join. For example:

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

is equivalent to:

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

In MySQL, CROSS JOIN is a syntactic equivalent to INNER JOIN (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.

In versions of MySQL prior to 5.0.1, parentheses in table_references
were just omitted and all join operations were grouped to the left. In
general, parentheses can be ignored in join expressions containing only
inner join operations. As of 5.0.1, nested joins are allowed (see
http://dev.mysql.com/doc/refman/5.0/en/nested-joins.html).

Further changes in join processing were made in 5.0.12 to make MySQL
more compliant with standard SQL. These charges are described later in
this section.

Index hints can be specified to affect how the MySQL optimizer makes
use of indexes. For more information, see
http://dev.mysql.com/doc/refman/5.0/en/index-hints.html.

URL: http://dev.mysql.com/doc/refman/5.0/en/join.html

