Oracle EXISTS / NOT EXISTS

Oracle EXISTS條件的使用方法如下

EXISTS(subquery)

目前有資料表DEPARTMENT及EMPLOYEE，為一對多的關聯，資料如下：

DEPARTMENT

  DEPARTMENT_ID   DEPARTMENT_NAME  
 --------------- ----------------- 
  1               總經理室             
  2               財務部              
  3               業務部              
  4               產品部              
  5               行銷部              

EMPLOYEE

  EMPLOYEE_ID   EMPLOYEE_NAME   DEPARTMENT_ID  
 ------------- --------------- --------------- 
  0001          Frank Wang      1              
  0002          Amy Liu         1              
  0003          John Chen       2              
  0004          Peter Hung      2              
  0005          Matt Lin        3              
  0006          Maggie Huang    3              
  0007          Bill Kin        4              
  0008          May Wang        4              
  0009          Ryan Hen        4              
  0010          Jane Li         4              

使用EXISTS來查詢出在EMPLOYEE中有存在的DEPARTMENT資料（EMPLOYEE資料表中有DEPARTMENT_ID）。

SELECT d.* FROM DEPARTMENT d 
WHERE EXISTS( SELECT NULL FROM EMPLOYEE e 
              WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID );

在EMPLOYEE中並沒有DEPARTMENT_ID = 5(行銷部)的資料，因此查詢結果如下：

  DEPARTMENT_ID   DEPARTMENT_NAME  
 --------------- ----------------- 
  1               總經理室             
  2               財務部              
  3               業務部              
  4               產品部              

相反地，如果要查出在EMPLOYEE不存在的DEPARTMENT資料（EMPLOYEE資料表中沒有DEPARTMENT_ID），則使用NOT EXISTS

SELECT d.* FROM DEPARTMENT d 
WHERE NOT EXISTS( SELECT NULL FROM EMPLOYEE e 
                  WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID );

查詢結果如下：

  DEPARTMENT_ID   DEPARTMENT_NAME  
 --------------- ----------------- 
  5               行銷部              

而下面的查詢分別利用IN及JOIN來查詢出同樣的結果：

-- IN
SELECT d.* FROM DEPARTMENT d 
WHERE d.DEPARTMENT_ID IN ( SELECT DISTINCT(e.DEPARTMENT_ID) FROM EMPLOYEE e );

SELECT d.* FROM DEPARTMENT d 
WHERE d.DEPARTMENT_ID NOT IN ( SELECT DISTINCT(e.DEPARTMENT_ID) FROM EMPLOYEE e );
--注意當資料有null時使用NOT IN會查無結果


-- JOIN
SELECT d.* FROM DEPARTMENT d
INNER JOIN ( SELECT DISTINCT(DEPARTMENT_ID) FROM EMPLOYEE ) e 
        ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;
  
SELECT d.* FROM DEPARTMENT d
LEFT JOIN ( SELECT DISTINCT(DEPARTMENT_ID) FROM EMPLOYEE ) e 
       ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE e.DEPARTMENT_ID IS NULL;  

但不論使用EXISTS或IN時都要考量對效能的影響。

根據Oracle Ask TOM的回答，當外查詢(outer query)結果筆數很少，而子查詢(subquery)結果筆數非常多的時候，使用EXISTS會比較快，因為使用EXISTS會先掃全部的外查詢的資料表，然後以外查詢的結果去查子查詢的資料表，類似如下

FOR x IN ( SELECT * FROM t1 )
LOOP
IF ( EXISTS ( SELECT null FROM t2 WHERE y = x.x )
  THEN 
    OUTPUT THE RECORD
  END IF
END LOOP

而上述外查詢及子查詢的資料表都必須有適當的索引(index)才能滿足。

使用IN時，會先子查詢並完成DISTINCT後，再與外查詢的結果INNER JOIN起來，

SELECT * FROM t1 ,( SELECT DISTINCT y from T2 ) t2
WHERE t1.x = t2.y;

因此若子查詢的資料表筆數不多，則或許可用IN。

但可以的話，還是盡量使用JOIN搭配適當的索引來取代EXISTS或IN來改善效能。

參考：

• Oracle IN vs Exists difference?
• SQL Exists vs. IN clause
• Spring Data JPA Repository INSERT INTO SELECT WHERE NOT EXISTS