[SQL] 서브쿼리

1. 조건을 만족하는 ID 목록 → WHERE IN

주문을 3번 이상 한 회원 정보를 출력하라.

1.

orders에서
user_id별로 묶고
3번 이상인 user_id 목록을 만든다.

SELECT user_id
FROM orders o
WHERE o.user_id = u.user_id
GROUP BY o.user_id
HAVING COUNT(*) >= 3

2.

SELECT *
FROM users u
WHERE u.user_id IN (
    SELECT o.user_id
    FROM orders o
    WHERE o.user_id = u.user_id
    GROUP BY o.user_id
    HAVING COUNT(*) >= 3
);

문장에서 보이는 표현이

“~에 해당하는 회원”
“~한 ID를 가진 상품”
“특정 조건을 만족하는 사용자”

에 해당하면 일단 안쪽에서 ID 목록을 만들 수 있나?을 생각하자.

---

2. 평균/최대/최소와 비교 → Scalar Subquery

전체 평균 연봉보다 높은 직원을 구하라.

1.

전체 평균 연봉 = 숫자 하나

2.

SELECT *
FROM employee
WHERE salary > (
    SELECT AVG(salary)
    FROM employee
);

문장에서 보이는 표현이

전체 평균보다
전체 최대값과 같은
가장 비싼 가격보다
기준값 이상

같이 비교 기준 하나가 필요하면 scalar subquery를 먼저 생각하자.

---

3. 집계 결과를 재활용한다 → FROM (subquery)

회원별 총 구매금액을 구하고, 그 총 구매금액들의 평균을 구하라.

1.

1단계: 회원별 SUM
2단계: 그 SUM들의 AVG

로 끊어서 생각한다.

2.

SELECT AVG(x.total)
FROM (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) x;

단순히 집계 결과에 조건만 거는 경우라면 서브쿼리보다 HAVING으로 해결할 수 있다.
집계 결과를 다시 SELECT / JOIN / 집계해야 할 때 FROM (subquery)를 고려한다.

---

4. 그룹별 MAX/MIN + 원본의 다른 컬럼도 필요 → JOIN (subquery) ⭐

카테고리별 최고가 상품의 상품명, 가격을 출력하라.

1.

카테고리별 최고가격

SELECT category_id, MAX(price) AS max_price
FROM products
GROUP BY category_id

2.

이 결과에는 product_name이 없기 때문에 원본과 다시 붙인다.

SELECT p.*
FROM products p
JOIN (
    SELECT category_id, MAX(price) AS max_price
    FROM products
    GROUP BY category_id
) x
    ON p.category_id = x.category_id
   AND p.price = x.max_price;

이 방식은 최고가가 여러 개라면 해당 상품이 모두 출력된다.
그룹별 정확히 1개만 골라야 한다면 ROW_NUMBER() 같은 Window Function을 고려한다.

---

5. 있냐 / 없냐 → EXISTS / NOT EXISTS

1.

주문한 적 있는 회원을 구하라.

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

2.

주문한 적 없는 회원을 구하라.

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

문장에서 보이는 표현이

“~한 적이 있는”
“~가 존재하는”
“한 번도 ~하지 않은”

이라면 EXISTS / NOT EXISTS 을 떠올리자.

---

6. 각 행마다 자기 그룹 기준과 비교 → 상관 서브쿼리

자기 부서 평균보다 연봉 높은 직원

전체 평균 아님 주의
직원마다 속한 부서가 다르므로

SELECT *
FROM employee e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employee e2
    WHERE e2.dept_id = e.dept_id
);

이 때 안쪽에서 e.dept_id라는 바깥 행 값을 참조하게 된다.

문장에서 보이는 표현이

자기 부서 평균
같은 카테고리 평균
동일 회원의 과거 기록

같이 현재 행에 따라 비교 기준이 달라지면 상관 서브쿼리를 의심해보자.

---

정리

문제에서 보이는 요구	먼저 떠올릴 구조	핵심 사고
조건을 만족하는 회원/상품 목록	WHERE ... IN (subquery)	안쪽에서 ID 목록을 만들 수 있는가?
전체 평균/최대/최소와 비교	Scalar Subquery	안쪽에서 값 하나를 만들 수 있는가?
집계 결과를 다시 가공	FROM (subquery)	먼저 중간 테이블을 만든다
그룹별 MAX/MIN인 원본 행 조회	JOIN (subquery)	그룹별 대표값 계산 후 원본과 다시 JOIN
관련 데이터가 있나/없나	EXISTS / NOT EXISTS	값이 아니라 존재 여부만 확인
자기 부서/카테고리 기준과 비교	Correlated Subquery	현재 바깥 행에 따라 안쪽 조건이 달라짐