上一章的联结只用了两张表、一种写法。 这一章补齐三件工具：表别名、自联结、外联结，
外加一个最容易写错的搭配——聚集函数配外联结。
Demo 里自联结和子查询给出同样的六行；内联结丢掉没下单的 Mouse House，左外联结把它捞回来；
COUNT(列) 给 0 而 COUNT(*) 给 1——外联结造的那行全是 NULL。
第 15 章的等值联结只留「两边都对上」的行， 这一章讲对不上的怎么办——那正是业务里最常问的问题（谁没买过、哪个产品没卖过）。 外联结是全书最容易被误用的一处：条件写在 ON 还是 WHERE，结果完全不同。
一、表别名：让同一张表出现两次
FROM vendors AS v 给表起了个短名，之后整句都用 v. 前缀。
别名只在这一次查询里有效，不改表名、不影响别的查询。
好处有三个：少打字、同名列不打架、最重要的是同一张表能出现两次。
这一点和列别名不同：列别名改的是表头，表别名改的是「这句话里怎么称呼它」。
有些数据库（比如 Oracle）要求表别名不带 AS，MySQL 和 SQLite 两种都认。
二、自联结：自己跟自己连
自联结就是把一张表当两张用：products AS p1, products AS p2。
两个别名必须不同，否则数据库分不清你说哪一份。
教材那个例子：「找出与 DTNTR 同一供应商的所有产品」。
p2 负责查出 DTNTR 的 vend_id，p1 负责列出这个 vend_id 下的产品。
同样的问题用子查询也能做，而且更像人话：
WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR')。
教材的结论是自联结通常更快，因为子查询可能被当成两步执行。
先写对，再谈快——两种写法结果一样时，选你看得懂的那个。
三、不同类型的联结：内、左外、右外、全外
内联结（INNER JOIN）只留两边都对上的行。 客户和订单内联结，
没下过单的客户直接消失——他甚至不会告诉你「有个人没下单」。
左外联结（LEFT OUTER JOIN）保留左表全部行，右边配不上就填 NULL。
这是「以左表为主」的写法：五个客户必须都出现，下过单的带单号，没下过的是 NULL。
右外联结反过来保留右表全部行，全外联结两边都保留。
注意支持情况：老版本 SQLite 只有 LEFT，MySQL 没有 FULL JOIN（要用 LEFT 加 UNION 拼），
本课这套引擎三个都支持。
一个必须记住的坑：条件写在 ON 和写在 WHERE 效果不同。
写在 ON 是「怎么连」，写在 WHERE 是「连完再筛」——把 o.order_num IS NULL 写进 ON，
左外联结就退化成内联结了。
四、外联结的经典用法：找「没有」
「哪些客户一单没下」这个问题，内联结永远答不出来，因为那些客户根本不在结果里。
标准解法：左外联结 + WHERE 右表主键 IS NULL。配不上的行才有 NULL，恰好就是你要找的缺口。
这个套路可以套在很多问题上：没卖过的产品、没写过评论的用户、
没交过作业的学生——凡是「找出没做过某事的人」，都是这一句。
五、带聚集函数的联结
「每个客户下了几单」需要联结加分组：左外联结保住全部客户，再 GROUP BY 客户 数单号。
这里有个致命细节：数什么。 COUNT(o.order_num) 数非 NULL，没下单的客户给 0；
COUNT(*) 数行，外联结给他造了一行，于是给 1。
教材专门把这两个摆在一起对比，因为 COUNT(*) 那个 1 看起来完全正常，
只有跟业务对账时才会发现「这个客户明明没买过」。
六、小结
教材小结：表别名可自联结，联结分内与外，外联结用 LEFT OUTER JOIN，配聚集函数时要数对列。 本课补三条：一是自联结与子查询等价，通常自联结更快；二是「找没有」靠左外联结加 IS NULL； 三是外联结加 COUNT 要用列名，星号会把空行数成 1。
七、动手 Demo：别名、自联结、三种外联结一次看完
Demo 七段：先给表起别名，再用自联结查同门产品，接着对比子查询写法； 然后内联结与外联结摆一起看「丢了几行」，最后用聚集函数踩一次 COUNT 的坑。 重点盯三处行数：内联结 5、左外联结 6、全联结 6——差的那一行就是 Mouse House。
import sqlite3
con = sqlite3.connect(":memory:")
# 表别名：v 就是 vendors，p 就是 products
con.execute("SELECT v.vend_name, p.prod_name FROM vendors AS v, products AS p "
"WHERE v.vend_id = p.vend_id ORDER BY v.vend_name")
# 自联结：同一张表两个别名，找与 DTNTR 同供应商的其他产品
con.execute("SELECT p1.prod_id, p1.prod_name FROM products AS p1, "
"products AS p2 WHERE p1.vend_id = p2.vend_id "
"AND p2.prod_id = 'DTNTR'")
# 同一件事的子查询写法
con.execute("SELECT prod_id, prod_name FROM products WHERE vend_id = "
"(SELECT vend_id FROM products WHERE prod_id = 'DTNTR')")
# 内联结漏掉没下单的客户，左外联结补回来
con.execute("SELECT c.cust_id, o.order_num FROM customers AS c "
"INNER JOIN orders AS o ON c.cust_id = o.cust_id")
con.execute("SELECT c.cust_id, o.order_num FROM customers AS c "
"LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust_id")
# 找「没有」：配不上的行 IS NULL
con.execute("SELECT c.cust_name FROM customers AS c "
"LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust_id "
"WHERE o.order_num IS NULL")
# COUNT(列) 不数 NULL，COUNT(*) 会把它数成 1
con.execute("SELECT c.cust_name, COUNT(o.order_num) AS n FROM customers AS c "
"LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust_id "
"GROUP BY c.cust_id")
实跑输出：
--------------------------------------------------------------------
第十六课：创建高级联结
--------------------------------------------------------------------
【一】表别名：给表起个短名
SQL> SELECT v.vend_name, p.prod_name FROM vendors AS v,
products AS p WHERE v.vend_id = p.vend_id ORDER BY v.vend_name, p.prod_name
vend_name prod_name
-------------------------------
ACME Bird seed
ACME Carrots
ACME Detonator
ACME Sling
ACME TNT (1 stick)
ACME TNT (5 sticks)
Anvils R Us .5 ton anvil
Anvils R Us 1 ton anvil
Anvils R Us 2 ton anvil
Anvils R Us Safe
Jet Set JetPack 2000
LT Supplies Fuses
LT Supplies Oil can
（13 行）
**AS 给表起别名**，`v` 就是 vendors、`p` 就是 products。 结果和上一章第 15 章那段完全一样。
**别名只在这一次查询里有效**，不改表名、不影响别的查询。
**好处有三**：少打字、让同名列不打架、**最重要的——同一张表可以出现两次。**
【二】自联结：一张表当两张用
SQL> SELECT p1.prod_id, p1.prod_name FROM products AS p1,
products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR'
prod_id prod_name
---------------------------
DTNTR Detonator
FB Bird seed
FC Carrots
SLING Sling
TNT1 TNT (1 stick)
TNT2 TNT (5 sticks)
（6 行）
**p1 与 p2 是同一张表**：p2 用来定位「DTNTR 的供应商是谁」，p1 用来列出这个供应商的所有产品。
**六个，都是 1003 的。**
**自联结的必须条件：两个别名必须不同**，否则数据库分不清你说哪一份。
【三】同一个问题，子查询也能做
SQL> SELECT prod_id,
prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR')
prod_id prod_name
---------------------------
DTNTR Detonator
FB Bird seed
FC Carrots
SLING Sling
TNT1 TNT (1 stick)
TNT2 TNT (5 sticks)
（6 行）
**也是六行，结果一模一样。** 这是第 14 章那条子查询路。
**教材的结论是：自联结通常更快。** 因为子查询可能被当成两步执行，而自联结一次扫完。
**不过可读性上子查询更直观**，先写对再谈快。
【四】外联结：左表一行都不许丢
SQL> SELECT c.cust_id,
o.order_num FROM customers AS c INNER JOIN orders AS o ON c.cust_id = o.cust_id ORDER BY c.cust_id
cust_id order_num
----------------------
10001 20005
10001 20009
10003 20006
10004 20007
10005 20008
（5 行）
**内联结给五行。** 五个客户里只有四个下过单，**Mouse House 一单没下，被内联结丢了。**
SQL> SELECT c.cust_id,
o.order_num FROM customers AS c LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust_id ORDER BY c.cust_id
cust_id order_num
----------------------
10001 20005
10001 20009
10002 NULL
10003 20006
10004 20007
10005 20008
（6 行）
**外联结给六行。** 10001 有两单占两行，Mouse House 那行的 order_num 是 NULL。
**LEFT 的意思是「左边的表全留」**，右边配不上的位置填 NULL。
**教材建议写全 `LEFT OUTER JOIN`**，虽然 `LEFT JOIN` 等价，写全更能说明意图。
【五】外联结的经典用法：找「没有」
SQL> SELECT c.cust_id,
c.cust_name FROM customers AS c LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust_id WHERE o.order_num IS NULL
cust_id cust_name
------------------------
10002 Mouse House
（1 行）
**找出没下过单的客户，就靠 `IS NULL`。** 一行：Mouse House。
**这类「找缺口」的查询内联结做不到**——因为配不上的行根本不出现，你也就看不见缺口。
【六】右联结与全联结
SQL> SELECT COUNT(*) AS n FROM customers AS c RIGHT JOIN orders AS o ON c.cust_id = o.cust_id
n
--
5
（1 行）
SQL> SELECT COUNT(*) AS n FROM customers AS c FULL JOIN orders AS o ON c.cust_id = o.cust_id
n
--
6
（1 行）
**右联结反过来「右边的表全留」**，这里是 5 行，因为五张单的客户都找得到。
**全联结两边都留**，6 行：五张单加一个没下单的客户。
**老版本 SQLite 只有 LEFT，右联结要把两张表调个位置**；
**MySQL 也不支持 FULL JOIN**，得用 LEFT 加 UNION 拼。这一课这套引擎三个都支持。
【七】带聚集函数的联结：COUNT 星号会骗人
SQL> SELECT c.cust_name,
COUNT(o.order_num) AS num_ord FROM customers AS c LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust_id GROUP BY c.cust_id ORDER BY num_ord DESC
cust_name num_ord
-----------------------------
Coyote Inc. 2
Wascals 1
Yosemite Place 1
E Fudd 1
Mouse House 0
（5 行）
**Mouse House 给 0**，因为 `COUNT(列)` 不数 NULL，它配出来的那行 order_num 是 NULL。
SQL> SELECT c.cust_name,
COUNT(*) AS num_ord FROM customers AS c LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust_id GROUP BY c.cust_id ORDER BY num_ord DESC
cust_name num_ord
-----------------------------
Coyote Inc. 2
Mouse House 1
Wascals 1
Yosemite Place 1
E Fudd 1
（5 行）
**换成 `COUNT(*)`，Mouse House 变成 1。** 因为外联结确实给它造了一行，
只是那行的值全是 NULL——**数行数和数有效值，是两件事。**
第一段别名。 结果和第 15 章逐行一致，只是写法短了。
第二段自联结。 六行，都是 1003 的产品——包括 DTNTR 自己。
第三段子查询。 同样六行，两条路都通，选你看得懂的。
第四段内外对比。 内联结 5 行，左外 6 行，多的那行 10002 的 order_num 是 NULL。
第五段找缺口。 IS NULL 一句，直接点出 Mouse House。
第六段右联结与全联结。 5 行与 6 行，右联结没多出行，因为每张单的客户都找得到。
第七段 COUNT 的坑。 先给 0，换成星号就变 1。两段结果只有 Mouse House 那一行不同。
30 秒实验
- 把第一段的
ORDER BY v.vend_name去掉，13 行的顺序变不变？ - 用自联结找出「与 ANV01 同供应商的产品」，应该有几种？
- 把第七段的
COUNT(o.order_num)换成SUM(o.order_num IS NULL)，结果说明什么？
常见误区
- 以为 LEFT JOIN 和 INNER JOIN 差不多。左外保留左表全部，内联结丢掉配不上的行。
- 把 WHERE 条件写成 ON 条件。写在 ON 里就成了联结条件，外联结会退化成内联结。
- 外联结配 COUNT(*)。配不上的行也算一行，没下单的客户会被数成 1。
自测三问（点开对答案）
1. 自联结和子查询做同一件事，怎么选？ —— 结果一样时，通常自联结更快（一次扫描），子查询更好读；先写对，再按需要换。 2. 怎么找出「一次都没下单的客户」？ —— `customers LEFT OUTER JOIN orders ON ... WHERE orders.order_num IS NULL`； 配不上的行才有 NULL，就是缺口。 3. 左外联结后 `COUNT(*)` 和 `COUNT(o.order_num)` 为什么不同？ —— 星号数行，外联结为没下单的客户造了一行（值全 NULL），所以数成 1；列名只数非 NULL，给 0。多表能拼、缺口能找到， 但一次查询只能有一种「行」的样子。 下一章把几条独立查询的结果上下摞起来——组合查询。
- ☐ （本章完，下一章 第17章 组合查询）