查询未返回正确结果：如何找出项目缺失的必填标签？

我有两张表：

• 表1：存储每个项目ID对应的文件元数据标签，每个项目ID可关联多个标签
• 表2：存储所有项目ID的必填标签

表数据 #

表1（各ID的标签） #

ID  Tag
37616   CORE_CCA
37616   CSHOT_FILE
37616   DRILL_DEV
37616   DRILL_EOWR
37616   DWL_WIRE
37616   GEOL_BIO
37616   GEOL_GEOW
37616   GPHYS_CSHOT
37616   GPHYS_GEN
37616   JWL_AUDIT
37616   JWL_FILE
37616   LOG_COMP
37616   LOG_CORE
37616   LOG_LITH
37616   LOG_MUD
37616   LOG_VEL
37616   LOG_WIRE
37616   VSP_SEGY
37616   WDD_FILE

表2（必填标签） #

Tag
DRILL_DEV
DRILL_EOWR
GEOL_GEOW
GEOL_MUD
LOG_CASE
LOG_COMP
LOG_MUD
PRE_DPROG
PRE_GPROG
WDD_FILE

预期结果 #

需要找出表1中指定项目（示例为37616，原预期结果里的31716应为笔误）缺失的必填标签，正确结果应为：

ID  Tag
37616   GEOL_MUD
37616   LOG_CASE
37616   PRE_DPROG
37616   PRE_GPROG

注：原预期结果中的WDD_FILE在表1中已存在，属于笔误。

已尝试的错误SQL #

1. 左连接语句（未关联项目ID，且NULL判断错误）：

select e.ID, e.tag, m.tag
from table2 m left join table1 e on m.tag = e.tag

添加WHERE e.tag = NULL后结果仍不对，因为SQL中NULL不能用=判断，必须用IS NULL，且未关联项目ID导致匹配逻辑错误。

2. EXISTS子查询（逻辑方向错误）：

select e.id, e.tag
from table1 e 
where exists (select * from table2 where tag = e.tag)

该语句是找出表1中属于必填标签的项，和需求（找缺失项）完全相反。

核心逻辑是：先生成目标项目ID与所有必填标签的全组合，再排除表1中已存在的标签组合。

方法1：笛卡尔积 + LEFT JOIN 筛选缺失项 #

-- 针对单个项目37616查询，如需批量检查所有项目，去掉WHERE ID=37616即可
WITH target_project AS (
    SELECT DISTINCT ID FROM table1 WHERE ID = 37616
)
SELECT tp.ID, t2.Tag
FROM target_project tp
CROSS JOIN table2 t2
LEFT JOIN table1 t1 ON tp.ID = t1.ID AND t2.Tag = t1.Tag
WHERE t1.Tag IS NULL;

• 步骤：
  1. 用CTE获取要检查的项目ID（支持单个或多个项目）
  2. 用CROSS JOIN生成项目与所有必填标签的全量组合
  3. 左连接表1，筛选出表1中不存在的组合（t1.Tag IS NULL即为缺失项）

方法2：NOT EXISTS子查询 #

SELECT tp.ID, t2.Tag
FROM (SELECT DISTINCT ID FROM table1 WHERE ID = 37616) tp
CROSS JOIN table2 t2
WHERE NOT EXISTS (
    SELECT 1 FROM table1 t1
    WHERE t1.ID = tp.ID AND t1.Tag = t2.Tag
);

• 步骤：
  同样先生成全量组合，再通过NOT EXISTS排除表1中已存在的记录，直接得到缺失项。

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