WHERE/FROM/SELECT절, CTE

SQL 서브쿼리 — 쿼리 안의 쿼리

💡 복잡한 문제를 단계별로 해결하는 서브쿼리를 완벽 정리한다.

---

🤔 서브쿼리가 뭐야?

쿼리 안에 들어가는 또 다른 쿼리.

복잡한 질문을 단계별로 나눠서 해결할 수 있다.

-- "평균보다 많이 주문한 유저는?"
SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);
             └────────── 서브쿼리 ──────────┘

---

📊 예제 테이블

[users]
| user_id | name | created_at |
|---------|------|------------|
| 1 | 김철수 | 2024-01-01 |
| 2 | 이영희 | 2024-01-15 |
| 3 | 박민수 | 2024-02-01 |

[orders]
| order_id | user_id | amount | order_date |
|----------|---------|--------|------------|
| 101 | 1 | 150000 | 2024-01-10 |
| 102 | 1 | 50000 | 2024-01-20 |
| 103 | 2 | 200000 | 2024-02-05 |
| 104 | 2 | 80000 | 2024-02-10 |
| 105 | 3 | 300000 | 2024-02-15 |

---

🗂️ 서브쿼리 위치별 분류

서브쿼리는 어디에 위치하느냐에 따라 다르게 동작한다.

SELECT (서브쿼리)  ← 스칼라 서브쿼리
FROM (서브쿼리)    ← 인라인 뷰
WHERE (서브쿼리)   ← 중첩 서브쿼리

---

1️⃣ WHERE 절 서브쿼리

단일 값 반환

-- 평균 주문금액보다 큰 주문 조회
SELECT *
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

서브쿼리가 하나의 값(156,000)을 반환 → 비교 가능

여러 값 반환 (IN)

-- 주문한 적 있는 유저 조회
SELECT *
FROM users
WHERE user_id IN (SELECT DISTINCT user_id FROM orders);

서브쿼리가 여러 값(1, 2, 3)을 반환 → IN으로 비교

여러 값 반환 (NOT IN)

-- 주문한 적 없는 유저 조회
SELECT *
FROM users
WHERE user_id NOT IN (SELECT DISTINCT user_id FROM orders);

---

2️⃣ FROM 절 서브쿼리 (인라인 뷰)

서브쿼리 결과를 임시 테이블처럼 사용.

-- 유저별 총 주문금액 구한 뒤, 100,000원 이상만 필터
SELECT *
FROM (
    SELECT 
        user_id,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
) AS user_totals  -- 별칭 필수!
WHERE total_amount >= 100000;

user_id	total_amount
1	200000
2	280000
3	300000

포인트: FROM 절 서브쿼리는 별칭(AS) 필수!

---

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

각 행마다 서브쿼리가 실행되어 하나의 값을 반환.

-- 각 주문에 전체 평균 금액 함께 표시
SELECT 
    order_id,
    amount,
    (SELECT AVG(amount) FROM orders) AS avg_amount
FROM orders;

order_id	amount	avg_amount
101	150000	156000
102	50000	156000
103	200000	156000
...	...	...

연관 서브쿼리 (스칼라)

-- 각 유저의 주문 건수를 함께 표시
SELECT 
    u.user_id,
    u.name,
    (SELECT COUNT(*) 
     FROM orders o 
     WHERE o.user_id = u.user_id) AS order_count
FROM users u;

user_id	name	order_count
1	김철수	2
2	이영희	2
3	박민수	1

---

4️⃣ 연관 서브쿼리 vs 비연관 서브쿼리

비연관 (Non-correlated)

서브쿼리가 독립적으로 실행됨. 한 번만 실행.

-- 서브쿼리가 메인쿼리와 무관
SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

연관 (Correlated)

서브쿼리가 메인쿼리의 값을 참조. 행마다 실행.

-- 서브쿼리가 메인쿼리의 user_id를 참조
SELECT * FROM orders o1
WHERE amount > (
    SELECT AVG(amount) 
    FROM orders o2 
    WHERE o2.user_id = o1.user_id  -- 메인쿼리 참조!
);

→ "자기 유저의 평균보다 큰 주문"

---

5️⃣ EXISTS / NOT EXISTS

EXISTS

서브쿼리 결과가 존재하는지 확인 (True/False)

-- 주문이 있는 유저만 조회
SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.user_id = u.user_id
);

NOT EXISTS

-- 주문이 없는 유저 조회
SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.user_id = u.user_id
);

IN vs EXISTS

상황	추천
서브쿼리 결과가 작을 때	IN
서브쿼리 결과가 클 때	EXISTS
NULL 처리 필요할 때	EXISTS

---

6️⃣ 실전 패턴

패턴 1: 최대/최소값 가진 행 찾기

