如何自动检测卡牌组合表中缺失的双向组合？

我有一张包含COL_1和COL_2列的表A：

• COL_1存储一副卡牌（共52张）的ID；
• COL_2存储排除自身的卡牌组合。

如何自动检测类似3 & 4这样的组合是否缺失？

示例表 #

CREATE TABLE deck(
  id int,
  col_1 int,
  col_2 int
);

INSERT INTO deck VALUES
(1, 1, 2),
(2, 1, 3),
(3, 1, 4),
(4, 2, 1),
(5, 2, 3),
(6, 2, 4),
(7, 3, 1),
(8, 3, 2),
(9, 4, 1),
(10, 4, 2),
(11, 4, 3)

在这个示例中，卡牌3与4的组合（即col_1=3, col_2=4）缺失。

原查询的问题 #

我尝试了以下查询：

SELECT * FROM desk WHERE col_2 NOT IN (
SELECT col_1 FROM deck GROUP BY col_1)

但这个查询逻辑有误，它只是检查col_2是否是有效的卡牌ID，并没有针对每个COL_1校验所有应该存在的COL_2组合。

正确的解决方案 #

要找出缺失的组合，需要先生成所有理论上应该存在的非自身配对，再对比现有表找出不在其中的条目：

-- 生成所有合法的卡牌组合（排除自身配对）
WITH all_possible AS (
    SELECT a.col_1 AS card1, b.col_1 AS card2
    FROM (SELECT DISTINCT col_1 FROM deck) a
    CROSS JOIN (SELECT DISTINCT col_1 FROM deck) b
    WHERE a.col_1 != b.col_1
)
-- 筛选出缺失的组合
SELECT card1, card2
FROM all_possible
WHERE NOT EXISTS (
    SELECT 1
    FROM deck
    WHERE deck.col_1 = all_possible.card1
      AND deck.col_2 = all_possible.card2
);

逻辑说明 #

1. all_possible 公共表表达式（CTE）通过自连接所有唯一卡牌ID，生成了所有card1≠card2的配对，这是所有应该存在的组合。
2. 利用NOT EXISTS子查询，找出那些在all_possible中但未出现在deck表中的组合，这些就是缺失的条目。

针对示例表，该查询会返回：

card1 | card2
------|------
3     | 4

内容的提问来源于stack exchange，提问作者Leandro Bardelli