SQL表集合运算技巧

SQLite 会把三个集合运算都干掉：UNION、INTERSECT、EXCEPT 都能用，但它们的优先级是一样的，从左到右执行，且不能靠括号去改变这个顺序。排序只能最后统一做，字段名以第一个查询为准。就这些“硬性规则”，挺直接的。

把集合运算想成数学里的交、并、差就不容易迷糊。举个直白的例子：你有两张小表，t_set1 和 t_set2，里面放了几种水果。做交集就是挑出两张表都有的水果；做并集会把两个表里出现过的都凑一块、去掉重复；做差集就是把第一张里有、第二张里没有的那些挑出来。语法上也就是把两个查询中间塞个 INTERSECT、UNION 或 EXCEPT 就行，但别只记关键词，这些操作背后还有几条必须留意的规矩。

先说字段这事儿：两个参与集合运算的查询里返回的列数得一致，而且每一列的数据类型得能相互兼容。SQLite 在类型检查上比较宽松，不像 PostgreSQL 那样较真，但最好别以为它会通融一切。字段名以第一个查询里头的列名为准，这点常常被踩坑——你如果在后面的查询里换了列名，ORDER BY、输出显示啥的还是看第一个查询的列名。

排序得放最后这事很重大。不能在两个子查询里各自排好序再把它们贴起来，最终排序必须在整个集合表达式的尾巴上写 ORDER BY，而且一般只能引用第一个查询的字段名或用列序号。如果中间自己先排好了再拼接，SQL 可能会报错，或者结果和你想的不一样。

再说个优先级的坑。SQLite 不区分这三者的优先级，统一按左到右执行，连括号也改不了这个顺序。举个实际的语句：SELECT 1 AS n UNION ALL SELECT 1 INTERSECT SELECT 1。放到 SQLite 上跑，跟别的数据库比，结果可能大相径庭。像 PostgreSQL 或 SQL Server 那类更“讲规矩”的数据库里，INTERSECT 的优先级比 UNION 高，结果会不一样。这意味着同一段 SQL 在不同数据库上能表现出不同结果，开发时如果本地环境跟线上数据库不一样，很容易出事。

要是你用的是 MySQL，那就得稍微变通一点。MySQL 没有 INTERSECT 和 EXCEPT 关键字，大家常用 INNER JOIN、LEFT JOIN、NOT IN 这些方式来替代。举例来说，交集可以用 INNER JOIN，再配合 DISTINCT；差集常用 LEFT JOIN 把能匹配上的去掉，或用 NOT IN。注意这些替代方案在语义和性能上并不总能跟原生集合运算画等号，碰到 NULL 或者字段组合复杂时，行为差异会放大。

说到数据库家底，每种数据库的支持情况不一样：PostgreSQL 在集合运算和 ALL 选项上许多细节都做好了；Oracle 习惯上用 MINUS 表明差集（这是老传统），新一些的 Oracle 版本（从 21c 开始）才慢慢兼容 EXCEPT 的变体；SQL Server 对 INTERSECT/EXCEPT 支持得也很好。别光看关键字是否存在，实践中还得看实现细节，列如是否支持 INTERSECT ALL、EXCEPT ALL（这些保留重复行的版本），不同厂商支持得参差不齐。

性能上有简单的经验可循。UNION 默认会去重，这一步一般要排序或哈希，数据量一大代价就明显。UNION ALL 则直接把两个结果拼起来，不去重，快不少。如果你能确定不会有重复，优先用 UNION ALL。集合运算一旦要做去重，数据库就得额外动脑筋——这时候比起直接用 JOIN，执行计划的差异会显得更明显。有时用 JOIN 写法在性能上更可控，但写法会复杂一点。

现实里的两个常见场景，说明怎么用会更直观。

一个场景是找“2020 年有入选但 2019 年没有入选”的员工。用差集的思路最清楚：把 year = 2020 的结果减去 year = 2019 的结果。如果你用的是 PostgreSQL、SQLite、SQL Server，可以直接写 EXCEPT；用 MySQL 时，就改成 LEFT JOIN，把 2019 那边联不上（NULL）的当作新入选的。实现时别忘了 emp_id、emp_name 这些列必须一一对应，好处理重复和 NULL。

另一个常见场景是把某用户的所有权限合并出来——一部分是通过角色继承来的，一部分是直接授予的。把两部分合并成并集，去掉重复，再跟权限表关联以显示权限名称和是否是直接赋予的标识，这套路很常见。这里用 CTE（临时结果集）能让 SQL 看起来更清楚，逻辑也更好维护。如果数据库支持 CTE，就先把两边的权限合并到一个临时表里，再做后续处理，会简单许多。

说替代写法的细节：交集可以用 INNER JOIN 加 DISTINCT 模拟，差集可以用 LEFT JOIN 把匹配不到的挑出来，或者用 NOT IN。但这些替代法在 NULL 处理上常常不靠谱。举个情况，如果被比对的列里有 NULL，NOT IN 可能会由于三值逻辑而把所有行都过滤掉，造成结果不对。用 LEFT JOIN + IS NULL 的方式一般更稳妥，但写法上会复杂不少。测试时要把空值、重复值都覆盖到位。

再举个具体的小示例，便于在本地试手。先建两张表，随意塞点水果进去，列如 t_set1 有 apple、banana、orange，t_set2 有 apple、banana、pear。用 INTERSECT 会返回 apple、banana；用 EXCEPT（t_set1 EXCEPT t_set2）会返回 orange；用 UNION（默认去重）会是 apple、banana、orange、pear。如果你在 MySQL 上实现等价功能，交集就得用 INNER JOIN，差集用 LEFT JOIN…IS NULL 之类的写法。这种对照测试能把不同数据库之间的差异看得清清楚楚。

还要注意表结构和字段顺序：集合运算要求列数量一致、类型兼容。SQLite 的动态类型会宽容一些，你放字符串和数字混着也许能过，但像 PostgreSQL 那类数据库会严格报错。别把开发环境和生产环境的数据库随意换来换去，字段类型在两个环境不一致会导致线上出错。

性能调优方面，记住两点。一是去重的操作会消耗资源，由于数据库一般要排序或哈希来完成这件事；二是如果能确定不会产生重复，尽量使用 UNION ALL，它省去去重步骤，快得多。集合运算里有时会触发临时表、磁盘排序，这在数据量大时代价显著。JOIN 的执行计划有时更可预测，但你要把索引、连接条件这些东西都规划好。

还有一个常见的兼容策略：当你要在多种数据库之间保持可移植性，最稳妥的做法是把关键逻辑改写成通用的 JOIN/NOT IN/LEFT JOIN 形式，哪怕 SQL 稍长一点，兼容性更高。这样一来，即使在那些不支持 INTERSECT/EXCEPT 的数据库上，也能得到一致的结果。当然，这样改写后要额外注意 NULL 和重复行为的差别，并多写测试用例去覆盖各种边界情况。

说到测试，不要掉以轻心。重复数据、NULL 值、字段顺序不一致、以及你在 ORDER BY 里引用的字段名是否来自第一个查询，这些都会触发不一样的表现。把这些边界场景都列成测试用例，在开发环境里跑一遍，能提前把许多线上麻烦扼杀在摇篮里。最后一句实用的提议是：开发环境用的数据库最好和生产一致，要么在代码里明确针对数据库差异做兼容判断，这样日后查问题时心里踏实些。