안동민 개발노트 아이콘

안동민 개발노트

5장 : SQL 심화 — 조인과 서브쿼리

서브쿼리

하나의 SQL 안에 또 다른 SQL을 넣을 수 있습니다. 이것이 서브쿼리(Subquery)입니다. 평균 가격보다 비싼 상품처럼 다른 쿼리의 결과를 조건으로 사용하고 싶을 때 유용합니다.

서브쿼리는 SQL의 강력한 기능 중 하나로, 복잡한 비즈니스 로직을 하나의 SQL 문으로 표현할 수 있게 합니다. 서브쿼리 없이는 여러 번의 쿼리를 실행하고 결과를 프로그래밍 언어에서 조합해야 하지만, 서브쿼리를 사용하면 데이터베이스 엔진이 한 번에 처리합니다.

서브쿼리가 필요한 이유
-- 서브쿼리 없이: 2번의 쿼리 필요
-- 1단계: 평균 가격 구하기
SELECT AVG(price) FROM products;  -- 결과: 50000

-- 2단계: 평균보다 비싼 상품 조회
SELECT * FROM products WHERE price > 50000;

-- 서브쿼리 사용: 1번의 쿼리로 해결
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

서브쿼리의 가장 큰 장점은 동적 조건을 만들 수 있다는 점입니다. 평균값이 변하더라도 쿼리를 수정할 필요가 없습니다.

서브쿼리의 기본 분류

서브쿼리는 크게 두 가지 기준으로 분류할 수 있습니다.

반환 형태에 따른 분류

외부 쿼리와의 관계에 따른 분류

비상관 vs 상관
* 비상관 서브쿼리 (Non-correlated)
  외부 쿼리와 독립적. 서브쿼리가 먼저 한 번 실행되고 결과가 외부에 전달됨
  예: WHERE price > (SELECT AVG(price) FROM products)

* 상관 서브쿼리 (Correlated)
  외부 쿼리의 값을 참조. 논리적으로는 외부 행마다 평가되며, 실제 실행은 DBMS 옵티마이저가 조인이나 세미 조인으로 바꿀 수도 있음
  예: WHERE price = (SELECT MAX(price) FROM products p2 WHERE p2.category = p.category)

서브쿼리의 위치별 분류

위치이름반환예시
SELECT 절스칼라 서브쿼리단일 값(SELECT COUNT(*) ...)
FROM 절인라인 뷰테이블FROM (SELECT ...) t
WHERE 절중첩 서브쿼리값 또는 집합WHERE id IN (SELECT ...)
HAVING 절중첩 서브쿼리값HAVING COUNT(*) > (SELECT ...)

스칼라 서브쿼리

스칼라 서브쿼리는 단일 값이 필요한 위치에서 사용할 수 있습니다. 여기서는 SELECT 절에서 값을 하나 덧붙이는 예를 중심으로 보겠습니다. 스칼라 서브쿼리는 반드시 단일 값(1행 1열)을 반환해야 하며, 2개 이상의 행을 반환하면 오류가 발생합니다.

스칼라 서브쿼리: 각 주문에 고객 이름을 포함
SELECT o.order_id,
       o.order_date,
       o.total_amount,
       (SELECT u.username
        FROM users u
        WHERE u.user_id = o.user_id) AS customer_name
FROM orders o;

스칼라 서브쿼리는 외부 쿼리의 각 행마다 실행되므로 상관 서브쿼리입니다. 행이 많으면 성능 문제가 발생할 수 있습니다.

스칼라 서브쿼리의 다양한 활용
-- 전체 평균과 각 상품의 가격 차이
SELECT p.name,
       p.price,
       (SELECT AVG(price) FROM products) AS avg_price,
       p.price - (SELECT AVG(price) FROM products) AS diff
FROM products p;

-- 각 부서의 사원 수
SELECT d.dept_name,
       (SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.dept_id) AS emp_count
FROM departments d;

스칼라 서브쿼리의 NULL 처리

서브쿼리 결과가 0행이면 NULL을 반환합니다. 이는 오류가 아니라 정상 동작입니다.

NULL 반환 예시
-- 주문이 없는 고객의 경우 last_order_date는 NULL
SELECT u.username,
       (SELECT MAX(o.order_date)
        FROM orders o
        WHERE o.user_id = u.user_id) AS last_order_date