-- 가장 큰 주문 찾기
SELECT *
FROM orders
WHERE amount = (SELECT MAX(amount) FROM orders);

패턴 2: 그룹별 최대값 행 찾기

-- 유저별 가장 큰 주문 찾기
SELECT *
FROM orders o1
WHERE amount = (
    SELECT MAX(amount)
    FROM orders o2
    WHERE o2.user_id = o1.user_id
);

패턴 3: Top N 구하기

-- 주문 금액 상위 3개
SELECT *
FROM orders
WHERE amount >= (
    SELECT MIN(amount)
    FROM (
        SELECT amount
        FROM orders
        ORDER BY amount DESC
        LIMIT 3
    ) AS top3
);

패턴 4: 이전 값과 비교

-- 직전 주문보다 금액이 큰 주문 찾기
SELECT *
FROM orders o1
WHERE amount > (
    SELECT amount
    FROM orders o2
    WHERE o2.order_id = o1.order_id - 1
);

→ 윈도우 함수(LAG)로 더 쉽게 가능!

---

7️⃣ WITH (CTE) — 서브쿼리의 진화

CTE (Common Table Expression): 서브쿼리에 이름을 붙여 재사용

-- 서브쿼리 방식
SELECT *
FROM (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) AS user_totals
WHERE total > 100000;

-- CTE 방식 (더 깔끔!)
WITH user_totals AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
)
SELECT *
FROM user_totals
WHERE total > 100000;

CTE 장점

1. 가독성: 쿼리가 위에서 아래로 읽힘
2. 재사용: 같은 서브쿼리 여러 번 사용 가능
3. 디버깅: 단계별로 확인 쉬움

여러 CTE 사용

WITH 
user_orders AS (
    SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
),
vip_users AS (
    SELECT user_id
    FROM user_orders
    WHERE total >= 200000
)
SELECT u.name, uo.cnt, uo.total
FROM users u
JOIN user_orders uo ON u.user_id = uo.user_id
WHERE u.user_id IN (SELECT user_id FROM vip_users);

---

🎯 실전 문제

문제 1: WHERE 서브쿼리

전체 평균 주문금액보다 큰 주문의 order_id, amount를 구하세요.

정답 보기

SELECT order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

---

문제 2: IN 서브쿼리

2024년 2월에 주문한 유저의 이름을 구하세요.

정답 보기

SELECT name
FROM users
WHERE user_id IN (
    SELECT DISTINCT user_id
    FROM orders
    WHERE order_date >= '2024-02-01'
      AND order_date < '2024-03-01'
);

---

문제 3: NOT EXISTS

주문을 한 번도 하지 않은 유저를 구하세요.

정답 보기

SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.user_id
);

또는:

SELECT *
FROM users
WHERE user_id NOT IN (SELECT DISTINCT user_id FROM orders);

---

문제 4: 연관 서브쿼리

각 유저의 평균 주문금액보다 큰 주문을 찾으세요.

정답 보기

SELECT *
FROM orders o1
WHERE amount > (
    SELECT AVG(amount)
    FROM orders o2
    WHERE o2.user_id = o1.user_id
);

---

문제 5: CTE 활용

총 주문금액 기준 상위 2명의 유저 이름과 총액을 구하세요.

정답 보기

WITH user_totals AS (
    SELECT 
        user_id,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
    ORDER BY total_amount DESC
    LIMIT 2
)
SELECT u.name, ut.total_amount
FROM user_totals ut
JOIN users u ON ut.user_id = u.user_id;

---

💡 서브쿼리 vs JOIN vs CTE

상황	추천
단순 필터링	서브쿼리
여러 테이블 데이터 조합	JOIN
복잡한 단계별 처리	CTE
같은 결과 여러 번 사용	CTE

성능 팁:

• 서브쿼리가 너무 많으면 → JOIN이나 CTE로 변환 고려
• 연관 서브쿼리는 행마다 실행 → 대용량에서 느릴 수 있음

---

📝 핵심 정리

[서브쿼리 위치]
WHERE절: 필터링 조건으로 사용
FROM절:  임시 테이블처럼 사용 (인라인 뷰)
SELECT절: 각 행에 값 추가 (스칼라)

[주요 연산자]
IN / NOT IN: 여러 값과 비교
EXISTS / NOT EXISTS: 존재 여부 확인
비교연산자: 단일 값과 비교

[CTE]
WITH 절로 서브쿼리에 이름 붙여 재사용
가독성과 유지보수에 좋음

---

🔗 다음 포스트

[SQL 시리즈 #4] 윈도우 함수 — ROW_NUMBER, RANK, LAG의 마법

---

📝 서브쿼리는 복잡한 문제를 단계별로 해결하는 도구다.
CTE를 활용하면 더 깔끔하고 읽기 좋은 쿼리를 작성할 수 있다!