\h join
Name: 'JOIN'
Description:
MySQL supports the following JOIN syntax for the table_references part
of SELECT statements and multiple-table DELETE and UPDATE statements:

table_references:
    escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference:
    table_reference
  | { OJ table_reference }

table_reference:
    table_factor
  | joined_table

table_factor:
    tbl_name [PARTITION (partition_names)]
        [[AS] alias] [index_hint_list]
  | table_subquery [AS] alias
  | ( table_references )

joined_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_specification]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON search_condition
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

join_specification:
    ON search_condition
  | USING (join_column_list)

join_column_list:
    column_name [, column_name] ...

index_hint_list:
    index_hint [, index_hint] ...

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

index_list:
    index_name [, index_name] ...

A table reference is also known as a join expression.

A table reference (when it refers to a partitioned table) may contain a
PARTITION option, including a list of comma-separated partitions,
subpartitions, or both. This option follows the name of the table and
precedes any alias declaration. The effect of this option is that rows
are selected only from the listed partitions or subpartitions. Any
partitions or subpartitions not named in the list are ignored. For more
information and examples, see
https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html.

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

This is a conservative extension if each comma in a list of
table_reference items is considered 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, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents
(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 general, parentheses can be ignored in join expressions containing
only inner join operations. MySQL also supports nested joins. See
https://dev.mysql.com/doc/refman/5.6/en/nested-join-optimization.html.

Index hints can be specified to affect how the MySQL optimizer makes
use of indexes. For more information, see
https://dev.mysql.com/doc/refman/5.6/en/index-hints.html. The
optimizer_switch system variable is another way to influence optimizer
use of indexes. See
https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html.

URL: https://dev.mysql.com/doc/refman/5.6/en/join.html

Examples:
SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

Index hints can be specified to affect how the MySQL optimizer makes
use of indexes. For more information, see
https://dev.mysql.com/doc/refman/5.6/en/index-hints.html. The
optimizer_switch system variable is another way to influence optimizer
use of indexes. See
https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html.

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents
(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.

SELECT * FROM tbl;
+------+------+
| id   | col  |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    2 |    2 |
+------+------+
3 rows in set (0.00 sec)

SELECT * FROM tbl a CROSS JOIN tbl b ON a.id=b.col;
+------+------+------+------+
| id   | col  | id   | col  |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    1 |    1 |    2 |    1 |
|    2 |    1 |    2 |    2 |
|    2 |    2 |    2 |    2 |
+------+------+------+------+
4 rows in set (0.00 sec)

SELECT * FROM tbl a CROSS JOIN tbl b WHERE a.id=b.col;
+------+------+------+------+
| id   | col  | id   | col  |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    1 |    1 |    2 |    1 |
|    2 |    1 |    2 |    2 |
|    2 |    2 |    2 |    2 |
+------+------+------+------+
4 rows in set (0.01 sec)

SELECT * FROM tbl a INNER JOIN tbl b;
+------+------+------+------+
| id   | col  | id   | col  |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    2 |    1 |    1 |    1 |
|    2 |    2 |    1 |    1 |
|    1 |    1 |    2 |    1 |
|    2 |    1 |    2 |    1 |
|    2 |    2 |    2 |    1 |
|    1 |    1 |    2 |    2 |
|    2 |    1 |    2 |    2 |
|    2 |    2 |    2 |    2 |
+------+------+------+------+
9 rows in set (0.00 sec)

SELECT (SELECT 1);
+------------+
| (SELECT 1) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

SELECT (SELECT 1,2);
ERROR 1241 (21000): Operand should contain 1 column(s)

SELECT(SELECT 1 UNION SELECT 1);
+---------------------------+
| (SELECT 1 UNION SELECT 1) |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

SELECT(SELECT 1 UNION ALL SELECT 1);
ERROR 1242 (21000): Subquery returns more than 1 row

SELECT * FROM tbl;
+------+
| col  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

SELECT col FROM tbl WHERE col=(SELECT 1);
+------+
| col  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

SELECT col FROM tbl WHERE col=ANY(SELECT 1 UNION SELECT 2);
+------+
| col  |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

SELECT col FROM tbl WHERE col IN (SELECT 1 UNION SELECT 2);
+------+
| col  |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

SELECT a.col,b.col FROM tbl a,tbl b WHERE (a.col,b.col)=(SELECT 2,3);
+------+------+
| col  | col  |
+------+------+
|    2 |    3 |
+------+------+
1 row in set (0.00 sec)