FROM users u;

-- COALESCE로 NULL 대체
SELECT u.username,
       COALESCE(
           CAST((SELECT MAX(o.order_date)
                 FROM orders o
                 WHERE o.user_id = u.user_id) AS VARCHAR(20)),
           '주문 없음'
       ) AS last_order_date
FROM users u;

COALESCE의 인자는 보통 같은 타입으로 맞춰야 합니다. 날짜 값을 문자열 메시지와 함께 보여주려면 위처럼 날짜를 문자열로 변환하거나, 애플리케이션 표시 단계에서 NULL을 처리하는 편이 안전합니다.

스칼라 서브쿼리 vs LEFT JOIN

동일한 결과를 LEFT JOIN으로도 얻을 수 있습니다. 많은 경우 JOIN이 더 효율적인 실행 계획으로 이어질 수 있지만, DBMS와 인덱스 상태에 따라 달라질 수 있으므로 중요한 쿼리는 실행 계획을 확인해야 합니다.

동일한 결과의 두 가지 방법
-- 방법 1: 스칼라 서브쿼리
SELECT o.order_id,
       (SELECT u.username FROM users u WHERE u.user_id = o.user_id) AS name
FROM orders o;

-- 방법 2: LEFT JOIN (실행 계획에 따라 더 유리할 수 있음)
SELECT o.order_id, u.username AS name
FROM orders o
LEFT JOIN users u ON u.user_id = o.user_id;

인라인 뷰

FROM 절에 서브쿼리를 작성하면 그 결과가 임시 테이블처럼 동작합니다. 이를 인라인 뷰(Inline View)라 합니다. 대부분의 DBMS와 실무 스타일에서는 외부 쿼리에서 참조하기 쉽도록 별칭(alias)을 붙입니다.

인라인 뷰: 카테고리별 매출 요약
SELECT summary.category,
       summary.total_value,
       summary.product_count
FROM (
    SELECT category,
           SUM(price * stock) AS total_value,
           COUNT(*) AS product_count
    FROM products
    GROUP BY category
) summary
WHERE summary.total_value > 1000000
ORDER BY summary.total_value DESC;

인라인 뷰는 복잡한 쿼리를 단계적으로 작성할 때 유용합니다. 먼저 서브쿼리에서 데이터를 가공하고, 외부 쿼리에서 필터링하거나 정렬합니다.

인라인 뷰: TOP-N 패턴
-- 카테고리별 매출 상위 3개 카테고리
SELECT *
FROM (
    SELECT category,
           SUM(price * stock) AS total_value,
           ROW_NUMBER() OVER (ORDER BY SUM(price * stock) DESC) AS rn
    FROM products
    GROUP BY category
) ranked
WHERE ranked.rn <= 3;

인라인 뷰의 중첩

인라인 뷰 안에 또 다른 인라인 뷰를 넣을 수 있습니다. 하지만 깊은 중첩은 가독성을 떨어뜨리므로 CTE(WITH 절)로 대체하는 것이 좋습니다.

중첩 인라인 뷰 — 가독성이 낮음
SELECT *
FROM (
    SELECT category, avg_price
    FROM (
        SELECT category, AVG(price) AS avg_price
        FROM products
        GROUP BY category
    ) inner_view
    WHERE avg_price > 50000
) outer_view
ORDER BY avg_price DESC;

-- CTE로 개선 — 같은 결과, 더 읽기 쉬움
WITH category_avg AS (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
)
SELECT category, avg_price
FROM category_avg
WHERE avg_price > 50000
ORDER BY avg_price DESC;

WHERE 절 서브쿼리

WHERE 절에서 서브쿼리를 사용하면 다른 테이블의 데이터를 조건으로 활용할 수 있습니다.

IN 연산자

서브쿼리가 반환하는 목록에 값이 포함되는지 확인합니다.

IN 서브쿼리
-- 주문한 적이 있는 고객
SELECT * FROM users
WHERE user_id IN (SELECT user_id FROM orders);

-- 주문한 적이 없는 고객
SELECT * FROM users
WHERE user_id NOT IN (SELECT user_id FROM orders);

NOT IN과 NULL 함정

NOT IN에서 서브쿼리 결과에 NULL이 포함되면 전체 결과가 공집합이 됩니다. 이것은 매우 흔한 실수입니다.

