波仔
🛢️ 第9章 数据查询
📄 一句话概括

UNION 拼的是行，JOIN 拼的是列，这是两件完全不同的事。 UNION 会去重，UNION ALL 不会；能确定不重复时一律写 UNION ALL。 ORDER BY 和 LIMIT 只能写在最后一段之后，因为它们作用于最终结果。

📝 本章和前面几章的关系

9.6 只有两个关键字：UNION 和 UNION ALL。语法一分钟，坑有三个。 这一篇的全外连接拼法，是上一篇 9.4 里「MySQL 不支持 FULL OUTER JOIN」的补丁。

一、UNION 与 UNION ALL

UNION 把两段查询的结果竖着接起来，列数要对齐，行是一条条往下排。 它和 JOIN 没有可比性：一个是加行，一个是加列。

写法 结果行数 说明
UNION 4000 重复的并掉
UNION ALL 5001 一条不落

两段各给 3000 行和 2001 行，中间有 1001 行重叠。 UNION 并掉重叠后是 4000，UNION ALL 原样相加是 5001。 去重不是白来的：它得先排序或者建临时表才能比对。 这次只差了两成，因为两段本来就要全表扫；换成大数据量，差距会拉开。 所以能确定两段不会重复时，一律写 UNION ALL，这是最省事的一处优化。 常见的例子是「按状态拆成两段再拼」，两段互斥，根本不会重复。 反过来说，UNION 也不保证顺序。 它的排序只用于去重，不是给你看的；要顺序得自己写 ORDER BY。

二、列数、列序、类型

两段的列数必须一致，不一致直接报错，原文很直白： SELECTs to the left and right of UNION do not have the same number of result columns。 列的顺序也要对齐：第一段的第 1 列和第二段的第 1 列并成一列，不看列名。 所以两段列顺序不一致时不会报错，但数据会错位——这种错最难发现。 类型按第一段走。沙箱给了数字 1 和字符串 abc 两行，各留各的类型。 MySQL 会按第一段的类型统一，把 abc 转成数字 0，于是结果变成 1 和 0。 所以合并不同类型的列时，MySQL 那边要自己先 CAST，别指望它猜。 「合并查询」这件事，两个引擎对「类型不同怎么办」的处理差得挺远。

三、ORDER BY 只能写在最后

ORDER BY 和 LIMIT 是「对最终结果」的操作，只能出现在最后一段之后。 写在第一段会报 ORDER BY clause should come after UNION not before。 想给某一段单独限量，得先把那一段包成派生表，再合并。 直接在第一段后面加 LIMIT 是语法错，加括号在沙箱里也是语法错。 这也说明合并查询的优先级：UNION 比 ORDER BY 更早绑定。 所以整句是「先合并，再排序，最后限量」，顺序不能倒。

四、用 UNION 拼出全外连接

MySQL 不支持 FULL OUTER JOIN，但用 LEFT UNION RIGHT 能拼出来。 原理很简单：两边都保留的结果，等于「左边全留」加上「右边独有」。 这次左连接 5000 行、右连接 4865 行，合并之后还是 5000 行。 因为每个类别都有商品，右连接没有「右表独有」的行。 万一有个类别一个商品都没有，那几行才会补进来——这正是全外连接要的效果。 所以这个拼法是对的，只是这次的数据没让它显出差别。

五、INTERSECT 与 EXCEPT

这两个是 UNION 的兄弟：一个取交集，一个取差集。 教材没讲，因为 MySQL 到 8.0.31 才支持，很多老库上跑不了。 沙箱里的 SQLite 一直支持，所以这里能跑给你看。 两个都是「先算两段，再按行比对」，语义比用 IN 和 NOT IN 写更清楚。 注意 EXCEPT 是「第一段有、第二段没有」，方向不能反。 它和 NOT IN 有一点不同：EXCEPT 走的是集合运算，不受空值那套三值逻辑影响。

六、动手 Demo：合并查询，与去重的那一笔开销

两段查询刻意设计成有重叠：一段取 id ≤ 3000，另一段取 2000 到 4000。 重叠 1001 行，正好把「去重」和「不去重」的差别摆出来。

import sqlite3, time

c = sqlite3.connect(u"d041.db")
q1 = u"SELECT id FROM tb_goods WHERE id <= 3000"
q2 = u"SELECT id FROM tb_goods WHERE id BETWEEN 2000 AND 4000"

# ① 去重与不去重
print(c.execute(u"SELECT COUNT(*) FROM (%s UNION %s)" % (q1, q2)).fetchone())
print(c.execute(u"SELECT COUNT(*) FROM (%s UNION ALL %s)" % (q1, q2)).fetchone())

# ② 列数不一致
try:
    c.execute(u"SELECT id FROM tb_goods UNION SELECT id, name FROM tb_goods")
except sqlite3.OperationalError as e:
    print(e)

# ③ ORDER BY 只能写在最后
try:
    c.execute(u"SELECT id FROM tb_goods ORDER BY id LIMIT 3 "
              u"UNION SELECT id FROM tb_goods")
except sqlite3.OperationalError as e:
    print(e)

# ④ 拼出全外连接
c.execute(u"SELECT g.id FROM tb_goods g LEFT JOIN tb_cat k ON g.cat_id = k.id "
          u"UNION SELECT g.id FROM tb_goods g RIGHT JOIN tb_cat k "
          u"ON g.cat_id = k.id").fetchall()

实跑输出：

