连接只有一件事：按条件把两张表的行配对，配不上的怎么办就是内外连接的区别。 漏写连接条件不会报错，它给你一份「看着有数据、其实全是重复」的结果。 左连接和右连接的差别只是「保留哪一边」，写哪个都行，习惯上只用左连接。
9.4 讲三件事：内连接、外连接、复合条件连接。前两件是重点，第三件只是「条件里多写几句」。
这一篇的「漏写条件」，和第 9.5 篇的 NOT IN 空值陷阱是一对：
都是「不报错但结果全错」的典型。
一、交叉连接：每一行配每一行
不给连接条件时，数据库把两张表的所有组合都给你，这叫笛卡尔积。 行数是两张表行数相乘，11 行配 5000 行就是 55000 行。 这个数看着不大，但它是「相乘」不是「相加」，所以规模一大就失控。 换成两张十万行的表，结果是一亿行——这不是慢，是直接跑不出来。 所以看到「结果行数比预想多几十倍」，第一个怀疑对象就是漏了连接条件。
二、内连接与外连接
内连接是「交叉连接 + 条件筛选」，只留配得上的组合。 外连接是「不管配不配得上，先把某一边全留下」，另一边配不上就补 NULL。
| 连接方式 | 保留哪一边 | 结果行数 |
|---|---|---|
| 内连接 | 只留配得上的 | 4865 |
| 左外连接 | 保留 5000 个商品 | 5000 |
| 右外连接 | 保留 11 个类别 | 4866 |
左连接比内连接多出来的 135 行，就是「有商品但没类别」的那些。
右连接比内连接多 1 行，多的是那个「新品类」，它没有任何商品。
右连接比左连接少，不是因为哪个更强，是因为两边保留的东西不一样。
左连接保留「每一个商品」，右连接保留「每一个类别」，两张表行数本来就不同。
实践里几乎只用左连接，因为它读起来顺着「主表在左边」的思路走。
右连接除了让人多看两眼，没有别的作用——把两张表位置对调就是左连接。
外连接还有一个反直觉的用法：LEFT JOIN ... WHERE 右表.主键 IS NULL 用来找孤儿数据。
这一招在清洗数据时非常常用，比写子查询快得多。
三、复合条件连接与自连接
复合条件连接就是 ON 后面多写几个 AND，语法上没有新东西。
它的价值在于「连接之外还要筛」，把条件都放在 ON 里比放 WHERE 更清楚。
自连接是「同一张表取两个别名互相连」，典型场景是树形结构：员工和上级。
这里必须用 LEFT JOIN，否则没有上级的那个「总经理」会消失。
自连接必须给两个别名，这不是风格问题，是语法要求。
不起别名时，引擎不知道 name 是员工的名字还是上级的名字，直接报「列名有歧义」。
代价上要留意：自连接是「同一张表扫两遍」。
十万行的表自连接，等于二十万行的读，估算时要乘二。
四、忘写连接条件会怎样
这一节是这一篇最重要的地方。
把连接条件漏掉，SQL 不报错、不警告，它给你笛卡尔积的一个子集。
这次的结果是 2386 行变成 26983 行，多了十一倍多。
拿它去 SUM 金额，金额会被放大同样的倍数。
最要命的是「看着差不多」：数出来了，还像个正常数字，往往过很久才被发现。
预防办法只有一个：连接一律用 JOIN ... ON 的写法，不用逗号加 WHERE。
因为 ON 是必填的，漏了直接语法错；而逗号写法漏了条件，语法上完全合法。
让语法帮你挡住错误，比让自己记住更可靠。
五、动手 Demo：五种连接，和忘写条件的那一场灾难
两张表：tb_goods 5000 行（其中 135 行没类别），
tb_cat2 11 行（10 个正常类别 + 1 个没有任何商品的「新品类」）。
这个组合能把内外连接的差别全暴露出来。
import sqlite3
c = sqlite3.connect(u"d039.db")
# ① 交叉连接
print(c.execute(u"SELECT COUNT(*) FROM tb_cat2, tb_goods").fetchone())
# ② 内连接 / 左外 / 右外
print(c.execute(u"SELECT COUNT(*) FROM tb_goods g "
u"INNER JOIN tb_cat2 k ON g.cat_id = k.id").fetchone())
print(c.execute(u"SELECT COUNT(*) FROM tb_goods g "
u"LEFT JOIN tb_cat2 k ON g.cat_id = k.id").fetchone())
print(c.execute(u"SELECT COUNT(*) FROM tb_goods g "
u"RIGHT JOIN tb_cat2 k ON g.cat_id = k.id").fetchone())
# ③ 找孤儿数据
print(c.execute(u"SELECT COUNT(*) FROM tb_goods g "
u"LEFT JOIN tb_cat2 k ON g.cat_id = k.id "
u"WHERE k.id IS NULL").fetchone())
# ④ 漏写连接条件的后果
print(c.execute(u"SELECT COUNT(*) FROM tb_goods g, tb_cat2 k "
u"WHERE g.price > 100").fetchone()) # 漏了
print(c.execute(u"SELECT COUNT(*) FROM tb_goods g, tb_cat2 k "
u"WHERE g.price > 100 AND g.cat_id = k.id").fetchone())
实跑输出：
--------------------------------------------------------------------
第三十九课：五种连接，和忘写条件的那一场灾难
--------------------------------------------------------------------
**教材 9.4 讲连接查询。连接的核心只有一句话：把两张表按某个条件配对。**
**配不上的行怎么办，就是内连接和外连接的区别。**
**这一课跑五种连接，再故意忘写一次连接条件，看会发生什么。**
一、交叉连接：每一行配每一行
--------------------------------------------------------------------
表 行数
-------------------
`tb_cat2` 11
`tb_goods` 5000
两者交叉连接 55000
**第三行等于前两行相乘。**
**这就是笛卡尔积：不给条件，数据库就把所有组合都给出来。**
**11 行乘 5000 行得 55000 行，看着不多；换成两张十万行的表，就是一亿行。**
二、内连接：只要配得上的
--------------------------------------------------------------------
code name cat
---------------------
G00001 数码商品1 数码
G00002 家电商品2 家电
G00003 服饰商品3 服饰
3 rows in set
**`INNER JOIN` 就是「交叉连接 + 条件筛选」，只留配得上的组合。**
**这里配出 4865 行，比 55000 少了一个数量级，因为绝大多数的组合是错配。**
三、左外连接：左边的全留，右边配不上就补空
--------------------------------------------------------------------
连接方式 结果行数 说明
----------------------------------------------------
内连接 4865 配不上的都丢掉
左外连接 5000 左表全留，右边补 NULL
其中右表为空的行 135 这些就是「没类别」的商品
**第二行比第一行多出来的 135 行，就是「有商品但没类别」的那些。**
**所以外连接有一个反直觉的用法：`LEFT JOIN ... WHERE 右表.主键 IS NULL` 用来「找孤儿数据」。**
**这一招在清洗数据时非常常用。**
四、右外连接，其实就是换个方向的左连接
--------------------------------------------------------------------
连接方式 保留哪一边 结果行数
------------------------------------
内连接 只留配得上的 4865
左外连接 保留 5000 个商品 5000
右外连接 保留 11 个类别 4866
**`RIGHT JOIN` 的结果和把两张表位置对调的 `LEFT JOIN` 完全一样。**
**这里给 4866 行，比内连接多 1 行——多的是那个「新品类」，它没有任何商品。**
**但它比左连接少，因为两边保留的东西不一样。**
**左连接保留「每一个商品」，右连接保留「每一个类别」。**
**所以行数不同不是矛盾，是两张表行数本来就不同。**
**实践里几乎只用 `LEFT JOIN`，因为它读起来顺着「主表在左边」的思路走。**
**`RIGHT JOIN` 除了让人多看两眼，没有别的作用。**
**`FULL OUTER JOIN` 是「两边都留」，MySQL 不支持，要用 `LEFT UNION RIGHT` 拼。**
**所以跨库搬 SQL 时，看到 `FULL JOIN` 就知道要改。**
五、忘写连接条件的后果
--------------------------------------------------------------------
写法 结果行数 耗时
---------------------------------
写了连接条件 2386 876 微秒
漏了连接条件 26983 1.80 毫秒
**漏了条件之后，结果变成了原来的 11.3 倍。**
**而且它不报错、不警告——你会拿到一份「看起来有数据、其实全是重复」的结果。**
**这种错误在下游很致命：拿它去 `SUM` 金额，金额会被放大同样的倍数。**
**而且因为数字「看着差不多」，往往过很久才被发现。**
**预防办法只有一个：连接一律用 `JOIN ... ON` 的写法，不用逗号加 `WHERE`。**
**因为 `ON` 是必填的，漏了直接语法错；而逗号写法漏了条件，语法上是合法的。**
六、自连接：一张表当两张用
--------------------------------------------------------------------
员工 上级
---------------
总经理 NULL
研发总监 总经理
销售总监 总经理
财务总监 总经理
4 rows in set
**自连接就是「同一张表取两个别名，然后互相连」。**
**这里 `e` 是员工，`m` 是上级，其实是同一张 `tb_emp`。**
**用 `LEFT JOIN` 是为了让「总经理」那一行也留下来——他没有上级，右边补 NULL。**
**换成 `INNER JOIN`，总经理会消失，这往往不是你要的结果。**
**自连接必须用表别名，否则没法区分「这一列是员工的还是上级的」。**
**这是别名唯一「不写就报错」的场景，第 9.7 节会再讲。**
**这张表只有 10 行，自连接只花了 107 微秒。**
**但自连接是「同一张表扫两遍」，行数一大代价就翻倍。**
**十万行的表自连接，等于二十万行的读。这一点在估算时容易漏。**
六个数排下来就是一条完整的推理线：55000、4865、5000、4866、135、26983。
55000 是相乘得到的，4865 是筛出来的，5000 和 4866 是「保留一边」得到的。
135 这个数最有用：它等于 5000 减 4865，也等于「左连接里右表为空的行数」。
两个算法给同一个数，说明「左连接多出来的行」和「孤儿数据」是同一批。
最后那两个数是 26983 和 2386，相差十一倍多。
注意 26983 不是整数倍关系——因为 price > 100 的商品在类别上分布不均。
这也说明「多了几倍」这种话不能凭猜，得真跑。
自连接那一段只有 10 行数据，耗时 63 微秒，看不出差别。
它的代价要等行数上来才显出来，这一点在估算时容易漏。
30 秒实验
- 把左连接换成内连接，看 135 行怎么消失的。
- 把
WHERE k.id IS NULL去掉，看行数从 135 跳回 5000。 - 把自连接的
LEFT JOIN换成INNER JOIN，看总经理那一行怎么没了。
常见误区
- 用逗号加
WHERE写连接，漏了条件，拿到十一倍的行还以为查对了。 - 以为右连接「更强」或者「结果更多」，其实它只是保留另一边。
- 自连接不起别名，或者以为别名可写可不写。
自测三问（点开对答案）
1. 内连接和外连接的区别，一句话怎么说？ —— 内连接只留配得上的行；外连接先把某一边全留下，另一边配不上就补 NULL。 2. 为什么说右连接是多余的？ —— 因为把两张表位置对调，右连接就变成左连接，结果完全一样。保留哪个写法只是阅读习惯问题。 3. 漏写连接条件为什么危险？ —— 因为它在语法上完全合法，不报错不警告，只是结果行数被放大若干倍。 下游拿去求和，金额就跟着放大，而且往往很久才发现。表拼完了，下一篇 9.5 讲把一条查询套进另一条：子查询，和 NOT IN 遇见空值就归零的陷阱。
- ☐ （本篇完，下一篇 9.5 子查询）