MySQL JOIN Summary

1. JOIN syntax (simplified)

Table_reference:

Table_factor

| | join_table |

Table_factor:

Table_subquery [AS] alias

| (table_references)

Join_table:

Table_reference [INNER | CROSS] JOIN table_factor [join_condition]

| | table_reference {LEFT | RIGHT} [OUTER] JOIN table_reference join_condition |

| | table_reference NATURAL [{LEFT | RIGHT} [OUTER]] JOIN table_factor |

Join_condition:

ON conditional_expr

| | USING (column_list) |

2. JOIN classification: inner connection, outer connection.

2.1 Internal connection (INNER JOIN)

a. In MySQL, the syntax of JOIN, CROSS JOIN and and INNER JOIN is equivalent; without specifying the ON condition, you get the Cartesian product (That is, each and every row in the first table is joined to each and every row in the second table).

b. Internal connections (INNER JOIN), or equivalent connections, display only records that meet the criteria.

If the tables left_tbl and right_tbl, there are two forms when expressing inner joins: left_tbl INNER JOIN right_tbl, and left_tbl, (comma) right_tbl. The two forms are syntactically equivalent, but the priority of JOIN is higher than that of comma operator, and errors may occur when mixed, so it is best to use JOIN.

The two forms of internal connection correspond to two ways of obtaining data: ON, and WHERE. When using ON, it can also be rewritten to, (comma). But ON shows how to complete the JOIN operation between tables, while WHERE only filters out the records that meet the conditions in the result set, the meaning is still different.

2.2 external connection (OUTER JOIN)

a. There are two forms of external joins: LEFT | RIGHT [OUTER] JOIN (OUTER can be omitted), which is commonly used to obtain data in table left _ tbl rather than in table right_tbl.

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

b. If tables an and b have the same fields, such as C1, c2, and c3, then the following two ways of writing are equivalent:

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

c. For a LEFT JOIN b USING (C1, c2, c3), there is a slight difference from the result set obtained by a NATURAL LEFT JOIN b, which removes duplicate fields. Here NATURAL is temporarily regarded as a keyword, which can be used in combination with internal and external connections. The characteristic of NATURAL JOIN is that it connects with the name field and removes duplicates. Its practical effect is not great.