基于三表Left Outer Join的查询：找出拥有Bike但无Car的John

从以下3张表中找出名为John、拥有Bike但没有Car的记录。

我知道可以用左连接加where B.Key IS null的语法查询不存在的记录，比如：

Select <> from TableA A left join TableB B on A.Key = B.Key where B.Key IS null

但不清楚该怎么把这个逻辑融入多表查询。我目前写的查询语句是：

select t1.name from 
(table1 t1 join table3 on table3.table1id = t1.id join table2 t2 on table3.table2id = t2.id) 
left join 
(table1 t11 join table3 on table3.table1id = t11.id join table2 t22 on table3.table2id = t22.id) 
on t1.name = t11.name where t2.name = 'Bike' and t22.name = 'Car';

表结构及数据 #

Table1 #

Table2 #

Table3 #

你当前的查询逻辑有问题：左连接后加t22.name = 'Car'会把左连接强制转为内连接，无法筛选出没有Car的记录。以下两种方法可以实现需求：

方法一：左连接筛选不存在的记录 #

先筛选出拥有Bike的用户，再左连接到拥有Car的用户记录，最后保留左连接后Car记录为空的用户：

SELECT t1.name
FROM table1 t1
JOIN table3 t3_bike ON t1.id = t3_bike.table1id
JOIN table2 t2_bike ON t3_bike.table2id = t2_bike.id AND t2_bike.name = 'Bike'
LEFT JOIN table3 t3_car ON t1.id = t3_car.table1id
LEFT JOIN table2 t2_car ON t3_car.table2id = t2_car.id AND t2_car.name = 'Car'
WHERE t1.name = 'John' AND t2_car.id IS NULL;

方法二：使用NOT EXISTS子查询 #

直接查询名为John且拥有Bike，同时不存在对应Car记录的用户：

SELECT t1.name
FROM table1 t1
JOIN table3 t3 ON t1.id = t3.table1id
JOIN table2 t2 ON t3.table2id = t2.id
WHERE t1.name = 'John' 
  AND t2.name = 'Bike'
  AND NOT EXISTS (
    SELECT 1
    FROM table3 t3_car
    JOIN table2 t2_car ON t3_car.table2id = t2_car.id
    WHERE t3_car.table1id = t1.id 
      AND t2_car.name = 'Car'
  );

两种方法都能得到正确结果：John，因为他只有Bike记录，没有Car记录。

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