如何用JOIN替换NOT EXISTS ?

Question

我有以下查询:

select distinct a.id, a.name
from Employee a
join Dependencies b on a.id = b.eid
where not exists 
    ( 
select * 
    from Dependencies d 
    where b.id = d.id 
    and d.name  = 'Apple'
    )
and exists 
    (
    select * 
    from Dependencies c 
    where b.id = c.id 
    and c.name  = 'Orange'
    );

我有两个表，相对简单。第一个Employee有一个id列和一个name列第二个表Dependencies有3列，一个id，一个eid(要链接的员工id)和名字(apple, orange等)。

数据看起来像这样员工表如下所示

id  | name
-----------
1   | Pat
2   | Tom
3   | Rob
4   | Sam

的依赖性

id  | eid | Name
--------------------
1   | 1   |  Orange
2   | 1   |  Apple
3   | 2   |  Strawberry
4   | 2   |  Apple
5   | 3   |  Orange
6   | 3   |  Banana

正如你所看到的，Pat同时拥有橙子和苹果，他需要被排除在外，它必须通过连接，我似乎不能让它工作。最终，数据应该只返回Rob

Answer 1

内部连接你想要的名字，左连接你不需要的名字，然后使用哪里来确保左连接不匹配，像这样(SQL Fiddle):

select distinct a.id, a.name
from Employee a
  inner join Dependencies b on a.id = b.eid
    and b.name = 'Orange'
  left join Dependencies c on ( a.id = c.eid
    and c.name = 'Apple')
where c.id is null;

Answer 2

需要两次连接到Dependencies，因为有2个测试。暂时忽略性能，您可以尝试通过命名别名来提高连接的可理解性，例如:

SELECT DISTINCT e.ID, e.Name
   FROM Employee e
   LEFT OUTER JOIN Dependencies withApple
      ON withApple.eid = e.id
      AND withApple.Name = 'Apple'
   LEFT OUTER JOIN Dependencies withOrange
      ON withOrange.eid = e.id
      AND withOrange.Name = 'Orange'
   WHERE
      withApple.id IS NULL -- Don't want this
      AND
      withOrange.id IS NOT NULL -- Do want this.

SqlFiddle

Answer 3

另一个版本，类似于Jaaz Cole的版本是:

select distinct a.id, a.name
   from Employee a
inner join Dependencies b on a.id = b.eid
   and b.name = 'Orange'
left join Dependencies c on a.id = c.eid
where (c.id is null or c.name != 'Apple');

实质上这来自于逻辑:否定(A &