Oracle SQL多OR条件JOIN查询仅返回首个匹配记录的实现

问题背景 #

现有两张Oracle表：

表1：a_test（仅含ID字段） #

CREATE TABLE a_test (id_test VARCHAR2(20));

INSERT INTO a_test VALUES ('AAA');
INSERT INTO a_test VALUES ('BBB');
INSERT INTO a_test VALUES ('CCC');

表2：dummy_test（含三列，部分列引用a_test的ID） #

CREATE TABLE dummy_test (id_first VARCHAR2(20), id_second VARCHAR2(20), id_third VARCHAR2(20));

INSERT INTO dummy_test VALUES ('AAA', 'test', 'test');
INSERT INTO dummy_test VALUES ('test', 'test', 'BBB');
INSERT INTO dummy_test VALUES ('CCC', 'AAA', 'test');
INSERT INTO dummy_test VALUES ('test', 'BBB', 'CCC');
INSERT INTO dummy_test VALUES ('AAA', 'BBB', 'CCC');

需要查询返回a_test.id_test, dummy_test.*，且匹配优先级为：

1. 优先匹配a_test.id_test = dummy_test.id_first，匹配则返回，不再检查后两列
2. 若第一列不匹配，检查a_test.id_test = dummy_test.id_second，匹配则返回
3. 前两列都不匹配时，检查a_test.id_test = dummy_test.id_third，匹配则返回

实现SQL #

使用UNION ALL分三段实现优先级逻辑，同时排除已被高优先级匹配的行：

-- 第一优先级：匹配id_first
SELECT a.id_test, d.*
FROM a_test a
JOIN dummy_test d ON a.id_test = d.id_first

UNION ALL

-- 第二优先级：id_first无匹配，但id_second匹配
SELECT a.id_test, d.*
FROM a_test a
JOIN dummy_test d ON a.id_test = d.id_second
WHERE NOT EXISTS (SELECT 1 FROM a_test WHERE id_test = d.id_first)

UNION ALL

-- 第三优先级：id_first、id_second均无匹配，但id_third匹配
SELECT a.id_test, d.*
FROM a_test a
JOIN dummy_test d ON a.id_test = d.id_third
WHERE NOT EXISTS (SELECT 1 FROM a_test WHERE id_test = d.id_first)
  AND NOT EXISTS (SELECT 1 FROM a_test WHERE id_test = d.id_second)

ORDER BY a.id_test;

逻辑说明 #

• 第一段直接取最高优先级的匹配行，确保符合条件的行最先被返回
• 第二段通过NOT EXISTS过滤掉已经被第一优先级匹配过的行，只处理第一列无匹配的情况
• 第三段同理，过滤掉前两列已有匹配的行，只处理前两列都不满足的情况
• 最后用ORDER BY按id_test排序，让结果更规整

预期结果 #

执行后返回的结果如下：

比如dummy_test里的('AAA','BBB','CCC')只会和AAA匹配（第一优先级），不会重复和BBB、CCC关联；('test','BBB','CCC')因为第一列无匹配，所以和BBB关联（第二优先级），不会再匹配CCC，完全符合需求。

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