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

<PRE class=programlisting>table_reference, table_referencetable_reference [INNER | CROSS] JOIN table_reference [join_condition]table_reference STRAIGHT_JOIN table_referencetable_reference LEFT [OUTER] JOIN table_reference join_conditiontable_reference NATURAL [LEFT [OUTER]] JOIN table_reference{ OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr }table_reference RIGHT [OUTER] JOIN table_reference join_conditiontable_reference NATURAL [RIGHT [OUTER]] JOIN table_reference</PRE>

table_reference is defined as:

<PRE class=programlisting>tbl_name [[AS] alias] [[USE INDEX (key_list)] | [IGNORE INDEX (key_list)] | [FORCE INDEX (key_list)]]</PRE>

join_condition is defined as:

<PRE class=programlisting>ON conditional_expr | USING (column_list)</PRE>

You should generally not have any conditions in the ON part that are used to restrict which rows you want in the result set, but rather specify these conditions in the WHERE clause. There are exceptions to this rule.

Note that INNER JOIN syntax allows a join_condition only from MySQL 3.23.17 on. The same is true for JOIN and CROSS JOIN only as of MySQL 4.0.11.

The { OJ ... LEFT OUTER JOIN ...} syntax shown in the preceding list exists only for compatibility with ODBC.

As of MySQL 3.23.12, you can give hints about which index MySQL should use when retrieving information from a table. By specifying USE INDEX (key_list), you can tell MySQL to use only one of the possible indexes to find rows in the table. The alternative syntax IGNORE INDEX (key_list) can be used to tell MySQL to not use some particular index. These hints are useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes.

From MySQL 4.0.9 on, you can also use FORCE INDEX. This acts likes USE INDEX (key_list) but with the addition that a table scan is assumed to be very expensive. In other words, a table scan is only used if there is no way to use one of the given indexes to find rows in the table.

USE KEY, IGNORE KEY, and FORCE KEY are synonyms for USE INDEX, IGNORE INDEX, and FORCE INDEX.

Note: USE INDEX, IGNORE INDEX, and FORCE INDEX only affect which indexes are used when MySQL decides how to find rows in the table and how to do the join. They do not affect whether an index is used when resolving an ORDER BY or GROUP BY.

Some join examples:

<PRE class=programlisting>mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id -> LEFT JOIN table3 ON table2.id=table3.id;mysql> SELECT * FROM table1 USE INDEX (key1,key2) -> WHERE key1=1 AND key2=2 AND key3=3;mysql> SELECT * FROM table1 IGNORE INDEX (key3) -> WHERE key1=1 AND key2=2 AND key3=3;</PRE>

See Section 7.2.9, “How MySQL Optimizes LEFT JOIN and RIGHT JOIN”.