Oracle SQL如何找出主表各ID缺失的参考表两列组合？

要找出每个ID缺失的必填(ORG, DSIT)组合，核心是先生成每个ID应具备的完整参考组合，再和主表对比找出缺失项。以下是两种符合你需求的实现方式：

方式一：返回缺失的(ID, ORG, DSIT)组合 #

WITH required_combinations AS (
    -- 生成所有ID对应的完整必填组合（主表唯一ID × 参考表所有行）
    SELECT DISTINCT m.ID, r.ORG, r.DSIT
    FROM main_table m
    CROSS JOIN reference_table r
)
-- 筛选主表中不存在的组合
SELECT rc.ID, rc.ORG, rc.DSIT
FROM required_combinations rc
LEFT JOIN main_table mt 
  ON rc.ID = mt.ID 
  AND rc.ORG = mt.ORG 
  AND rc.DSIT = mt.DSIT
WHERE mt.ID IS NULL;

执行后会得到你想要的结果：

ID ORG DSIT
-----------
1  C   CC

方式二：返回带提示信息的结果 #

如果需要输出可读性更强的提示文本，可以基于上面的结果拼接字符串：

WITH required_combinations AS (
    SELECT DISTINCT m.ID, r.ORG, r.DSIT
    FROM main_table m
    CROSS JOIN reference_table r
),
missing_items AS (
    SELECT rc.ID, rc.ORG, rc.DSIT
    FROM required_combinations rc
    LEFT JOIN main_table mt 
      ON rc.ID = mt.ID 
      AND rc.ORG = mt.ORG 
      AND rc.DSIT = mt.DSIT
    WHERE mt.ID IS NULL
)
SELECT ID, CONCAT(ORG, ' and ', DSIT, ' is missing') AS MESSAGE
FROM missing_items;

执行结果：

ID  MESSAGE
-----------
1   C and CC is missing

补充：用NOT EXISTS实现（性能更优） #

如果数据量较大，NOT EXISTS的写法通常比左连接更高效：

SELECT DISTINCT m.ID, r.ORG, r.DSIT
FROM main_table m
CROSS JOIN reference_table r
WHERE NOT EXISTS (
    SELECT 1
    FROM main_table mt
    WHERE mt.ID = m.ID 
      AND mt.ORG = r.ORG 
      AND mt.DSIT = r.DSIT
);

为什么之前的尝试没成功？ #

你提到的左连接没关联到具体ID，是因为没有先通过交叉连接生成每个ID的完整必填组合，直接左连接无法建立ID和参考组合的对应关系。而先生成所有应有的组合再对比，就能精准定位每个ID缺失的项。

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