SQL中能否关联集合操作（如EXCEPT）的两侧查询？

核心结论 #

不能直接关联EXCEPT、UNION、INTERSECT这类集合操作的左右两侧查询。这类操作的本质是对两个独立生成的结果集进行整体的集合运算，左右两侧的查询会各自执行完毕后再做对比，无法共享关联条件来实现“逐组对比”的逻辑。

为什么你的尝试没生效？ #

你写的语句里，左右两侧的查询都关联了numbers表，但EXCEPT会先把左边所有id = n的行（即所有id从1到100的行，去掉id列）生成一个大结果集，再把右边所有id = n+1的行（id从2到101的行，去掉id列）生成另一个大结果集，最后返回左边有但右边没有的行——这是全局的差异，而不是每组(1,2)、(2,3)的独立差异，自然只会返回全局层面的差异结果。

实现批量id对对比的正确方式 #

如果要批量对比每组(id, id+1)的列差异，不需要用EXCEPT，可以通过自关联或者生成id对后逐组对比：

方法1：生成id对后逐组对比列 #

WITH numbers AS (
  SELECT n FROM UNNEST(GENERATE_ARRAY(1, 100)) n
),
sample_data AS (
  SELECT 
    n AS id,
    n2, 
    DATE_ADD(CURRENT_DATE(), INTERVAL n2 DAY) d 
  FROM numbers 
  CROSS JOIN UNNEST(GENERATE_ARRAY(numbers.n, 100)) n2
)
-- 生成所有需要对比的id对：(n, n+1)
SELECT
  pair.id_left,
  pair.id_right,
  -- 标记左侧有但右侧没有的行
  'left_only' AS diff_type,
  left_data.n2,
  left_data.d
FROM (
  SELECT n AS id_left, n+1 AS id_right FROM numbers WHERE n+1 <= 100
) pair
JOIN sample_data left_data ON left_data.id = pair.id_left
LEFT JOIN sample_data right_data 
  ON right_data.id = pair.id_right
  AND right_data.n2 = left_data.n2
  AND right_data.d = left_data.d
WHERE right_data.id IS NULL

UNION ALL

SELECT
  pair.id_left,
  pair.id_right,
  'right_only' AS diff_type,
  right_data.n2,
  right_data.d
FROM (
  SELECT n AS id_left, n+1 AS id_right FROM numbers WHERE n+1 <= 100
) pair
JOIN sample_data right_data ON right_data.id = pair.id_right
LEFT JOIN sample_data left_data 
  ON left_data.id = pair.id_left
  AND left_data.n2 = right_data.n2
  AND left_data.d = right_data.d
WHERE left_data.id IS NULL
ORDER BY id_left, diff_type;

方法2：用数组简化列对比 #

如果列很多，可以把除id外的列转成数组，避免逐个列写条件：

WITH numbers AS (
  SELECT n FROM UNNEST(GENERATE_ARRAY(1, 100)) n
),
sample_data AS (
  SELECT 
    n AS id,
    -- 把需要对比的列转成数组
    [CAST(n2 AS STRING), CAST(d AS STRING)] AS data_array,
    n2,
    d
  FROM numbers 
  CROSS JOIN UNNEST(GENERATE_ARRAY(numbers.n, 100)) n2
),
id_pairs AS (
  SELECT n AS id_left, n+1 AS id_right FROM numbers WHERE n+1 <= 100
)
SELECT
  id_left,
  id_right,
  diff_type,
  n2,
  d
FROM (
  -- 左侧独有的行
  SELECT
    id_left,
    id_right,
    'left_only' AS diff_type,
    s.n2,
    s.d
  FROM id_pairs p
  JOIN sample_data s ON s.id = p.id_left
  WHERE NOT EXISTS (
    SELECT 1 FROM sample_data s2 
    WHERE s2.id = p.id_right AND s2.data_array = s.data_array
  )
  
  UNION ALL
  
  -- 右侧独有的行
  SELECT
    id_left,
    id_right,
    'right_only' AS diff_type,
    s.n2,
    s.d
  FROM id_pairs p
  JOIN sample_data s ON s.id = p.id_right
  WHERE NOT EXISTS (
    SELECT 1 FROM sample_data s2 
    WHERE s2.id = p.id_left AND s2.data_array = s.data_array
  )
)
ORDER BY id_left, diff_type;

补充说明 #

集合操作（EXCEPT等）设计的目标是处理无分组的全局集合差异，如果需要逐组对比，必须先通过关联、生成id对等方式把每组的左右数据关联起来，再做行级的差异判断，而不是试图让EXCEPT的两侧查询关联。

内容的提问来源于stack exchange，提问作者Steven Ensslen