文章目录
- 1.什么是连接查询
- 2.连接类型
- 内连接
- 交叉连接
- 左连接
- 右连接
- 自然连接
- 3.连接条件
- 4.隐式连接
- 使用逗号连接表
- 逗号与 JOIN 的优先级
- 5.全外连接
- 6.小结
- 参考文献
1.什么是连接查询
在关系型数据库管理系统（RDBMS）中，连接查询是一项重要的数据库操作，它允许我们从多个表中检索和组合数据，以便进行更复杂的查询和分析。
比如员工的个人信息存储在 employee 表中，部门相关的信息存储在 department 表中，同时 employee 表中存在一个外键字段（dept_id），引用了 department 表的主键字段。
因此，当我们想要查看员工的个人信息以及他/她所在的部门信息，就需要同时查询 employee 和 department 表中的信息。此时，我们需要使用连接查询。连接查询（JOIN）可以基于两个表中的连接字段将数据行拼接到一起，返回两表中的相关数据。
2.连接类型
MySQL 支持 SELECT 语句以及多表 DELETE 和 UPDATE 语句中使用 JOIN。
MySQL 支持多种类型的 JOIN：
- 内连接（INNER JOIN）
- 交叉连接（CROSS JOIN）
- 左连接（LEFT JOIN）
- 右连接（RIGHT JOIN）
- 自然连接（NATURAL JOIN）
不管是哪种连接，本质上都是在总的笛卡尔积下进行筛选过滤。
内连接
内连接写作 JOIN 或 INNER JOIN。
内连接返回两个表中满足连接条件的记录。
SELECT columns
FROM
table1 INNER JOIN table2 ON table1.column = table2.column;
交叉连接
交叉连接写作 CROSS JOIN。
实际上，在 MySQL 中（仅限于 MySQL）CROSS JOIN 与 JOIN 和 INNER JOIN 的表现是一样的，在不指定 ON 条件得到的结果都是笛卡尔积，反之取两个表各自匹配的结果。
SELECT columns
FROM
table1 CROSS JOIN table2;
因为交叉连接无法使用维恩图进行描述，所以这里不用维恩图（Venn Diagram）表示 CROSS JOIN 的结果。
左连接
左连接写作 LEFT JOIN 或 LEFT OUTER JOIN。
左连接返回左表中所有记录，以及与右表中满足连接条件的记录。如果右表中没有匹配的记录，对应位置将显示为 NULL。
SELECT columns
FROM
table1 LEFT JOIN table2 ON table1.column = table2.column;
右连接
右连接写作 RIGHT JOIN 或 RIGHT OUTER JOIN。
右连接与左连接类似，但是返回右表中所有记录，以及与左表中满足连接条件的记录。如果左表中没有匹配的记录，对应位置将显示为 NULL。
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
RIGHT JOIN 的工作方式与 LEFT JOIN 类似。 为了保持代码在数据库之间的可移植性，建议您使用 LEFT JOIN 而不是 RIGHT JOIN。
自然连接
使用 NATURAL 关键字与其他类型的 JOIN 组合表示自然连接。
NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN
自然连接相当于不能指定连接条件的连接，MySQL 会使用左右表内相同名字和类型的字段作为连接条件。也就是说 NATURAL JOIN 两个表，与使用 USING 子句指定两个表所有同名列的 JOIN 在语义上等价。
假设 t1 表有 i 和 j 列，t2 表有 k 和 j 列，那么下面两个 JOIN 查询是等价的：
SELECT * FROM t1 NATURAL JOIN t2;
SELECT * FROM t1 JOIN t2 USING (j);
3.连接条件
连接表我们可以用两个关键字 ON 和 USING 指定连接条件。
与 ON 一起使用的 search_condition 是可在 WHERE 子句中使用的任何形式的条件表达式。 通常，ON 子句用于指定如何连接表的条件，而 WHERE 子句则限制结果集中包含哪些行。
USING(join_column_list) 子句指定两个表中都必须存在的列的列表。
如果表 a 和 b 都包含列 c1、c2 和 c3，则以下连接分别使用 USING 和 ON 指定连接条件是等价的。
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
关于确定哪些行满足连接条件，两个连接在语义上是相同的。
但是关于确定为 SELECT * 显示哪些列，这两个联接在语义上并不相同。 USING 连接选择相应列的合并值，而 ON 连接选择所有表中的所有列。 对于 USING 连接，SELECT * 选择以下值：
COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), COALESCE(a.c3, b.c3)
对于 ON 连接，SELECT * 选择以下值：
a.c1, a.c2, a.c3, b.c1, b.c2, b.c3
4.隐式连接
使用逗号连接表
当使用逗号而不是 JOIN 连接表时，为隐式连接。
如果指定条件，相当于 INNER JOIN。如果不指定条件，相当于执行 CROSS JOIN。
以 a 和 b 表为例，测试一下。
SELECT * FROM a;
+------+------+
| id | col |
+------+------+
| 1 | 11 |
| 2 | 12 |
+------+------+SELECT * FROM b;
+------+------+
| id | col |
+------+------+
| 2 | 22 |
| 3 | 23 |
+------+------+# 隐式连接不指定条件，相当于执行 CROSS JOINSELECT * FROM a, b;
+------+------+------+------+
| id | col | id | col |
+------+------+------+------+
| 1 | 11 | 2 | 22 |
| 2 | 12 | 2 | 22 |
| 1 | 11 | 3 | 23 |
| 2 | 12 | 3 | 23 |
+------+------+------+------+# 隐式连接指定条件，相当于执行 INNER JOINSELECT * FROM a,b WHERE a.id=b.id;
+------+------+------+------+
| id | col | id | col |
+------+------+------+------+
| 2 | 12 | 2 | 22 |
+------+------+------+------+# 显示执行 INNER JOINSELECT* FROM a JOIN b ON a.id=b.id;
+------+------+------+------+
| id | col | id | col |
+------+------+------+------+
| 2 | 12 | 2 | 22 |
+------+------+------+------+
逗号是隐式连接运算符。隐式连接是SQL92中的标准内容，而在SQL99中显式连接才是标准，虽然很多人还在用隐私连接，但是它已经从标准中被移除。推荐使用显示连接，因为可以更清楚地显示多个表之间连接关系和连接依赖的属性。
逗号与 JOIN 的优先级
当逗号与 JOIN 同时使用时，JOIN 的优先级高于逗号运算符 (,)。因此，连接表达式 t1, t2 JOIN t3 被解释为 (t1, (t2 JOIN t3))，而不是 ((t1, t2) JOIN t3)。这会影响 ON 子句，因为该子句只能引用连接表中的列。
CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
INSERT INTO t3 VALUES(1, 1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
JOIN 优先于逗号运算符，因此 ON 子句的操作数是 t2 和 t3。 由于 t1.i1 不是任一操作数中的列，因此结果将报错Unknown column 't1.i1' in 'on clause'。
如果想使上面的查询正确执行，可以采取下面两个措施：
- 使用括号将 t1, t2 显示组合在一起。
SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
- 避免使用逗号运算符并使用 JOIN 代替。
SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
5.全外连接
全外连接写作 FULL JOIN 或 FULL OUTER JOIN。
外连接是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出 ON 子句条件成立的记录，然后加上左表中剩余的记录，最后加上右表中剩余的记录。
MySQL 不支持全外连接，但是我们可以对左连接和右连接的结果做 UNION 操作（会去除重复行）来实现。
6.小结
连接查询是MySQL强大而常用的功能，它允许我们从多个表中检索和组合数据，以满足复杂的查询需求。
MySQL支持多种连接类型，包括 INNER JOIN、CROSS JOIN、LEFT JOIN、RIGHT JOIN 和 NATURAL JOIN，每种类型都有其特定的用途和语法。
通过理解连接查询的基本概念和 MySQL 支持的连接类型，你可以更好地利用 MySQL 来处理复杂的数据查询和分析任务，提高数据库应用的灵活性和功能性。
参考文献
MySQL 8.0 Reference Manual :: 13.2.13.2 JOIN Clause
