sql - 涉及三张表的左外连接

Question

我需要从下面的 3 个表格中找到“John”，他有自行车但没有汽车。我正在尝试使用这种语法

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

所以在实践中我从两个表创建了一个左连接但是我有点困惑 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';

表1

<表类="s-表"><头>身份证<日>姓名 <正文>1约翰2尼克

表2

<表类="s-表"><头>身份证<日>姓名 <正文>1自行车2汽车

表3

<表类="s-表"><头>表1ID表2ID<正文>112122

Answer 1

如果你想让你的查询不惜一切代价运行，你可以做一些像这样的可怕的事情:

select name from table1
left join (select table1Id, table2.id as CarId from table3 join table2 on table2.id = table3.table2Id where table2.name = 'Car') c on id=c.table1Id
left join (select table1Id, table2.id as BikeId from table3 join table2 on table2.id = table3.table2Id where table2.name = 'Bike') b on id=b.table1Id
where CarId is null and BikeId is not null

https://dbfiddle.uk/pbftUJQL

但最后它大致相当于直截了当

select name from table1
where not exists (select * from table3 join table2 on table2.id = table3.table2Id where table1.id=table3.table1Id and table2.name = 'Car')
and exists (select * from table3 join table2 on table2.id = table3.table2Id where table1.id=table3.table1Id and table2.name = 'Bike')

您必须分析查询计划以选择提供更好性能的变体。可能是这个:

select table1.name from table1 join table3 on table1.id=table3.table1Id join table2 on table2.id = table3.table2Id
where table2.name = 'Bike'
and not exists (select * from table3 t3 join table2 t2 on t2.id = t3.table2Id where table1.id=t3.table1Id and t2.name = 'Car')