INTERSECT 返回两个查询结果中都存在的整行并自动去重,要求列数相同、对应列类型兼容,ORDER BY 仅允许出现在整个语句末尾,MySQL 8.0.31+ 才支持且行为受限。

INTERSECT 会自动去重,且要求列数和类型严格匹配
INTERSECT 返回两个查询结果中都存在的行,并隐式去重——哪怕某行在各自结果里出现多次,最终也只出现一次。它不是简单“取共同值”,而是对**整行**做集合交集运算,因此两侧查询必须返回相同数量的列,对应位置的列类型需兼容(例如 INT 和 BIGINT 通常可隐式转换,但 VARCHAR 和 DATE 就不行)。常见错误是列数不一致导致报错 ERROR: each INTERSECT query must have the same number of columns。
- 确保两个
SELECT的字段个数完全相等 - 列顺序要一致:
SELECT a, b FROM t1和SELECT b, a FROM t2无法直接 INTERSECT - 若只需比对某几列,用子查询提前投影,避免拖入无关字段
ORDER BY 只能出现在整个 INTERSECT 语句末尾
不能在任一子查询后加 ORDER BY,否则语法报错 ERROR: ORDER BY in INTERSECT subquery is not allowed。排序必须放在整个 INTERSECT 表达式之后,且只能基于最终输出的列别名或位置编号(如 ORDER BY 1)。
- 错误写法:
(SELECT id FROM t1 ORDER BY id) INTERSECT (SELECT id FROM t2) - 正确写法:
SELECT id FROM t1 INTERSECT SELECT id FROM t2 ORDER BY id - 若需按计算字段排序,记得在顶层 SELECT 中定义别名,再在末尾
ORDER BY引用
替代方案:用 INNER JOIN 或 EXISTS 实现带重复、带条件的“类交集”
INTERSECT 无法保留重复行,也不支持在交集逻辑中附加额外条件(比如“只取状态为 active 的交集”)。这时更灵活的做法是改用 INNER JOIN 或 EXISTS。
- 要保留重复:用
INNER JOIN关联两表,显式控制连接条件和去重逻辑 - 要加业务过滤:把条件写进
JOIN ON或WHERE,而不是依赖 INTERSECT 的纯数据匹配 -
EXISTS在单侧主键明确时性能常优于 INTERSECT,尤其当一侧数据量远小于另一侧
PostgreSQL 和 SQL Server 支持完整 INTERSECT,MySQL 不原生支持
MySQL 8.0.31+ 虽引入了 INTERSECT,但仅限于 UNION 系列操作符的语法补全,实际执行仍可能被重写为 INNER JOIN,且不支持 INTERSECT ALL(即保留重复)。而 PostgreSQL 和 SQL Server 对标准支持更彻底,包括 INTERSECT ALL(如果需要保留重复,得换思路,比如用窗口函数计数后 join)。
- 跨数据库迁移时,别默认假设
INTERSECT行为一致 - 在 MySQL 中遇到
ERROR 1064: You have an error in your SQL syntax,先确认版本是否 ≥ 8.0.31 - 生产环境若需强兼容性,建议统一用
INNER JOIN替代,可控性更高
TEXT,一边是 CHAR(10),看似都是字符串,但某些数据库会拒绝 INTERSECT。动手前先 SELECT ... LIMIT 1 看两边的 pg_typeof() 或 SQL_VARIANT_PROPERTY 结果,比猜靠谱。

















