SQL 서브쿼리의 종류와 특징

서브쿼리는 SQL에서 하나의 쿼리 내에 포함된 또 다른 쿼리이다.

메인 쿼리의 조건이나 데이터를 보완하는 용도로 사용되며, 적절히 활용하면 복잡한 데이터 처리를 효율적으로 수행할 수 있다.

 

1. WHERE 절 서브쿼리 (중첩 서브쿼리)

a. 단일행 서브쿼리

서브쿼리에서 한 개의 행만 반환한다. 주로 비교 연산자(>, <, =, >=, <=, <>)와 함께 사용된다.

SELECT employee_id, salary 
FROM employees 
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees
);

이 예시는 전체 직원의 평균 급여보다 높은 급여를 받는 직원들을 조회한다.

b. 다중행 서브쿼리

서브쿼리에서 여러 행의 값을 반환할 수 있다. IN, SOME(ANY), ALL 등과 함께 사용된다.

SELECT employee_id, department_id 
FROM employees 
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location = '서울'
);

위 쿼리는 서울에 위치한 부서에 소속된 직원들을 조회한다.

다중행 서브쿼리에서 사용되는 연산자:

• IN, NOT IN: 목록 내 값과 일치 여부 확인
• ALL: 모든 값과 비교 (> ALL은 최대값보다 큼)
• SOME(ANY): 하나라도 조건 만족 (> ANY는 최소값보다 큼)

SELECT orderid, saleprice
FROM Orders
WHERE salesprcie > [ ALL or Some ]
( 
    SELECT saleprice
    FROM Orders
    WHERE custid = 3
);

=> ALL인 경우, custid가 3인 salesprice 중 가장 큰 값보다 커야한다.

=> SOME인 경우, custid가 3인 salesprice 중 가장 작은 값보다 커야한다.

• EXISTS, NOT EXISTS: 서브쿼리 결과의 존재 여부 확인

SELECT SUM(saleprice) 'total'
FROM Orders od
WHERE EXISTS 
( 
    SELECT *
    FROM Customer cs
    WHERE address LIKE '%대한민국%' AND cs.custid = od.custid
);

c. 다중컬럼 서브쿼리

서브쿼리에서 한 행에서 여러 컬럼이 동시에 반환한다. 복합 조건 비교에 사용된다.

SELECT * 
FROM tableA 
WHERE (col1, col2) IN (SELECT col1, col2 FROM tableB);

이 예시는 tableA의 col1과 col2 조합이 tableB의 col1과 col2 조합과 일치하는 행을 찾는다.

 

2. FROM 절 서브쿼리 (인라인 뷰) + WITH 문

FROM 절 서브쿼리

파생 테이블 또는 인라인 뷰라고 한다.

메인 쿼리에서 임시 테이블처럼 사용한다.

SELECT dept, avg_salary 
FROM (
    SELECT department_id AS dept, AVG(salary) AS avg_salary 
    FROM employees 
    GROUP BY department_id 
) AS dept_avg 
WHERE avg_salary > 5000;

WITH 문

재사용 가능한 서브쿼리를 정의하여 쿼리 가독성을 높인다.

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary 
    FROM employees 
    GROUP BY department_id
) 
SELECT e.employee_id, e.salary, d.avg_salary 
FROM employees e 
JOIN dept_avg d ON e.department_id = d.department_id 
WHERE e.salary > d.avg_salary;

주의사항: 상관 부속질의를 FROM 절에서 사용할 수 없다.

SQL 실행 순서:

1. FROM 절 (테이블 결합 및 서브쿼리 실행)
2. WHERE 절
3. GROUP BY 절
4. HAVING 절
5. SELECT 절
6. ORDER BY 절

FROM 절의 서브쿼리는 먼저 실행되어 하나의 임시 테이블(derived table)이 생성된다.

그런데 상관 부속질의는 외부 쿼리의 각 행을 참조해야만 실행되므로,

FROM 절이 실행될 때 필요한 데이터가 아직 준비되지 않아 의미적으로 실행이 불가능하다.

 

3. SELECT 절 서브쿼리 (스칼라 서브쿼리)

결과 집합의 각 행에 대해 개별적으로 실행되며, 항상 단일 값(스칼라 값)을 반환해야 한다.

주로 집계값 등을 계산하여 출력한다.

SELECT employee_id, salary, 
    (SELECT AVG(salary) 
     FROM employees 
     WHERE department_id = e.department_id) AS dept_avg_salary 
FROM employees e;

 

상관 서브쿼리 vs 비상관 서브쿼리

서브쿼리가 외부 쿼리의 칼럼을 참조하는지 여부에 따라 구분된다.

비상관 서브쿼리 (Non-correlated Subquery)

• 서브쿼리가 외부 쿼리의 칼럼에 전혀 의존하지 않고 독립적으로 실행된다.
• 서브쿼리 결과가 저장되어 재활용될 수 있다.

SELECT employee_id
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

상관 서브쿼리 (Correlated Subquery)

• 서브쿼리가 외부 쿼리의 컬럼을 참조하여, 외부 쿼리의 각 행마다 다시 실행된다.

SELECT employee_id, salary, 
    (SELECT AVG(salary) FROM employees AS e2 
     WHERE e2.department_id = e1.department_id) AS dept_avg_salary 
FROM employees AS e1;

여기서 서브쿼리는 외부 쿼리의 e1.department_id를 참조하므로, 각 직원 행마다 해당 부서의 평균 급여를 계산하기 위해 서브쿼리가 반복적으로 실행된다.

일부 자료에서는 상관 서브쿼리 사용을 지양하도록 권장하기도 한다.

하지만 DBMS의 최적화 기능이 발전하면서 많은 경우 문제없이 효율적으로 동작하기도 한다.

 

실행 계획으로 확인하는 서브쿼리 특성

프로그래머스 문제(https://school.programmers.co.kr/learn/courses/30/lessons/301651)를 통해 실행 계획을 분석해보면,

비상관 서브쿼리와 상관 서브쿼리의 특성 차이를 확인할 수 있다.

비상관 서브쿼리 실행 계획

EXPLAIN FORMAT=JSON 
SELECT * 
FROM ECOLI_DATA 
WHERE SIZE_OF_COLONY > (
    SELECT AVG(SIZE_OF_COLONY) 
    FROM ECOLI_DATA
);

실행 계획에서 서브쿼리 부분을 보면 "dependent": false, "cacheable": true로 나타난다.

이는 서브쿼리가 외부 쿼리와 독립적이며, 결과가 캐시되어 재사용될 수 있음을 의미한다.

상관 서브쿼리 실행 계획

EXPLAIN FORMAT=JSON 
SELECT e1.ID, e1.SIZE_OF_COLONY,
    (SELECT COUNT(*) 
     FROM ECOLI_DATA e2 
     WHERE e2.PARENT_ID = e1.ID 
     AND e2.SIZE_OF_COLONY > e1.SIZE_OF_COLONY) AS larger_children_count
FROM ECOLI_DATA e1;

실행 계획에서는 "dependent": true, "cacheable": false로 표시된다.

이는 서브쿼리가 외부 쿼리에 의존적이며, 결과가 캐시되어 재사용될 수 없음을 의미한다.