“教程所用示例表：关注并回复关键字“sql教程示例数据”即可获取，需要的朋友请自取。
引言（Preface）
哈喽，各位朋友大家好吖~~，欢迎来到SQL教程系列第4讲！今天继续之前的多表关联查询内容。在当今的数据驱动时代，对于SQL的掌握至关重要。随着数据量的不断增长和复杂性的增加，处理多表数据成为SQL开发人员必备的技能之一。本教程将深入探讨SQL中处理多表数据的关键主题，从判断表之间是否存在相同数据到识别和避免笛卡尔积，再到执行连接和外连接等高级技术，一一展现给您。
判断两张表是否具有相同的数据
问题：假设你想知道两张表或视图是否具有相同的数据（基数或值）。考虑以下视图：
CREATE VIEW V37
AS
SELECT *
FROM emp
WHERE deptNo <> 10
UNION ALL
SELECT *
FROM emp
WHERE eName = 'WARD';
SELECT * FROM v37;
现在需要确定此视图是否与表emp具有完全相同的数据。为了说明实现方法不仅会显示不同的数据，还会显示重复的数据，我们复制了员工WARD的所在行。根据表emp中的行，差异将是部门10中员工的三行和员工WARD的两行。
实现：如果你的DBMS提供了集合差集的运算函数（比如EXCEPT或MINUS），那比较表相对容易。但是，如果你的DBMS不提供差集运算函数，那就使用相关子查询和
UNION ALL实现。MySQL，SQL Server
SELECT *
FROM (SELECT e.empNo, e.eName, e.job, e.mgr, e.hireDate,
e.sal, e.comm, e.deptNo, COUNT(*) cnt
FROM emp e
GROUP BY e.empNo, e.eName, e.job, e.mgr,
e.hireDate, e.sal, e.comm, e.deptNo) t
WHERE NOT EXISTS(
SELECT NULL
FROM (SELECT v.empNo, v.eName, v.job, v.mgr, v.hireDate,
v.sal, v.comm, v.deptNo, COUNT(*) cnt
FROM V37 v
GROUP BY v.empNo, v.eName, v.job, v.mgr,
v.hireDate, v.sal, v.comm, v.deptNo) tt
WHERE t.empNo = tt.empNo
AND t.eName = tt.eName
AND t.job = tt.job
AND COALESCE(t.mgr, 0) = COALESCE(tt.mgr, 0)
AND t.hireDate = tt.hireDate
AND t.sal = tt.sal
AND COALESCE(t.comm, 0) = COALESCE(tt.comm, 0)
AND t.deptNo = tt.deptNo
AND t.cnt = tt.cnt
)
UNION ALL
SELECT *
FROM (SELECT v.empNo, v.eName, v.job, v.mgr, v.hireDate,
v.sal, v.comm, v.deptNo, COUNT(*) cnt
FROM V37 v
GROUP BY v.empNo, v.eName, v.job, v.mgr,
v.hireDate, v.sal, v.comm, v.deptNo) t
WHERE NOT EXISTS(
SELECT NULL
FROM (SELECT e.empNo, e.eName, e.job, e.mgr, e.hireDate,
e.sal, e.comm, e.deptNo, COUNT(*) cnt
FROM emp e
GROUP BY e.empNo, e.eName, e.job, e.mgr,
e.hireDate, e.sal, e.comm, e.deptNo) tt
WHERE t.empNo = tt.empNo
AND t.eName = tt.eName
AND t.job = tt.job
AND COALESCE(t.mgr, 0) = COALESCE(tt.mgr, 0)
AND t.hireDate = tt.hireDate
AND t.sal = tt.sal
AND COALESCE(t.comm, 0) = COALESCE(tt.comm, 0)
AND t.deptNo = tt.deptNo
AND t.cnt = tt.cnt
);
如果你的DBMS提供差集运算函数，比如Oracle，实现代码更简洁一些，就是将相关子查询换成MINUS：
(
SELECT e.EMPNO, e.ENAME, e.JOB, e.MGR, e.BDATE,
e.MSAL, e.COMM, e.DEPTNO, COUNT(*) cnt
FROM EMPLOYEES e
GROUP BY e.EMPNO, e.ENAME, e.JOB, e.MGR,
e.BDATE, e.MSAL, e.COMM, e.DEPTNO
MINUS
SELECT v.EMPNO, v.ENAME, v.JOB, v.MGR, v.BDATE,
v.MSAL, v.COMM, v.DEPTNO, COUNT(*) cnt
FROM V37 v
GROUP BY v.EMPNO, v.ENAME, v.JOB, v.MGR,
v.BDATE, v.MSAL, v.COMM, v.DEPTNO
)
UNION ALL
(
SELECT v.EMPNO, v.ENAME, v.JOB, v.MGR, v.BDATE,
v.MSAL, v.COMM, v.DEPTNO, COUNT(*) cnt
FROM V37 v
GROUP BY v.EMPNO, v.ENAME, v.JOB, v.MGR,
v.BDATE, v.MSAL, v.COMM, v.DEPTNO
MINUS
SELECT e.EMPNO, e.ENAME, e.JOB, e.MGR, e.BDATE,
e.MSAL, e.COMM, e.DEPTNO, COUNT(*) cnt
FROM EMPLOYEES e
GROUP BY e.EMPNO, e.ENAME, e.JOB, e.MGR,
e.BDATE, e.MSAL, e.COMM, e.DEPTNO
);
如果是DB2或PostgreSQL，则将MINUS换成EXCEPT即可。
解释：虽然使用了不同的技术，但其实所有实现方法从概念上看都是一样的： 找出emp表在视图V37中不存在的行。 找出视图V37在emp表中不存在的行。 使用 UNION ALL合并上面的两个结果集就得到最终的结果集。
如果两张表完全相等，那么就不会有任何行返回。反之，如果两张表不同，则会返回二者的差异行。
识别和避免笛卡尔积
问题：查询10号部门中每个员工的姓名以及部门所在位置。我们来看看错误的实现：
SELECT e.eName, d.loc
FROM emp e, dept d
WHERE e.deptNo = 10
ORDER BY e.eName;
看出问题来了吗？员工表中10号部门有3名员工，因此正确的结果应该只有三行，但是上面却是12行，即10号部门的3名员工每一个都与部门表中的4行配对组合（3×4）。这就是笛卡尔积。那正确的实现是什么呢？
实现：笛卡尔积的产生往往是在使用连接的时候忘记加连接条件，使得连接成为了自然连接。要想得到正确的结果，只需要加上连接条件即可。
SELECT e.eName, d.loc
FROM emp e,
dept d
WHERE e.deptNo = d.deptNo
AND e.deptNo = 10
ORDER BY e.eName;
或者显式地使用内连接语法，这样可以更好地避免笛卡尔积的产生，因为连接条件在ON之后，如果忘记连接条件会报语法错误：
SELECT e.eName, d.loc
FROM emp e
INNER JOIN dept d
ON e.deptNo = d.deptNo
WHERE e.deptNo = 10
ORDER BY e.eName;
解释：总之，如果你将连接的表都放在 FROM子句之后，连接条件放到WHERE子句中，这样很容易忘记连接条件。所以推荐大家使用第二种实现方法。
在使用聚合时执行连接
问题：假设你想执行聚合，但是查询又涉及到多个表，这个时候又不希望破坏聚合该怎么做呢？比如，计算10号部门中每个员工的总薪水和总奖金。这里需要借助另外一个临时表emp_bonus，它存储员工的奖金信息：
DROP TABLE IF EXISTS emp_bonus;
CREATE TEMPORARY TABLE emp_bonus
(
empNo INT,
received DATE,
type INT
);
INSERT INTO emp_bonus
VALUES (7934, '2005-03-17', 1),
(7934, '2005-02-15', 2),
(7839, '2005-02-15', 3),
(7782, '2005-02-15', 1);
SELECT * FROM emp_bonus;
其中，type的值表示奖金占薪水的比例（1→10%；2→20%；3→30%）。我们先来看看错误的实现：
SELECT deptNo,
SUM(sal) total_sal,
SUM(bonus) total_bonus
FROM (SELECT e.deptNo,
e.sal,
e.sal * (CASE
WHEN eb.type = 1 THEN .1
WHEN eb.type = 2 THEN .2
ELSE .3
END) AS bonus
FROM emp e
INNER JOIN emp_bonus eb ON e.empNo = eb.empNo
WHERE e.deptNo = 10) t
GROUP BY deptNo;
总奖金是正确的，但是总薪水确实错误。因为10号部门中所有员工的薪水总数应该是8750，可以通过下面的查询验证：
而造成薪水总额计算错误的原因就在于emp表和emp_bonus表的连接结果中存在重复行（员工MILLER），所以导致聚合求和的时候重复计算了该员工的薪水。可以看看下面的连接结果：
SELECT e.eName,
e.sal
FROM emp e
INNER JOIN emp_bonus eb ON e.empNo = eb.empNo
WHERE e.deptNo = 10;
实现：为了获得正确的薪水总额，应该在外层的聚合函数中使用 DISTINCT关键字进行去重。
SELECT deptNo,
SUM(DISTINCT sal) total_sal,
SUM(bonus) total_bonus
FROM (SELECT e.deptNo,
e.sal,
e.sal * (CASE
WHEN eb.type = 1 THEN .1
WHEN eb.type = 2 THEN .2
ELSE .3
END) AS bonus
FROM emp e
INNER JOIN emp_bonus eb ON e.empNo = eb.empNo
WHERE e.deptNo = 10) t
GROUP BY deptNo;
当然，还需要注意一点，上述案例使用内连接是理想情况，因为10号部门的3个员工在emp_bonus表中都有奖金，所以使用e.empNo = eb.empNo连接后能够确保计算10号部门中所有员工的薪水和奖金总数。但是如果10号部门中不是所有员工在emp_bonus表中都有奖金记录的话，使用内连接后再聚合的结果也是错的，因为那些没有获得奖金的员工薪水没有算在最终的薪水总额里。所以，完整全面的实现应该是采用外连接：
SELECT deptNo,
SUM(DISTINCT sal) total_sal,
SUM(bonus) total_bonus
FROM (SELECT e.deptNo,
e.sal,
e.sal * (CASE
WHEN eb.type = 1 THEN .1
WHEN eb.type = 2 THEN .2
ELSE .3
END) AS bonus
FROM emp e
LEFT JOIN emp_bonus eb ON e.empNo = eb.empNo
WHERE e.deptNo = 10) t
GROUP BY deptNo;
解释：对连接后的数据进行聚合时要特别小心重复行，因为一旦产生重复行，那么聚合结果也会相应出错。 DISTINCT关键字用于对结果进行去重，它可以确保无论是否存在重复行，聚合时对同一条数据都只会计算一次。
多表关联并返回缺失数据
问题：查询部门表中每个部门下的员工，包括没有员工的部门。可以通过外连接实现：
SELECT d.deptNo, d.dName, e.eName
FROM dept d
LEFT JOIN emp e ON d.deptNo = e.deptNo;
从结果的最后一行可以看出，OPERATIONS部门下没有任何员工，但仍然包含在结果集中。那么，假设员工表中有一个员工没有部门，要输出该员工的信息还是使用外连接（左/右），只需要将员工表作为主表即可：
INSERT INTO emp(empNo, eName, job, mgr, hireDate, sal, comm, deptNo)
SELECT 1111, 'YODA', 'JEDI', NULL, hireDate, sal, comm, NULL
FROM emp
WHERE eName = 'KING';
SELECT d.deptNo, d.dName, e.eName
FROM emp e
LEFT JOIN dept d ON d.deptNo = e.deptNo;
从结果中可以看出，虽然将没有部门的员工“YODA”返回了，但是没有员工的部门“OPERATIONS”信息却消失了。
实现：要想正确返回左表和右表中不满足连接条件的行，可以使用全连接。
SELECT d.deptNo, d.dName, e.eName
FROM emp e
FULL JOIN dept d ON d.deptNo = e.deptNo;
但是，MySQL不支持全连接操作，要实现全连接的效果，可以使用UNION ALL代替实现：
SELECT d.deptNo, d.dName, e.eName
FROM emp e
LEFT JOIN dept d ON d.deptNo = e.deptNo
UNION ALL
SELECT d.deptNo, d.dName, e.eName
FROM emp e
RIGHT JOIN dept d ON d.deptNo = e.deptNo;
解释：全连接其实就是左连接或者右连接的合并，不管是否满足连接条件，两张表的所有行都会返回，只是不满足连接条件的字段值用 NULL填充。
在比较中使用NULLs
问题： NULL与任何值都不存在相等或不等关系，即使是NULL本身。但是，如果你想对一个可为空的列执行计算该怎么做呢？比如，找出员工表中佣金低于“WARD”员工佣金的所有员工（comm列中包含空值）。实现：要想对可能存在空值的列执行比较，应该在比较前先处理空值情况，根据比较表达式的数据类型将空值替换为0或其他同类型的值即可。
SELECT eName, comm
FROM emp
WHERE COALESCE(comm, 0) < (SELECT comm
FROM emp
WHERE eName = 'WARD');
解释：请记住， NULL不能和任何值进行比较（比如，>, <, =, !=, <>）。COALESCE函数会返回传递给它的值列表的第一个非空值，在上面的实现方法中，当遇到NULL时，它会将其替换为0，然后再和“WARD”的佣金进行比较。
总结（Conclusion）
在本教程中，我们深入研究了SQL中处理多表数据的关键主题。通过学习如何判断两张表是否有相同的数据，识别和避免笛卡尔积，以及在使用聚合时执行连接和外连接等技术，我们不仅提升了SQL技能，还增强了对数据的理解和处理能力。通过这些技术的应用，我们能够更有效地处理多表数据，准确地识别缺失数据，并在数据操作和比较中正确处理空值。希望本教程能够帮助您更加深入地理解SQL，并在实际工作中取得更大的成就。
“如果你觉得这篇文章对你有用，请“点赞➕关注”支持我（关注我，不迷路🤭🤭🤭），你的点赞和关注就是我持续创作分享的动力，让更多的朋友发现并加入我们，共同学习和进步！
Thank you for reading!
- END -
推荐阅读👇👇👇
原文作者：数据派探险家；如有侵权，请联系管理员删除。
最新文章
- 5个被过度高估的Python库（你绝对想不到）！
- 不掌握这10个Python概念，请不要自称高级开发人员（二）~~
- 不掌握这10个Python概念，请不要自称高级开发人员（一）~~
- 数据分析面试：你认为数据分析的价值如何体现？
- Python必知必会：使用 dedent() 方法优雅地展示多行字符串！
- Python必知必会：39个Python关键字详解，你的编程查阅小手册，收藏！
- Python必知必会：每个程序员都必须知道的22个Python单行代码！
- Python必知必会：15个令人相见恨晚的Python字符串格式化技巧！
- 24个令人眼前一亮的Python技巧—提升你的代码水平和质量（二）
- 24个令人眼前一亮的Python技巧—提升你的代码水平和质量（一）
