获取未被各客户购买的产品对应的客户名与产品名

原始表结构及数据 #

以下是测试用的表定义和数据插入语句：

declare @customer table(CId int identity,Cname varchar(10))
insert into @customer values('A'),('B')
--select * from @customer

declare @Product table(PId int identity,Pname varchar(10))
insert into @Product values('P1'),('P2'),('P3'),('P4')
--select * from @Product

declare @CustomerProduct table(CPId int identity,Cid int, Pid int)
insert into @CustomerProduct values(1,1),(1,2),(2,1),(2,4)

需求及期望输出 #

需要获取每个客户未购买的产品对应的客户名称（Cname）和产品名称（Pname），正确的期望输出如下：

declare @outputtable table (Cname varchar(10), Pname varchar(10))
insert into @outputtable values('A','P3'),('A','P4'),('B','P2'),('B','P3')
select * from @outputtable

注：原需求中的期望输出存在重复记录，此处已修正为正确的无重复结果

用户尝试的错误查询 #

用户使用左连接但仅得到已购买的产品记录，查询语句如下：

Select P.Pname,C.Cname from @Product p
left join @CustomerProduct cp on cp.Pid=p.pId
Left Join @customer c  on cp.Cid=c.CId
where c.CId=cp.Cid

原查询的问题在于：where c.CId=cp.Cid条件过滤掉了左连接中不匹配的记录，等价于将左连接转为内连接，因此只能获取到已有购买记录的产品。

正确的SQL查询语句 #

方法1：交叉连接+EXCEPT排除已购买组合 #

先生成所有客户与产品的可能组合，再排除已存在于购买表中的组合：

SELECT c.Cname, p.Pname
FROM @customer c
CROSS JOIN @Product p
EXCEPT
SELECT c.Cname, p.Pname
FROM @customer c
JOIN @CustomerProduct cp ON c.CId = cp.Cid
JOIN @Product p ON cp.Pid = p.PId

方法2：交叉连接+左连接筛选未匹配记录 #

通过交叉连接生成全量组合，左连接购买表后筛选无匹配记录的行：

SELECT c.Cname, p.Pname
FROM @customer c
CROSS JOIN @Product p
LEFT JOIN @CustomerProduct cp 
  ON c.CId = cp.Cid AND p.PId = cp.Pid
WHERE cp.CPId IS NULL

两种方法都能得到符合需求的结果，即每个客户未购买的产品列表。

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