NOT IN + NULL = 공집합 (위험!)
-- orders 테이블에 user_id가 NULL인 행이 있다면
-- 아래 쿼리는 결과가 0행!
SELECT * FROM users
WHERE user_id NOT IN (SELECT user_id FROM orders);

-- 원인: NULL과의 비교는 항상 UNKNOWN
-- user_id NOT IN (1, 2, NULL) → user_id != 1 AND user_id != 2 AND user_id != NULL
-- user_id != NULL은 UNKNOWN이므로 전체가 UNKNOWN → 거짓

-- 해결 방법 1: NOT IN에서 NULL 제거
SELECT * FROM users
WHERE user_id NOT IN (
    SELECT user_id FROM orders WHERE user_id IS NOT NULL
);

-- 해결 방법 2: NOT EXISTS 사용 (권장)
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);

EXISTS 연산자

EXISTS는 서브쿼리 결과가 하나라도 존재하면 TRUE를 반환합니다. NOT EXISTS는 결과가 없으면 TRUE입니다.

EXISTS vs IN
-- 주문한 적이 있는 고객 (EXISTS)
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);

-- 동일한 결과 (IN)
SELECT * FROM users
WHERE user_id IN (SELECT user_id FROM orders);

EXISTS는 논리적으로 조건을 만족하는 행이 하나라도 있으면 TRUE입니다. 많은 DBMS는 이를 효율적인 semi join 형태로 최적화할 수 있습니다. IN도 옵티마이저가 비슷하게 변환하는 경우가 있으므로, 둘의 실제 차이는 실행 계획으로 확인하는 것이 정확합니다.

ALL / ANY(SOME) 연산자

ALL은 서브쿼리의 모든 값과 비교하여 참인 경우, ANY(또는 SOME)는 하나라도 만족하면 참입니다.

ALL, ANY 사용 예시
-- ALL: 모든 도서 가격보다 비싼 상품 (= 도서 중 최고가보다 비쌈)
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = '도서');

-- 비어 있지 않고 NULL이 없는 집합에서는 MAX와 같은 뜻
SELECT * FROM products
WHERE price > (SELECT MAX(price) FROM products WHERE category = '도서');

-- ANY: 도서 중 하나라도보다 비싼 상품 (= 도서 중 최저가보다 비쌈)
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = '도서');

-- 비어 있지 않고 NULL이 없는 집합에서는 MIN과 같은 뜻
SELECT * FROM products
WHERE price > (SELECT MIN(price) FROM products WHERE category = '도서');

ALL/ANY는 서브쿼리 결과가 빈 집합이거나 NULL을 포함할 때 MAX/MIN 치환과 완전히 같지 않을 수 있습니다. 예를 들어 price > ALL (빈 집합)은 참으로 평가되지만, price > (SELECT MAX(...))에서 MAX가 NULL이면 조건 결과는 UNKNOWN이 됩니다. 그래서 치환은 값 집합이 비어 있지 않고 비교 값이 NULL이 아니도록 보장될 때만 안전합니다.

HAVING 절 서브쿼리

GROUP BY와 HAVING을 함께 사용할 때도 서브쿼리를 넣을 수 있습니다.

HAVING 절에 서브쿼리
-- 전체 평균 주문 수보다 많이 주문한 고객
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > (
    SELECT AVG(cnt) FROM (
        SELECT COUNT(*) AS cnt FROM orders GROUP BY user_id
    ) t
);

상관 서브쿼리 심화

상관 서브쿼리(Correlated Subquery)는 외부 쿼리의 현재 행 값을 참조하는 서브쿼리입니다. 논리적으로는 외부 쿼리의 각 행마다 서브쿼리가 평가됩니다.

상관 서브쿼리: 카테고리별 최고가 상품
SELECT p.name, p.price, p.category
FROM products p
WHERE p.price = (
    SELECT MAX(p2.price) FROM products p2
    WHERE p2.category = p.category   -- 외부의 p.category 참조
);

실행 과정

행이 많을수록 평가 비용이 커질 수 있으므로, 대량 데이터에서는 성능 문제가 발생할 수 있습니다. 다만 최신 DBMS는 일부 상관 서브쿼리를 조인, 세미 조인, 집계 조인으로 변환할 수 있으므로 실제 위험은 실행 계획으로 확인해야 합니다.

