如何查询未出现在Transactions_Products中的Products表prod_id

为什么NOT IN会失效？ #

当Transactions_Products表的prod_id字段存在NULL值时，NOT IN会因为SQL的三值逻辑（TRUE/FALSE/UNKNOWN）返回空结果。因为任何值和NULL比较都会得到UNKNOWN，NOT IN会过滤掉这些情况，最终没有符合条件的记录。

推荐的替代方案 #

方案1：LEFT JOIN + IS NULL #

这是最通用且高效的写法，不受NULL值影响：

SELECT p.prod_id
FROM Products p
LEFT JOIN Transactions_Products tp ON p.prod_id = tp.prod_id
WHERE tp.prod_id IS NULL;

逻辑：将Products和Transactions_Products左连接，保留所有Products的记录，然后筛选出Transactions_Products端没有匹配（即tp.prod_id为NULL）的产品ID。

方案2：NOT EXISTS #

同样不受NULL值影响，执行效率在多数数据库中与LEFT JOIN相当：

SELECT p.prod_id
FROM Products p
WHERE NOT EXISTS (
    SELECT 1
    FROM Transactions_Products tp
    WHERE tp.prod_id = p.prod_id
);

逻辑：检查Products中的每个prod_id，确认其在Transactions_Products中不存在匹配记录。

方案3：修复NOT IN的写法（仅适用于无NULL的场景） #

如果能确认Transactions_Products.prod_id没有NULL值，可以给NOT IN语句加上NULL过滤：

SELECT prod_id
FROM Products
WHERE prod_id NOT IN (
    SELECT prod_id
    FROM Transactions_Products
    WHERE prod_id IS NOT NULL -- 关键：排除NULL值
);

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