如何编写SQL查询找出各门店未开设的州？

我有存储门店信息的表A（Stores）和存储所有州信息的表B，此前已通过关联两表获取门店已开设的州，现需编写SQL查询找出各门店未开设的州。

表A（Stores）结构及数据 #

表B结构及数据 #

期望输出表结构及数据 #

我尝试过的SQL语句 #

SELECT *
FROM tableB
LEFT OUTER JOIN tableA
  ON tableA.<stateCode> = tableB.<stateCode>
WHERE tableA.<stateCode> IS NULL

问题分析 #

当前SQL只能找出没有任何门店覆盖的州，但无法关联到具体每个门店未开设的州，不符合需求。要实现目标，需要先生成所有门店与所有州的全组合，再排除掉已存在的门店-州配对。

正确的SQL查询 #

SELECT s.Stores, b.StateCode, b.StateName
FROM (SELECT DISTINCT Stores FROM Stores) s
CROSS JOIN tableB b
LEFT JOIN Stores a 
  ON s.Stores = a.Stores AND b.StateCode = a.StateCode
WHERE a.StateCode IS NULL
ORDER BY s.Stores, b.StateCode;

思路说明 #

1. 先通过SELECT DISTINCT Stores FROM Stores获取所有唯一门店，避免重复处理同一门店的记录。
2. 使用CROSS JOIN生成每个门店与所有州的全组合，得到所有可能的门店-州配对。
3. 通过LEFT JOIN关联原Stores表，筛选出原表中不存在的配对（即a.StateCode IS NULL的记录），这些就是该门店未开设的州。
4. 最后按门店和州编码排序，让结果更规整。

内容的提问来源于stack exchange，提问作者bhanu pratap singh sikarwar