如何通过自连接高效查询两列唯一组合（禁用指定函数）

需求说明 #

给定如下示例表数据：

SELECT
    t.col1,
    t.col2
FROM (
    VALUES
        ('1212', '2323'),
        ('2323', '1212'),
        ('3434', '1212'),
        ('3434', '4545')
) AS t(col1, col2)

需要找出col1和col2的唯一无序组合（即('1212','2323')与('2323','1212')视为同一组合），最终得到3条结果，且要求：

• 不使用数组函数或LEAST()/GREATEST()函数
• 采用自连接方案
• 优化查询性能

自连接实现方案 #

通过左连接匹配反向组合，再筛选出唯一的组合记录：

SELECT DISTINCT t1.col1, t1.col2
FROM t t1
LEFT JOIN t t2 
  ON t1.col1 = t2.col2 
  AND t1.col2 = t2.col1
WHERE 
  -- 没有反向组合的记录直接保留
  t2.col1 IS NULL 
  -- 存在反向组合时，只保留col1字典序小于col2的那条，避免重复
  OR t1.col1 < t1.col2;

性能优化要点 #

• 建立复合索引：为表建立两个复合索引，覆盖正向和反向匹配的场景，让数据库快速定位反向记录，避免全表扫描：

  CREATE INDEX idx_col1_col2 ON t(col1, col2);
  CREATE INDEX idx_col2_col1 ON t(col2, col1);
  

• 按需保留DISTINCT：如果表中不存在完全重复的(col1,col2)正向记录，可去掉DISTINCT以提升性能；若存在重复记录，则保留DISTINCT完成去重。

验证结果 #

执行查询后，会得到预期的3条唯一组合：

• ('1212', '2323')
• ('3434', '1212')
• ('3434', '4545')

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