如何获取所有未入驻门店的商品？求最优SQL实现方案

现有两张表：

• productstore表：记录商品入驻门店信息（示例里Product 1入驻了门店1、2、3）
• stores表：存储所有门店信息（包含门店1、2、3、4）

需要找出所有商品对应的未入驻门店（比如Product 1没入驻门店4，要把所有商品的这类未入驻组合都查出来）。

现有代码的问题 #

尝试的LEFT JOIN代码逻辑错误 #

select * from productstore ps
left join stores s on ps.storeid=s.storeid
where prdid='Product 1'
and s.storeid IS NULL

这段代码从productstore表出发关联stores，但productstore里根本没有Product 1和门店4的记录，自然查不到任何结果。要找未入驻的组合，得先生成所有商品+所有门店的全量可能，再排除已经入驻的。

当前NOT EXISTS代码只能查单个商品 #

SELECT s.storeid FROM stores s 
WHERE not exists
(select ps.storeid from productstore ps
where s.storeid=ps.storeid
and s.PrdId='Product 1') 

这段只能查指定商品（Product 1）的未入驻门店，没法覆盖所有商品的情况。

最优解决方案 #

方法1：CROSS JOIN + LEFT JOIN 排查未入驻组合 #

先生成所有商品和所有门店的全量组合，再关联已入驻记录，筛选出没有匹配的行：

SELECT 
    prd.prdid, 
    s.storeid
FROM 
    (SELECT DISTINCT prdid FROM productstore) prd
CROSS JOIN 
    stores s
LEFT JOIN 
    productstore ps ON prd.prdid = ps.prdid AND s.storeid = ps.storeid
WHERE 
    ps.prdid IS NULL

• 先通过SELECT DISTINCT prdid FROM productstore拿到所有有入驻记录的商品
• CROSS JOIN stores生成每个商品对应所有门店的全量组合
• LEFT JOIN关联已入驻数据，ps.prdid IS NULL的行就是该商品未入驻的门店

方法2：NOT EXISTS 适配所有商品 #

用NOT EXISTS检查每个商品+门店的组合是否存在于入驻表中，不存在的就是未入驻：

SELECT 
    prd.prdid, 
    s.storeid
FROM 
    (SELECT DISTINCT prdid FROM productstore) prd
CROSS JOIN 
    stores s
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM productstore ps 
        WHERE ps.prdid = prd.prdid 
          AND ps.storeid = s.storeid
    )

这个方法和方法1逻辑一致，只是用存在性检查替代关联筛选，性能取决于数据库的索引优化，大部分场景下表现相近。

补充：包含从未入驻任何门店的商品 #

如果业务里有从未入驻过任何门店的商品（这类商品不在productstore表中），可以从商品主表（比如叫products）获取全量商品，替换上面的子查询：

-- 示例：包含所有商品（含从未入驻的）
SELECT 
    p.prdid, 
    s.storeid
FROM 
    products p
CROSS JOIN 
    stores s
LEFT JOIN 
    productstore ps ON p.prdid = ps.prdid AND s.storeid = ps.storeid
WHERE 
    ps.prdid IS NULL

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