在查詢中使用 exists 而不是正常 where

我看到了這個查詢，它找到了所有擁有超過一個學位的員工：

select id, name
from employee E
where exists (
select *
from academics A1, academics A2
where  A1.emp_id = E.id and
       A2.emp_id = E.id and
       A1.discipline != A2.discipline
)

但為什麼是exists必要的？為什麼不這樣做：

select id, name
from employee E, academics A1, academics A2
where A1.emp_id = E.id and
     A2.emp_id = E.id and
     A1.discipline != A2.discipline

上面兩個是等價的嗎？

我建議你另一種方法。

COUNT DISTINCT員工紀律。

然後用於EXISTS檢查一名員工是否擁有超過一個學位。

select      e.id, e.name
from        Employees e
where exists (select    1
             from      Academics a
             where     a.emp_id = e.id
             having count(distinct a.discipline) > 1);

或將其與表員工加入並顯示這些是 NumDegrees > 1

select      e.id, e.name, nd.NumDegrees
from        Employees e
inner join (select    emp_id, count(distinct a.discipline) NumDegrees
           from      Academics a
           group by  a.emp_id) nd
on          e.id = nd.emp_id
where       nd.NumDegrees > 1;

為了完整起見，我會再添加一個變體，這對某些人來說可能更清楚，並且任何體面的優化器都會認為它等同於 McNets 提出的第二個變體：

SELECT e.id, e.name
 FROM Employees e
WHERE e.id IN (SELECT a.emp_id
                         FROM Academics a
                         GROUP BY a.emp_id
                         HAVING COUNT(DISTINCT a.discipline) > 1);

引用自：https://dba.stackexchange.com/questions/166699