“

教程所用示例表：关注并回复关键字“sql教程示例数据”即可获取，需要的朋友请自取。

引言

在数据库管理领域，处理多个表的能力是任何SQL从业者必不可少的技能。无论您是经验丰富的开发人员还是初学者，精通在不同表之间导航和操作数据的艺术都是发挥SQL全部潜力的关键。

在本教程中，我们将深入探讨处理多个表的复杂性，探索各种技术和策略，以有效地管理关系数据。通过一系列深入讨论和实际示例，我们将涵盖各种主题，比如数据合并、连接等。

本教程的每个部分都将专注于处理多个表的特定方面，为您提供宝贵的见解和实用的指导，以增强您的SQL熟练度。通过本教程的学习，您将掌握处理涉及多个表的任何SQL挑战所需的知识和技能。

因此，无论您是希望优化数据库性能、简化数据分析，还是增强数据完整性，都请加入我们，共同探索在SQL中处理多个表的奥秘。让我们一起踏上这段启迪之旅，揭开关系数据库的全部潜力。

结果集合并（UNION ALL）

• 问题：假设你想返回的数据存储在多个表中，从概念上讲就是将一个结果集堆叠到另一个结果集上。这些表不需要具有共同的键，但是它们的列必须具有相同的数据类型。例如，显示emp表中10号部门的所有员工姓名和部门编号，同时还有dept表中的所有部门名称和部门编号。
• 实现：使用UNION ALL即可实现多个结果集的合并。

SELECT eName AS ename_and_dname, deptNo
FROM emp
WHERE deptNo = 10
UNION ALL
SELECT '-----------------', NULL
FROM t1     -- 只含有一列（id）一行（1）数据的辅助表
UNION ALL
SELECT dName, deptNo
FROM dept;

• 解释：UNION ALL用于将多个结果集合并为一个。不过需要注意，UNION ALL连接的两个SELECT列表的字段数量和数据类型必须匹配，否则会报错。此外，请记住，UNION ALL返回的结果会包含重复行（如果存在），如果你想过滤重复行，仅使用去掉UNION即可。但是，如果对重复行没有要求的话，尽量使用UNION ALL，因为UNION操作会涉及到排序操作，这会影响查询性能，特别是对于大型数据集的处理。

合并相关行（JOIN）

• 问题：假设你想通过连接已知的公共列或者具有共同值的列从多个表中查询数据。例如，您想显示部门10中所有员工的姓名，以及每个员工所在部门的位置，但这些数据存储在两个单独的表中。
• 实现：

SELECT e.eName, d.loc
FROM emp e,
     dept d
WHERE e.deptNo = d.deptNo
  AND e.deptNo = 10;

• 解释：上述实现是连接的一个示例，更准确地说是等值连接，它是内连接的一种类型。连接是将两个表的结果集合并成一个的操作。等值连接是一种基于相等条件的连接（例如，其中一个部门编号等于另一个）。内连接是最原始的连接类型，它返回的每一行都是满足连接条件的每个表的数据。采用内连接的实现如下：

SELECT e.eName, d.loc
FROM emp e
         INNER JOIN dept d ON e.deptNo = d.deptNo
WHERE e.deptNo = 10;

找到两个表的公共行（交集）

• 问题：假设你希望找到两个表之间的公共行，但是有多个列可供您连接。例如，考虑以下根据emp表创建的视图V：

DROP VIEW IF EXISTS V;
CREATE VIEW V
AS
SELECT eName, job, sal
FROM emp
WHERE job = 'CLERK';

SELECT * FROM V;

视图V中只包含三列，但是现在需要返回员工表中所有与视图V匹配的所有员工的员工号、姓名、职位、薪水和部门号。

• 实现：可以通过在所有必要的列上连接表来返回正确的结果。或者，使用集合操作INTERSECT来避免执行连接，返回两个表的交集（公共行）。

MySQL，SQL Server

SELECT e.empNo, e.eName, e.job, e.sal, e.deptNo
FROM emp e
         INNER JOIN V
                    ON e.eName = V.eName
                        AND e.job = V.job
                        AND e.sal = V.sal;

MySQL和SQL Server的实现方法也同样适用于DB2、Oracle和PostgreSQL。但是，如果你不需要返回视图中的任何字段的话，DB2、Oracle和PostgreSQL还支持集合操作，可以使用取交集的方法（INTERSECT）获取两个结果集的公共部分。

SELECT EMPNO, ENAME, JOB, MSAL, DEPTNO
FROM EMPLOYEES
WHERE (ENAME, JOB, MSAL) IN (SELECT ENAME, JOB, MSAL
                             FROM EMPLOYEES
                             INTERSECT
                             SELECT ENAME, JOB, MSAL
                             FROM V_emp);

• 解释：在执行连接时，为了确保返回正确的结果，必须考虑正确的列来连接。当要返回的行在某些列上具有共同值，而对其他列却具有不同值时，这一点尤为重要。
  
  集合操作INTERSECT将返回两个结果集的公共部分。与UNION ALL类似，在使用INTERSECT时，需要两个结果集的字段数量及数据类型相同。请记住，在使用集合操作时，默认情况下不会返回重复行。

检索在A表却不在B表中的数据（差集）

• 问题：找出部门表dept中哪个部门并不在员工表emp中，根据前面的表数据，我们知道部门40并不在员工表中，所以应该返回的是40号部门。
• 实现：DB2、PostgreSQL、SQL Server和Oracle有专门的函数支持集合运算，因此可以使用差集方法实现。

-- DB2, PostgreSQL, and SQL Server
SELECT deptNo FROM dept
EXCEPT
SELECT deptNo FROM emp;

-- Oracle
SELECT deptNo FROM dept
MINUS
SELECT deptNo FROM emp;