--------------------------------------------------------------------
第四十一课：合并查询，与去重的那一笔开销
--------------------------------------------------------------------

  **教材 9.6 讲 `UNION`。它是「把两段查询的结果竖着接起来」，不是横向配对。**
  **连接（`JOIN`）拼的是列，合并（`UNION`）拼的是行，这两个别混。**

  一、UNION 与 UNION ALL：去重是有代价的
  --------------------------------------------------------------------

  写法         结果行数  耗时
  --------------------------------
  `UNION`      4000      3.26 毫秒
  `UNION ALL`  5001      2.92 毫秒
  **两段各给 3000 行和 2001 行，中间有 1001 行是重叠的。**
  **`UNION` 把重复的并掉，4000 行；`UNION ALL` 一条不落，5001 行。**
  **去重不是白来的：它得先把结果排序或者建一张临时表才能比对。**
  **所以能确定两段不会重复时，一律写 `UNION ALL`，这是最省事的一处优化。**
  **反过来说，`UNION` 也不保证顺序——它的排序只用于去重，不是给你看的。**
  **要顺序得自己写 `ORDER BY`。**

  二、两段的列数必须一致
  --------------------------------------------------------------------

  情形         结果
  -----------------
  列数 1 对 2  报错
  列数 2 对 1  报错
  **两句都报错，意思很直白，原文是这一句：**
  SELECTs to the left and right of UNION do not have the same number of result columns
  **列的顺序也要对齐：第一段的第 1 列和第二段的第 1 列并成一列，不看列名。**
  **所以两段的列顺序不一致时，不会报错，但数据会错位——这种错最难发现。**

  三、类型按第一段走
  --------------------------------------------------------------------
  1
  ---
  1
  abc
  2 rows in set
  **沙箱给了两行：数字 1 和字符串 `abc`，各留各的类型。**
  **MySQL 会按第一段的类型统一，把 `abc` 转成数字 0，于是结果变成 1 和 0。**
  **所以合并不同类型的列时，MySQL 那边要自己先 `CAST`，别指望它猜。**

  四、ORDER BY 只能写在最后
  --------------------------------------------------------------------

  写法                 结果
  -----------------------------------------------------------------------------
  `ORDER BY` 在第一段  报错：ORDER BY clause should come after UNION not before
  第一段加括号         报错：near "(": syntax error
  外面套一层再取三条   成功，3 行
  **前两行都失败，第三行才成。**
  **因为 `ORDER BY` 和 `LIMIT` 是「对最终结果」的操作，只能出现在最后一段之后。**
  **想给某一段单独限量，得先把那一段包成派生表，再合并。**
  **这也说明合并查询的优先级：`UNION` 比 `ORDER BY` 更早绑定。**

  五、用 UNION 拼出「全外连接」
  --------------------------------------------------------------------

  写法                 结果行数
  -----------------------------
  `LEFT JOIN` 单独跑   5000
  `RIGHT JOIN` 单独跑  4865
  两者 `UNION`         5000
  **第三行不是前两行相加，因为两边大部分行是重复的，`UNION` 把它们并掉了。**
  **这次每个类别都有商品，所以右连接没有「右表独有」的行，合并结果就等于左连接。**
  **万一有个类别一个商品都没有，那几行才会补进来——这正是「全外连接」要的效果。**
  **MySQL 不支持 `FULL OUTER JOIN` 这个语法，但用 `UNION` 能拼出来。**

  六、INTERSECT 和 EXCEPT
  --------------------------------------------------------------------

  写法         结果行数  含义
  -------------------------------------------
  `INTERSECT`  1001      两段都有的行
  `EXCEPT`     1999      第一段有、第二段没有
  **这两个是 `UNION` 的兄弟：一个取交集，一个取差集。**
  **教材没讲，因为 MySQL 到 8.0.31 才支持，很多老库上跑不了。**
  **沙箱里的 SQLite 一直支持，所以这里能跑给你看。**

4000 和 5001 这两个数的关系值得记：5001 减 4000 等于 1001，正好是重叠的行数。 所以「UNION 去掉了多少」是可以验算的，不用猜。 两个报错都指向同一件事：UNION 是「整句级」的运算。 它管的是两段的最终结果，所以列要对齐，排序要放到最后。 最后那个全外连接给出 5000 行，和左连接一样。 看着像是 UNION 没起作用，其实是右连接那 4865 行全都包含在左连接里了。 INTERSECT 给 1001 行、EXCEPT 给 1999 行，这两个数也能互相对上：3000 = 1001 + 1999。

30 秒实验

  1. 把 UNION 换成 UNION ALL，看行数从 4000 变成 5001。
  2. 把第二段的 BETWEEN 2000 AND 4000 改成 BETWEEN 4000 AND 5000，看重叠还剩多少。
  3. 给 UNION ALL 加一个 ORDER BY id，看它排的是最终结果还是第一段。

常见误区

  1. 把 UNION 当成「连接」用，以为能横向拼列。
  2. 该用 UNION ALL 的地方写成 UNION，白白多做一趟去重。
  3. 在第一段后面写 ORDER BY，以为能控制第一段的顺序。
自测三问（点开对答案） 1. `UNION` 和 `JOIN` 的区别，一句话怎么说？ —— `UNION` 把结果竖着接起来，行变多；`JOIN` 把表横向拼起来，列变多。 2. 什么时候该用 `UNION ALL`？ —— 能确定两段不会产生重复行的时候。它省掉去重那一趟排序或临时表。 3. `ORDER BY` 为什么不能写在第一段后面？ —— 因为它作用于最终结果，而 `UNION` 比它更早绑定。要控制某一段的顺序，得先把那一段包成派生表。

下一篇 9.7 讲别名：两种别名，和它们各自的可见范围。

相关笔记