[Database] (쉬운 코드) Subquery in SQL (쿼리 안의 쿼리)

아래의 내용은 MySQL 기준으로 작성한 것입니다. 다른 RDBMS의 SQL 문법은 조금씩 다를 수 있습니다.

테이블 구조

우선 시작하기에 앞서 내용 이해에 필요한 테이블 구조입니다.

SELECT with subquery

---

• subquery(nested query or inner query) : SELECT, INSERT, UPDATE, DELETE에 포함된 query
• outer query(main query) : subquery를 포함하는 query
• subquery는 () 안에 기술된다.

ex. ID가 14인 임직원보다 생일이 빠른 임직원의 ID, 이름, 생일을 알고 싶다.

-- outer query --
SELECT id, name, birth_date FROM employee
    WHERE birth_date < (
        -- subquery --
        SELECT birth_date FROM employee WHERE id = 14
    );

ex. ID가 1인 임직원과 같은 부서 같은 성별인 임직원들의 ID와 이름과 직군을 알고 싶다.

SELECT id, name, position
    FROM employee
    WHERE (dept_id, sex) = (
        SELECT dept_id, sex
        FROM employee
        WHERE id = 1
    );

ex. ID가 5인 임직원과 같은 프로젝트에 참여한 임직원들의 ID를 알고 싶다.

SELECT proj_id FROM works_on WHERE empl_id = 5;
SELECT DISTINCT empl_id FROM works_on
    WHERE empl_id != 5 AND proj_id IN (2001, 2002);

-- subquery 로 작성 --
SELECT DISTINCT empl_id
    FROM works_on
    WHERE empl_id != 5 AND proj_id IN (
        SELECT pro_id
        FROM works_on
        WHERE empl_id = 5;
    )

IN & NOT IN

---

• v IN (v1, v2, v3, ...) : v가 (v1, v2, v3, …) 중에 하나와 값이 같다면 TRUE를 return 한다.
  • (v1, v2, v3, …) 는 명시적인 값들의 집합일 수도 있고 subquery의 결과 (set or multiset)일 수도 있다.
• v NOT IN (v1, v2, v3, ...) : v 가 (v1, v2, v3, …) 의 모든 값과 값이 다르다면 TRUE를 return 한다.

unqualified attribute가 참조하는 table은 해당 attribute가 사용된 query를 포함하여 그 query의 바깥쪽으로 존재하는 모든 queries 중에 해당 attribute 이름을 가지는 가장 가까이에 있는 table을 참조한다.

만약 위의 예시에서 임직원의 이름을 추가로 알고싶다면 어떻게 작성할까?

SELECT id, name
    FROM employee
    WHERE id IN (
        SELECT DISTINCT empl_id
        FROM works_on
        WHERE empl_id != 5 AND proj_id IN (
            SELECT proj_id
            FROM works_on
            WHERE empl_id = 5
        )
    );

--- FROM 에 subquery 작성해 가상의 테이블 생성-
SELECT id, name
    FROM employee,
        (
            SELECT DISTINCT empl_id
            FROM works_on
            WHERE empl_id != 5 AND proj_id IN (
                SELECT proj_id
                FROM works_on
                WHERE empl_id = 5
            )
        ) AS DSTNCT_E
    WHERE id = DSTINCT_E.empl_id;

EXISTS & NOT EXISTS

---

또 다른 키워드로 EXISTS가 있다.

EXISTS는 subquery의 결과가 최소 하나의 row라도 있다면 TRUE를 반환한다. 반면에 NOT EXISTS는 subquery의 결과가 단 하나의 row도 없다면 TRUE를 반환한다.

• correlated query : subquery가 바깥쪽 query의 attribute를 참조할 때, correlated subquery라 부름

ex. ID가 7 혹은 12인 임직원이 참여한 프로젝트의 ID와 이름을 알고 싶다

SELECT P.id, P.name
    FROM project P
    WHERE EXISTS (
        SELECT *
        FROM works_on W
        WHERE W.proj_id = P.id AND W.empl_id IN (7, 12)
    )

EXISTS <-> IN

EXISTS 대신 IN으로 코드를 작성할 수 도 있다.

SELECT P.id, P.name
    FROM project P
    WHERE id IN (
        SELECT W.proj_id
        FROM works_on W
        WHERE W.empl_id IN (7, 12)
    )

ex. 2000년대생이 없는 부서의 ID와 이름을 알고싶다.

--- NOT EXISTS ---
SELECT D.id, D.name
    FROM departments AS D
    WHERE NOT EXISTS (
        SELECT *
        FROM employee E
        WHERE E.dept_id = D.id AND E.birth_date >= '2000-01-01'
    )

--- NOT IN 으로 구현 ---
SELECT D.id, D.name
    FROM departments AS D
    WHERE D.id NOT IN (
        SELECT E.dept_id
        FROM employee E
        WHERE E.birth_date >= '2001-01-01'
    )

성능 비교 : IN vs EXISTS

RDBMS의 종류와 버전에 따라 다르며 최근 버전은 많은 개선이 이루어져서 IN과 EXISTS의 성능 차이거 거의 없다.

ANY

---

• v comparison_operatro ANY (subquery) : subquery가 반환한 결과들 중에 단 하나라도 v와의 비교 연산이 TRUE라면 TRUE를 반환한다.
• SOME도 ANY와 같은 역할을 한다.

ex. 리더보다 높은 연봉을 받는 부서원을 가진 리더의 ID와 이름, 연봉을 알고 싶다.

SELECT E.id, E.name, E.salary
    FROM department D, employee E
    WHERE D.leader_id = E.id AND E.salay < ANY (
        SELECT salary
        FROM employee
        WHERE id <> D.leader_id AND dept_id == E.dept_id
    );

ex. 리더보다 높은 연봉을 받는 부서원을 가진 리더의 ID와 이름, 연봉과 해당 부서 최고 연봉을 알고 싶다.

SELECT E.id, E.name, E.salary, (
    SELECT max(salary)
    FROM employee
    WHERE dept_id = E.dept_id
) AS dept_max_salary
FROM department D, employee E
WHERE D.leader_id = E.id AND E.salary < ANY (
    SELECT salary
    FROM employee
    WHERE id <> D.leader_id AND dept_id = E.dept_id
);

ALL

---

• v comparison_operator ALL (subquery) : subquery가 반환한 결과들과 v와의 비교 연산이 모두 TRUE라면 TRUE를 반환한다.

ex. ID가 13인 임직원과 한 번도 같은 프로젝트에 참여하지 못한 임직원들의 ID, 이름, 직군을 알고싶다.

SELECT E.id, E.name, E.position
    FROM employee E, works_on W
    WHERE E.id = W.empl_id AND W.proj_id <> ALL (
        SELECT proj_id
        FROM works_on
        WHERE empl_id = 13
    )