(mysql.info.gz) JOIN

 
 13.1.7.1 `JOIN' Syntax
 ......................
 
 MySQL supports the following `JOIN' syntaxes for the TABLE_REFERENCES
 part of `SELECT' statements and multiple-table `DELETE' and `UPDATE'
 statements:
 
      TABLE_REFERENCE, TABLE_REFERENCE
      TABLE_REFERENCE [INNER | CROSS] JOIN TABLE_REFERENCE [JOIN_CONDITION]
      TABLE_REFERENCE STRAIGHT_JOIN TABLE_REFERENCE
      TABLE_REFERENCE LEFT [OUTER] JOIN TABLE_REFERENCE [JOIN_CONDITION]
      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 NATURAL [RIGHT [OUTER]] JOIN TABLE_REFERENCE
 
 TABLE_REFERENCE is defined as:
 
      TBL_NAME [[AS] ALIAS]
          [[USE INDEX (KEY_LIST)]
            | [IGNORE INDEX (KEY_LIST)]
            | [FORCE INDEX (KEY_LIST)]]
 
 JOIN_CONDITION is defined as:
 
      ON CONDITIONAL_EXPR | USING (COLUMN_LIST)
 
 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.
 
    * A table reference can 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;
           mysql> SELECT t1.name, t2.salary FROM employee t1, info t2
               ->        WHERE t1.name = t2.name;
 
    * The `ON' conditional is any conditional expression of the form
      that can 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'.   `LEFT JOIN' optimization LEFT JOIN
      optimization.
 
    * The `USING' `(column_list)' clause names a list of columns that
      must exist in both tables. The following two clauses are
      semantically identical:
 
           a LEFT JOIN b USING (c1,c2,c3)
           a LEFT JOIN b ON 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.
 
    * `INNER JOIN' and `,' (comma) are semantically equivalent in the
      absence of a join condition: both will produce a Cartesian product
      between the specified tables (that is, each and every row in the
      first table will be joined onto all rows in the second table).
 
    * `RIGHT JOIN' works analogously to `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 for which the join optimizer puts the tables in the
      wrong order.
 
 
 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 will only
 be 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'.
 
 * `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 will be
 used when resolving an `ORDER BY' or `GROUP BY'.
 
 Some join 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;
 
  `LEFT JOIN' optimization LEFT JOIN optimization.
Info Catalog
(mysql.info.gz) SELECT
(mysql.info.gz) UNION
automatically generated byinfo2html