MySQL没有专门的函数支持集合操作，因此可以通过子查询实现：

SELECT deptNo
FROM dept
WHERE deptNo NOT IN (SELECT DISTINCT deptNo FROM emp);

• 解释：EXCEPT和MINUS都是集合运算中的差集，用第一个结果集减去第二个结果集，实现简单方便。但是，这种方法也有其限制，那就是两个SELECT列表中需要比较的值的数据类型和数量必须匹配。

  注意，在使用NOT IN时，要小心比较的列存在空值（NULL）的情况。考虑下面的表（new_dept）：

DROP TABLE IF EXISTS new_dept;
CREATE TEMPORARY TABLE new_dept(deptNo INT);
INSERT INTO new_dept VALUES (10), (50), (NULL);

此时，如果将上面MySQL实现中的部门表换成new_dept的话，那将不会有任何行返回。

SELECT deptNo
FROM dept
WHERE deptNo NOT IN (SELECT DISTINCT deptNo FROM new_dept);

原则上，应该返回部门号20、30和40，因为这三个部门都不在new_dept中，但是结果却什么都没有返回，为什么呢？原因就在于new_dept表中存在空值（NULL）。子查询返回了三行，分别是10、50和NULL。IN和NOT IN本质上是OR操作，我们都知道正常数学中的逻辑判断是二值逻辑（真或假），但是在SQL中却是三值逻辑（真、假和未知），任何值（包括空值本身）与空值（NULL）比较的结果都是未知。OR的真值表中 false OR unknown的结果为unknown，所以上面的查询没有任何行返回。

那要避免NOT IN中含有空值的问题，该如何实现才能返回正确的结果呢？可将相关子查询与NOT EXISTS结合使用。术语相关子查询指的是外部查询中的行会在子查询中被引用。以下示例是一个替代方案，不会受到空值行的影响：

SELECT d.deptNo
FROM dept d
WHERE NOT EXISTS(
        SELECT 1
        FROM new_dept nd
        WHERE d.deptNo = nd.deptNo
    );

从A表中检索在B表不存在对应行的数据（条件连接）

• 问题：找出哪个部门没有员工。
• 实现：使用左连接或右连接即可实现。

-- 左连接
SELECT d.*
FROM dept d
         LEFT JOIN emp e ON d.deptNo = e.deptNo
WHERE e.deptNo IS NULL;

-- 右连接
SELECT d.*
FROM emp e
         RIGHT JOIN dept d ON d.deptNo = e.deptNo
WHERE e.deptNo IS NULL;

• 解释：左连接和右连接可互换，只是主表的位置不同而已。如果想要返回左表的所有行以及右表的匹配行则选用左连接；反之，如果想要返回右表的所有行和左表的匹配行则用右连接。注意，两种连接中，未匹配行在结果集中都以NULL填充。

在不干扰其他连接的情况下向查询添加连接（多连接）

• 问题：假设有一个员工奖金的发放日期表emp_bonus，如下所示，

DROP TABLE IF EXISTS emp_bonus;
CREATE TEMPORARY TABLE emp_bonus
(
    empNo INT,
    received DATE,
    type INT
);

INSERT INTO emp_bonus
VALUES (7369, '2005-03-14', 1),
       (7900, '2005-03-14', 2),
       (7788, '2005-03-14', 3);

SELECT * FROM emp_bonus;

假设现在你需要查询所有员工，他们工作的部门所在位置，以及每个员工的奖金发放日期。

• 实现：这涉及到三个表，通过简单的将三个表放到FROM子句中，然后在WHERE子句中给出两两表之间的连接条件，并不会返回正确的结果，因为并非每个员工都有奖金，如果采用这种方式的话，那些没奖金的员工并不会出现在最后的结果集中。所以，这个时候需要用多连接来实现。

SELECT e.eName, d.loc, eb.received
FROM emp e
         LEFT JOIN dept d ON d.deptNo = e.deptNo
         LEFT JOIN emp_bonus eb ON e.empNo = eb.empNo
ORDER BY d.loc;

还有一种方式可以实现相同的目的，即标量子查询（在SELECT语句中的子查询）：

SELECT e.eName,
       d.loc,
       (SELECT received
        FROM emp_bonus eb
        WHERE eb.empNo = e.empNo) AS received
FROM emp e
         LEFT JOIN dept d ON d.deptNo = e.deptNo
ORDER BY d.loc;

“

Tips：标量子查询的方式适用于所有平台。

• 解释：外连接将返回一个表的所有行和另一个表的匹配行。使用标量子查询是一种在不影响当前结果集的情况下向查询中添加额外数据的简单方法。但是，请注意，在使用标量子查询时，必须确保它们返回的是一个标量（单个）值。如果SELECT列表中的子查询返回多行，代码将会报错。

结论

多表处理是实际工作中经常会遇到的情况，因为大多数时候需求方（客户/领导等）需要的数据都不是来自单个表，往往需要多个表的数据拼接。因此，掌握多表处理相关的方法和技术至关重要，通过掌握这些技术，SQL从业者可以有效地管理关系数据，简化数据分析流程，并确保在多个表之间的数据完整性。无论是执行数据比较，识别差异还是生成全面的报告，从本教程中获得的知识都将让SQL开发人员自信和高效地应对各种挑战。

总之，在SQL中处理多个表是任何涉及数据库管理和数据分析的人都必备的关键技能。通过本教程学到的技术，SQL从业者能够充分利用关系数据库的潜力，并在其组织中推动基于信息的决策。

“

如果你觉得这篇文章对你有用，请“点赞➕关注”支持我（关注我，不迷路🤭🤭🤭），你的点赞和关注就是我持续创作分享的动力，让更多的朋友发现并加入我们，共同学习和进步！

Thank you for reading!

- END -