MySQL DQL 连接查询

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 JOIN

SELECT * FROM a, b;
+------+------+------+------+
| id   | col  | id   | col  |
+------+------+------+------+
|    1 |   11 |    2 |   22 |
|    2 |   12 |    2 |   22 |
|    1 |   11 |    3 |   23 |
|    2 |   12 |    3 |   23 |
+------+------+------+------+

# 隐式连接指定条件，相当于执行 INNER JOIN

SELECT * FROM a,b WHERE a.id=b.id;
+------+------+------+------+
| id   | col  | id   | col  |
+------+------+------+------+
|    2 |   12 |    2 |   22 |
+------+------+------+------+

# 显示执行 INNER JOIN

SELECT* 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'。

如果想使上面的查询正确执行，可以采取下面两个措施：

1. 使用括号将 t1, t2 显示组合在一起。

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

1. 避免使用逗号运算符并使用 JOIN 代替。

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

5.全外连接

全外连接写作 FULL OUTER JOIN 或简写为 FULL 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