상관 서브쿼리를 JOIN으로 변환

많은 상관 서브쿼리는 JOIN이나 윈도우 함수로 변환할 수 있습니다.

상관 서브쿼리 → JOIN 변환
-- 원본: 상관 서브쿼리 (느릴 수 있음)
SELECT p.name, p.price, p.category
FROM products p
WHERE p.price = (
    SELECT MAX(p2.price) FROM products p2
    WHERE p2.category = p.category
);

-- 변환 1: JOIN 사용
SELECT p.name, p.price, p.category
FROM products p
INNER JOIN (
    SELECT category, MAX(price) AS max_price
    FROM products
    GROUP BY category
) m ON p.category = m.category AND p.price = m.max_price;

-- 변환 2: 윈도우 함수 사용 (중복 순위 처리와 실행 계획 확인 필요)
SELECT name, price, category
FROM (
    SELECT name, price, category,
           RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rk
    FROM products
) ranked
WHERE rk = 1;

다중 열 서브쿼리

서브쿼리에서 여러 열을 동시에 비교할 수 있습니다.

다중 열 서브쿼리
-- 각 카테고리에서 최고가인 상품 (다중 열 비교)
SELECT * FROM products
WHERE (category, price) IN (
    SELECT category, MAX(price) FROM products GROUP BY category
);

-- Oracle에서 다중 열 비교
SELECT * FROM employees
WHERE (department_id, salary) IN (
    SELECT department_id, MAX(salary) FROM employees GROUP BY department_id
);

주의: DBMS와 버전에 따라 다중 열 IN 서브쿼리의 최적화 방식이 다를 수 있습니다. 대량 데이터에서는 실행 계획을 확인하는 것이 좋습니다.

서브쿼리 활용 패턴

패턴 1: 존재 여부 확인 (EXISTS)

주문이 있는 상품만 조회
SELECT p.name, p.price
FROM products p
WHERE EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id
);

패턴 2: 비율 계산

전체 대비 비율
SELECT category,
       COUNT(*) AS cnt,
       ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM products), 1) AS pct
FROM products
GROUP BY category
ORDER BY pct DESC;

패턴 3: 이전/다음 값 참조

이전 주문과의 일수 차이
SELECT o.order_id,
       o.order_date,
       o.order_date - (
           SELECT MAX(o2.order_date)
           FROM orders o2
           WHERE o2.user_id = o.user_id
             AND o2.order_date < o.order_date
       ) AS days_since_last
FROM orders o
ORDER BY o.user_id, o.order_date;

-- 위 쿼리는 LAG 윈도우 함수로 더 효율적으로 작성 가능
SELECT order_id,
       order_date,
       order_date - LAG(order_date) OVER (
           PARTITION BY user_id ORDER BY order_date
       ) AS days_since_last
FROM orders
ORDER BY user_id, order_date;

패턴 4: 조건부 UPDATE

서브쿼리를 사용한 UPDATE
-- 3개월간 주문이 없는 고객을 휴면 처리
UPDATE users
SET status = '휴면'
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = users.user_id
      AND o.order_date >= CURRENT_DATE - INTERVAL '3 months'
);

-- DELETE에서도 사용 가능
DELETE FROM products
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = products.product_id
);

위 INTERVAL 문법은 PostgreSQL식 표현입니다. MySQL은 CURRENT_DATE - INTERVAL 3 MONTH, SQL Server는 DATEADD(month, -3, CAST(GETDATE() AS date))처럼 날짜 계산 문법이 다릅니다.

패턴 5: INSERT ... SELECT

서브쿼리를 사용한 INSERT
-- 월별 매출 요약을 집계 테이블에 삽입
INSERT INTO monthly_sales (month, total_amount, order_count)
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(total_amount),
       COUNT(*)
FROM orders
WHERE order_date >= DATE '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date);

DATE_TRUNC와 DATE 'YYYY-MM-DD'는 PostgreSQL 계열 문법입니다. Oracle, MySQL, SQL Server에서는 월 절단 함수와 날짜 리터럴 표기가 다르므로 대상 DBMS 문법에 맞춰 바꿔야 합니다.

서브쿼리 vs 조인 비교

