MySQL JOIN Summary

1. Join syntax (after refinement)

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 Category: Inner connection, outer connection.

2.1 Internal connection (INNER join)

A. In MySQL, join, cross join, and INNER join syntax are equivalent; Without specifying an on condition, the resulting Cartesian product (that is, each and every row in the first table was joined to each and every row in the second table).

B. An inner join (INNER join), or equivalent connection, that shows only records that meet the criteria.

In the case of tables Left_tbl and RIGHT_TBL, there are two forms of connection within the representation: Left_tbl INNER JOIN right_tbl, and Left_tbl, (comma) right_tbl. The two forms are syntactically equivalent, but join takes precedence over, (comma operator), which can produce errors when mixed, preferably with join.

Two representations of inner joins correspond to two ways of acquiring data: on, and where. When using on, it can also be rewritten as, (comma). However, on shows how to complete join operations between tables, where it is only in the result set to filter out records that satisfy the criteria, meaning is still different.

2.2 External connection (OUTER join)

A. There are two forms of outer joins: left | Right [OUTER] JOIN (OUTER can be omitted), commonly used to get data in table LEFT_TBL, not 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 table A, and B have the same fields, such as C1, C2, C3, then the following two types of notation 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), and a NATURAL left join B gets a slightly different result set, which removes the repeating field. Here NATURAL temporarily as a key word, can be used in conjunction with the internal and external connection, NATURAL join is characterized by the same name segment connected, and to go heavy. It has little practical effect.

This article is from the "Move Yourself" blog, please be sure to keep this source http://coveringindex.blog.51cto.com/12533373/1953568

MySQL JOIN Summary