Join syntax

MySQL supports the following JOIN syntaxes:

table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional-expr
table_reference LEFT [OUTER] JOIN table_reference USING (column-commalist)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional-expr }

The last example is ODBC syntax.

• A table reference may be aliased with table_reference AS alias or table_reference alias.
• , and JOIN are semantically identical. This does a full join between the used tables. One normally specifies in the WHERE condition how the tables should be linked.
• The ON conditional is any WHERE conditional. If there is no matching record for the right table in a LEFT JOIN a row with all columns set to NULL will be used for the right table.
• The USING column-list is a list of fields that must exists in both tables. A LEFT JOIN B USING (C1,C2,C3...) is defined to be semantically identical to using an ON expression A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3... .
• The NATURAL LEFT JOIN of two tables is defined to be semantically identical to a USING with all column names that exist in both tables.
• The last LEFT JOIN syntax exists only for compatibility with ODBC.

Go to the first, previous, next, last section, table of contents.