如何对5个来自不同表、单查询输出百万级数据的SQL结果取交集

前置要求：5条SQL查询输出的字段数量、字段顺序、对应字段的数据类型必须完全一致，否则无法做交集运算

方案1：原生INTERSECT实现（支持的数据库优先选择） #

适用Oracle、PostgreSQL、SQL Server等支持INTERSECT关键字的数据库，该关键字天然用于取多个结果集的交集，由数据库内核做了执行优化，比手动实现的效率更高，默认自动对结果去重。
示例代码如下：

-- 按顺序拼接5个查询，中间用INTERSECT连接即可
SELECT 匹配字段1, 匹配字段2, 匹配字段N FROM 表1 WHERE [查询1的筛选条件]
INTERSECT
SELECT 匹配字段1, 匹配字段2, 匹配字段N FROM 表2 WHERE [查询2的筛选条件]
INTERSECT
SELECT 匹配字段1, 匹配字段2, 匹配字段N FROM 表3 WHERE [查询3的筛选条件]
INTERSECT
SELECT 匹配字段1, 匹配字段2, 匹配字段N FROM 表4 WHERE [查询4的筛选条件]
INTERSECT
SELECT 匹配字段1, 匹配字段2, 匹配字段N FROM 表5 WHERE [查询5的筛选条件]

如果需要保留结果集中的重复行，可以替换为INTERSECT ALL，注意该语法不是所有数据库都支持。

方案2：INNER JOIN关联实现（兼容所有数据库，含MySQL） #

MySQL等不支持INTERSECT的数据库，可以通过子查询+内连接的方式实现交集，关联条件为所有需要匹配的交集字段：

SELECT DISTINCT q1.匹配字段1, q1.匹配字段2, q1.匹配字段N
FROM (
  SELECT 匹配字段1, 匹配字段2, 匹配字段N FROM 表1 WHERE [查询1的筛选条件]
) q1
INNER JOIN (
  SELECT 匹配字段1, 匹配字段2, 匹配字段N FROM 表2 WHERE [查询2的筛选条件]
) q2 ON q1.匹配字段1 = q2.匹配字段1 AND q1.匹配字段2 = q2.匹配字段2 -- 所有需要匹配的字段都要加关联条件
INNER JOIN (
  SELECT 匹配字段1, 匹配字段2, 匹配字段N FROM 表3 WHERE [查询3的筛选条件]
) q3 ON q1.匹配字段1 = q3.匹配字段1 AND q1.匹配字段2 = q3.匹配字段2
INNER JOIN (
  SELECT 匹配字段1, 匹配字段2, 匹配字段N FROM 表4 WHERE [查询4的筛选条件]
) q4 ON q1.匹配字段1 = q4.匹配字段1 AND q1.匹配字段2 = q4.匹配字段2
INNER JOIN (
  SELECT 匹配字段1, 匹配字段2, 匹配字段N FROM 表5 WHERE [查询5的筛选条件]
) q5 ON q1.匹配字段1 = q5.匹配字段1 AND q1.匹配字段2 = q5.匹配字段2

如果数据量过大，可以先将每个子查询的结果写入临时表，给临时表的关联字段建立索引后再做连接，性能会提升数倍。

百万级数据量的优化建议 #

• 裁剪多余字段：每个查询仅保留做交集匹配需要的字段，不要输出无关字段，大幅减少数据扫描和内存占用
• 提前过滤数据：每个查询的筛选条件尽可能写全，减少单条查询的输出结果量，后续交集运算的成本会线性降低
• 索引优化：所有查询用到的筛选字段、交集匹配字段提前建立索引，避免全表扫描
• 分片运算：如果数据库内存不足，可对匹配字段做分片（比如按ID范围、时间范围分片），分批次计算交集后再合并结果

内容的提问来源于stack exchange，提问作者dark prince