網頁

2018/12/1

Oracle EXISTS / NOT EXISTS

Oracle EXISTS條件的使用方法如下

EXISTS(subquery)

目前有資料表DEPARTMENTEMPLOYEE,為一對多的關聯,資料如下:

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               行銷部              

而下面的查詢分別利用INJOIN來查詢出同樣的結果:

-- 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;  

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

根據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搭配適當的索引來取代EXISTSIN來改善效能。


參考:

沒有留言:

張貼留言