비교 항목서브쿼리조인
가독성논리적 순서로 읽힘테이블 관계를 한눈에 봄
성능상관 서브쿼리는 느릴 수 있음실행 계획에 따라 더 유리할 수 있음
결과 중복중복 발생하지 않음1:N 관계에서 중복 가능
NULL 안전성NOT IN에 주의, NOT EXISTS 권장LEFT JOIN + IS NULL은 검사 컬럼 선택 주의
사용 시점단일 값 비교, EXISTS다중 컬럼 결합, 양쪽 데이터 표시
SELECT 절스칼라 서브쿼리 가능불가 (다른 방법 필요)
같은 결과, 다른 방법
-- 방법 1: 서브쿼리 (IN)
SELECT * FROM users
WHERE user_id IN (SELECT user_id FROM orders WHERE total_amount > 100000);

-- 방법 2: 조인
SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.total_amount > 100000;

-- 방법 3: EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.user_id AND o.total_amount > 100000
);

옵티마이저가 서브쿼리를 자동으로 조인으로 변환하는 경우도 있습니다(Subquery Unnesting). 하지만 불가능한 경우도 있으므로, 대량 데이터에서는 EXPLAIN으로 실행 계획을 확인하는 것이 좋습니다.

서브쿼리 성능 최적화

실행 계획 확인

EXPLAIN으로 서브쿼리 최적화 확인
EXPLAIN ANALYZE
SELECT * FROM users
WHERE user_id IN (SELECT user_id FROM orders);

-- 실행 계획에서 확인할 점:
-- "Semi Join" → 옵티마이저가 서브쿼리를 조인으로 변환함 (좋음)
-- "Subplan" → 서브쿼리가 그대로 실행됨 (대량 데이터에서 주의)

최적화 가이드

흔한 실수와 주의사항

실수 1: 스칼라 서브쿼리가 여러 행 반환

오류 발생
-- 오류! 서브쿼리가 여러 행을 반환
SELECT (SELECT username FROM users) FROM orders;
-- ERROR: more than one row returned by a subquery

-- 수정: 조건을 추가하여 1행만 반환
SELECT (SELECT username FROM users WHERE user_id = o.user_id)
FROM orders o;

실수 2: SELECT 절의 컬럼 수 불일치

컬럼 수 일치 필요
-- 오류! IN 서브쿼리는 단일 열만 반환해야 함
SELECT * FROM users
WHERE user_id IN (SELECT user_id, username FROM orders);

-- 수정: 비교할 열만 반환
SELECT * FROM users
WHERE user_id IN (SELECT user_id FROM orders);

실수 3: 불필요한 서브쿼리

불필요한 서브쿼리 제거
-- 불필요: 서브쿼리 안에 서브쿼리
SELECT * FROM products
WHERE category IN (
    SELECT category FROM (
        SELECT DISTINCT category FROM products
    ) t
);

-- 간결하게: 서브쿼리 없이
SELECT DISTINCT category FROM products;

핵심 정리

다음 절에서는 서브쿼리의 재사용 버전인 뷰와 CTE를 다루겠습니다.

OUTER JOIN

이전 페이지

뷰와 CTE

다음 페이지

목차

서브쿼리의 기본 분류반환 형태에 따른 분류외부 쿼리와의 관계에 따른 분류서브쿼리의 위치별 분류스칼라 서브쿼리스칼라 서브쿼리의 NULL 처리스칼라 서브쿼리 vs LEFT JOIN인라인 뷰인라인 뷰의 중첩WHERE 절 서브쿼리IN 연산자NOT IN과 NULL 함정EXISTS 연산자ALL / ANY(SOME) 연산자HAVING 절 서브쿼리상관 서브쿼리 심화실행 과정상관 서브쿼리를 JOIN으로 변환다중 열 서브쿼리서브쿼리 활용 패턴패턴 1: 존재 여부 확인 (EXISTS)패턴 2: 비율 계산패턴 3: 이전/다음 값 참조패턴 4: 조건부 UPDATE패턴 5: INSERT ... SELECT서브쿼리 vs 조인 비교서브쿼리 성능 최적화실행 계획 확인최적화 가이드흔한 실수와 주의사항실수 1: 스칼라 서브쿼리가 여러 행 반환실수 2: SELECT 절의 컬럼 수 불일치실수 3: 불필요한 서브쿼리핵심 정리