GNU Info

(mysql.info)JOIN

---

Prev: SELECT Up: SELECT

---

Enter node , (file) or (file)node

`JOIN' Syntax
.............

MySQL supports the following `JOIN' syntaxes for use in `SELECT'
statements:

     table_reference, table_reference
     table_reference [CROSS] JOIN table_reference
     table_reference INNER JOIN table_reference join_condition
     table_reference STRAIGHT_JOIN table_reference
     table_reference LEFT [OUTER] JOIN table_reference join_condition
     table_reference LEFT [OUTER] JOIN table_reference
     table_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_condition
     table_reference RIGHT [OUTER] JOIN table_reference
     table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

Where `table_reference' is defined as:
     table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)]

and `join_condition' is defined as:

     ON conditional_expr |
     USING (column_list)

You should never have any conditions in the `ON' part that are used to
restrict which rows you have in the result set.  If you want to restrict
which rows should be in the result, you have to do this in the `WHERE'
clause.

Note that in versions before Version 3.23.17, the `INNER JOIN' didn't
take a `join_condition'!

The last `LEFT OUTER JOIN' syntax shown above exists only for
compatibility with ODBC:

   * A table reference may be aliased using `tbl_name AS alias_name' or
     `tbl_name alias_name':

          mysql> select t1.name, t2.salary from employee AS t1, info AS t2
                     where t1.name = t2.name;

   * The `ON' conditional is any conditional of the form that may be
     used in a `WHERE' clause.

   * If there is no matching record for the right table in the `ON' or
     `USING' part in a `LEFT JOIN', a row with all columns set to
     `NULL' is used for the right table.  You can use this fact to find
     records in a table that have no counterpart in another table:

          mysql> select table1.* from table1
                     LEFT JOIN table2 ON table1.id=table2.id
                     where table2.id is NULL;

     This example finds all rows in `table1' with an `id' value that is
     not present in `table2' (that is, all rows in `table1' with no
     corresponding row in `table2').  This assumes that `table2.id' is
     declared `NOT NULL', of course. Note: LEFT JOIN optimization.

   * The `USING' `(column_list)' clause names a list of columns that
     must exist in both tables.  A `USING' clause such as:

          A LEFT JOIN B USING (C1,C2,C3,...)

     is defined to be semantically identical to an `ON' expression like
     this:

          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 equivalent to an `INNER JOIN' or a `LEFT JOIN' with a
     `USING' clause that names all columns that exist in both tables.

   * `RIGHT JOIN' works analogously as `LEFT JOIN'.  To keep code
     portable across databases, it's recommended to use `LEFT JOIN'
     instead of `RIGHT JOIN'.

   * `STRAIGHT_JOIN' is identical to `JOIN', except that the left table
     is always read before the right table. This can be used for those
     (few) cases where the join optimizer puts the tables in the wrong
     order.

   * As of MySQL Version 3.23.12, you can give hints about which index
     MySQL should use when retrieving information from a table. This is
     useful if `EXPLAIN' shows that MySQL is using the wrong index.  By
     specifying `USE INDEX (key_list)', you can tell MySQL to use only
     one of the specified 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.

Some examples:

     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;

Note: `LEFT JOIN' optimization.