[데이터베이스 기초] 복잡한 질의 정리 (WITH, CASE, Recursive Queries)

WITH and CASE

 

WITH 절 : 특정 쿼리내에서 사용할 수있는 임시 테이블을 정의한다. 특정 쿼리가 끝나면 함께 사라진다. 

* 비슷한 조건이 여러번 나올 경우에 사용하면 편하게 사용할 수 있다.

 

ex) 직원 수가 3명 이상인 부서에 대해서 부서 번호, 급여가 40000보다 많은 지원 수를 검색하라

WITH BIGDEPTS (Dno) AS
	(SELECT Dno FROM EMPLOYEE GROUP BY Dno HAVING COUNT(*) > 3)
SELECT Dno, COUNT(*)
FROM EMPLOYEE
WHERE Salary>40000 AND Dno IN BIGDEPTS
GROUP BY Dno;

 

CASE 사용법

UPDATE EMPLOYEE
SET Salary = 
CASE WHEN Dno = 5 THEN Salary + 2000
     WHEN Dno = 4 THEN Salary + 1500
     WHEN Dno = 1 THEN Salary + 3000
     ELSE Salary + 0;

 

 

Recursive Queries

예시로는 직원과 상사의 관계가 있다. 

(->) : 상사 관계

직원1 -> 직원2

직원2 -> 직원3

여기서 직원1은 직원2의 상사가 직원3이라는 것을 알아야 직원3도 상사라는 것을 알 수 있음

 

ex) 자신의 모든 상사를 다 검색해라

WITH RECURSIVE SUP_EMP (SupSsn, EmpSsn) AS
(SELECT Superssn, Ssn
 FROM EMPLOYEE
 UNION
 SELECT S.SupSsn, E.Ssn
 FROM EMPLOYEE AS E, SUP_EMP AS S
 WHERE E.Super_ssn = S.EmpSsn)
SELECT *
FROM SUP_EMP;

-> SUP_EMP 정의 안에 또 다시 SUP_EMP를 사용하고 있다.

 

수행 과정

최초 SUP_EMP에는 EMPLOYEE 와 같은 형태를 갖는다. 이 두 테이블을 조인한 결과와 SUP_EMP를 UNION하여 새로운 SUP_EMP를 만들면 자신의 상사의 상사까지 테이블에 포함되고 SUP_EMP에 더이상 변화가 없을 때 까지 다시 이 작업을 반복하여 모든 상사들을 표현할 수 있게 된다.

 

질의 수행 순서

FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY

 

가능하면 nesting 과 odering이 없는 질의어가 좋다. 데이터 독립적인 측면에서 같은 결과를 내는 쿼리문은 항상 DBMS가 최적화하여 같은 방식으로 처리되면 좋겠지만 실제로는 그렇게 되지 않는다. 따라서 유저가 직접 쿼리문을 최적화하여야 한다.