使用连接对多对多关系进行单个SQL查询

Question

我有一个简单的数据库，只有几个表(和一些示例列)

Employee (ID, Title, Content)

Project (ID, Title)

ProjectEmployee (ID、EMPLOYEE_ID PROJECT_ID)

需要使用join查询查找没有任何项目的员工

我可以使用subquery

select * from employee 
where id not in (select  id from project_employee);

Answer 1

LEFT JOIN和NOT NULL给出了预期的结果。

select e.* 
from employee e
LEFT JOIN project_employee pe on pe.id = e.id
where pe.id is not null;

Answer 2

这将是最好的实现使用not exists，连接不是正确的解决方案，如果你不需要从连接表返回任何列。

select *
from employee e
where not exists (select * from project_employee pe where pe.id = e.id)