第16节 外连接自连接与连接陷阱

内连接会丢掉没匹配的记录。书店现在要求“所有图书都显示,即使没分类、没卖过”,这就需要外连接。

1 LEFT JOIN保留左边全部记录

-- Book在左,保留6本书;没有分类的星空漫游显示NULL分类名
SELECT b.Title,c.CategoryName
FROM dbo.Book b
LEFT JOIN dbo.Category c ON b.CategoryId=c.CategoryId
ORDER BY b.BookId;

右侧没匹配时补NULL,不是创造了一条真实分类记录。右外连接RIGHT JOIN保留右侧;换一下两表位置通常就能改写成LEFT JOIN。

-- Category在左,因此保留全部3种分类;科普这一行的书名为NULL
SELECT c.CategoryName,b.Title
FROM dbo.Category c LEFT JOIN dbo.Book b ON c.CategoryId=b.CategoryId
ORDER BY c.CategoryId,b.BookId;
 
-- FULL JOIN两侧都保留:既保留无分类图书,也保留无图书分类
SELECT b.Title,c.CategoryName
FROM dbo.Book b FULL JOIN dbo.Category c ON b.CategoryId=c.CategoryId;

2 外连接之后怎么计数

-- 没卖过的图书仍显示,COUNT(s.SaleId)对它计0,而不是1
SELECT b.BookId,b.Title,
       COUNT(s.SaleId) AS [销售记录数],
       ISNULL(SUM(s.Quantity),0) AS [售出本数]
FROM dbo.Book b LEFT JOIN dbo.Sale s ON b.BookId=s.BookId
GROUP BY b.BookId,b.Title
ORDER BY b.BookId;

这里COUNT(*)会把外连接产生的空扩展行也数进来,所以“没卖过”会误显示1。SUM无匹配时为NULL,本题确定没卖过就是0本,因此可以ISNULL补0;未知库存则不能随意当0。

3 ON与WHERE不能随便互换

-- 保留所有图书,右侧只匹配单次销售数量至少3的销售记录
SELECT b.Title,s.Quantity
FROM dbo.Book b
LEFT JOIN dbo.Sale s ON b.BookId=s.BookId AND s.Quantity>=3;
 
-- WHERE在连接后过滤,右侧NULL不满足>=3,最终只剩Python基础
SELECT b.Title,s.Quantity
FROM dbo.Book b LEFT JOIN dbo.Sale s ON b.BookId=s.BookId
WHERE s.Quantity>=3;

第一条要回答“所有图书及符合条件的销售”,第二条要回答“有符合条件销售的记录”。先看题意,不能机械地说条件一定放哪一处。

4 自连接:一张表扮演两个角色

-- a、b都来自Book,但代表两本不同的书;寻找同分类图书对
SELECT a.Title AS [图书甲],b.Title AS [图书乙]
FROM dbo.Book a
JOIN dbo.Book b ON a.CategoryId=b.CategoryId AND a.BookId<b.BookId;
-- 计算机3对、文学1对,共4对;NULL分类不自动与NULL相等

小于号同时排除自己和自己配对,以及A-B、B-A镜像重复。只写 <>仍会留下镜像。

5 CROSS JOIN所有组合

-- 每本书配每一种分类,6×3=18行;没有ON匹配条件
SELECT b.Title,c.CategoryName
FROM dbo.Book b CROSS JOIN dbo.Category c;

实际查询若意外得到远多于预期的行数,先查是不是漏了JOIN关联条件。

本节练习

查从未卖过的图书;说出用哪个列IS NULL更稳妥。


上一节:15 用内连接组合两张表的信息 | 下一节:17 子查询与EXISTS循序理解 | 课程目录