[Database] SQL 서브쿼리(Subquery) 정리: 개념, 위치별 활용

1. 서브쿼리 정의 및 분류 기준

1-1. 정의

• 서브쿼리(Subquery)
  다른 SQL문 안에 중첩된 SELECT 문
  → “먼저 이 쿼리로 집합을 만들고, 그 결과를 밖에서 다시 사용”하는 구조
  
  
• 주로 사용되는 위치
  • SELECT 절: 스칼라 서브쿼리 (값 1개)
  • FROM 절: 인라인 뷰 / 파생 테이블
  • WHERE / HAVING 절: 필터링 조건 (IN, EXISTS, =, >, ANY, ALL 등)

 

1-2. 분류 기준

1. 상관 vs 비상관
   • 비상관 서브쿼리:
     안쪽 쿼리가 바깥 쿼리와 독립적. 먼저 한 번 실행 후 결과를 재사용
   • 상관 서브쿼리:
     안쪽 쿼리가 바깥 쿼리의 컬럼을 참조 → 바깥 행마다 반복 실행되는 형태
2. 반환 형태
   • 단일 행(single row): 값 1개 (=, >, < 등 비교에 사용)
   • 다중 행(multi row): 여러 행 (IN, EXISTS, ANY, ALL 등과 함께 사용)
   • 단일 컬럼 vs 다중 컬럼:
     • 단일 컬럼: IN (SELECT col)
     • 다중 컬럼: (col1, col2) IN (SELECT c1, c2 …)

 

2. 위치별 서브쿼리 활용 패턴

2-1. SELECT 절: 스칼라 서브쿼리

• 특징: 행마다 값 1개를 계산해서 컬럼처럼 붙이고 싶을 때 사용

-- 각 부서의 평균 급여를 함께 표시
SELECT e.emp_id,
e.emp_name,
e.salary,
(SELECT AVG(salary)
FROM emp e2
WHERE e2.dept_id = e.dept_id) AS dept_avg_salary
FROM emp e;

• 장점: 간단하고 직관적
• 단점: 상관 서브쿼리 형태가 되기 쉬워서 부서 행 수 × 직원 수 만큼 반복 실행될 수 있음

 

2-2. FROM 절: 인라인 뷰(파생 테이블)

• 특징: 복잡한 집계/필터를 먼저 한 번에 처리하고, 그 결과를 테이블처럼 JOIN하고 싶을 때.

-- 부서별 평균 급여를 먼저 계산 후, 평균보다 높은 직원만 조회
SELECT e.emp_id, e.emp_name, e.salary, d.dept_avg_salary
FROM emp e
JOIN (
SELECT dept_id, AVG(salary) AS dept_avg_salary
FROM emp
GROUP BY dept_id
) d
ON e.dept_id = d.dept_id
WHERE e.salary > d.dept_avg_salary;

• 장점:
  • 복잡한 로직을 블록으로 분리 → 가독성·재사용성 좋음
  • 집계 후 JOIN으로 바깥은 비교만 수행 → 상관 서브쿼리보다 성능 유리한 경우 많음

 

2-3. WHERE / HAVING 절: 필터용 서브쿼리

(1) IN + 서브쿼리

-- 주문이 존재하는 고객만
SELECT *
FROM customer
WHERE customer_id IN (
SELECT DISTINCT customer_id
FROM orders
);

• 다중 행 반환 서브쿼리에 주로 사용
• 간단하지만 NOT IN + NULL 주의 (뒤에서 다시 언급)

(2) EXISTS / NOT EXISTS

-- 주문이 존재하는 고객(행 존재 여부만 확인)
SELECT c.*
FROM customer c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);

-- 주문이 전혀 없는 고객
SELECT c.*
FROM customer c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);

• 보통 상관 서브쿼리 형태
• “존재 여부” 판단에 최적화된 패턴

(3) 단일 행 서브쿼리 비교

-- 회사 전체 평균 급여보다 높은 직원
SELECT *
FROM emp
WHERE salary > (
SELECT AVG(salary)
FROM emp
);

• 서브쿼리는 딱 1행 1컬럼만 반환해야 함. (여러 행이면 오류)

 

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

3-1. 비상관 서브쿼리

• 바깥 쿼리와 독립.
• 실행 순서 개념
  1. 서브쿼리 1번 실행 → 결과 집합 생성
  2. 바깥 쿼리가 그 결과를 이용해 필터링/비교

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

• 장점: 실행 계획 최적화에 유리, 1회 실행

3-2. 상관 서브쿼리

• 서브쿼리 안에서 바깥 쿼리 컬럼을 참조

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

• 실행 개념: 바깥 행 한 줄 읽을 때마다 서브쿼리 다시 실행
• 장점: “행별 조건” 같은 유연한 필터링 가능
• 단점: 데이터량이 크면 성능 이슈 → 가능하면 인라인 뷰 + JOIN으로 대체 검토

 

4. IN vs EXISTS vs JOIN 간 차이

4-1. 비교

구분IN + 서브쿼리EXISTS / NOT EXISTSJOIN

주 용도	특정 값이 집합에 포함되는지 확인	행 존재 여부 판단	두 테이블의 데이터 결합
반환 형태	바깥 테이블 컬럼	바깥 테이블 컬럼	양쪽 컬럼 모두(필요시 필터링)
성능 관점	집합 크기·인덱스에 따라 달라짐	조건 충족하는 행 발견 즉시 종료 가능	조인 방식(NL/Hash/Merge)에 영향 받음
특징	간단하고 직관적	“있냐/없냐”에 특화, 보안·제약조건 검증에 자주	레포트, 조회용 SELECT의 기본 패턴

4-2. NOT IN + NULL 주의

-- 이렇게 작성하면 위험
SELECT *
FROM customer
WHERE customer_id NOT IN (
SELECT customer_id
FROM orders
);

• orders.customer_id에 NULL이 포함되어 있으면,
  NOT IN 비교가 전부 **UNKNOWN(=FALSE 취급)**로 되어 결과가 비어버릴 수 있음
• 안전한 패턴

SELECT *
FROM customer c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);

 

5. 서브쿼리 실전 패턴 모음

5-1. “부서 평균보다 높은 급여” (단일 행·상관 서브쿼리)

SELECT e.emp_id, e.emp_name, e.dept_id, e.salary
FROM emp e
WHERE e.salary > (
SELECT AVG(salary)
FROM emp e2
WHERE e2.dept_id = e.dept_id
);

• “같은 부서 안에서 상대적으로 높은 급여” 케이스

---

5-2. “마지막 주문일 기준 최신 주문만” (NOT EXISTS 패턴)

SELECT o.*
FROM orders o
WHERE NOT EXISTS (
SELECT 1
FROM orders o2
WHERE o2.customer_id = o.customer_id
AND o2.order_date > o.order_date
);

• 각 고객별로 가장 최근 주문 1건만 가져오는 대표 패턴
• “동일 키에 대해 더 나은(더 큰) 값이 존재하지 않는 행만 선택”할 때 NOT EXISTS 자주 사용

---

5-3. “집계 결과와 비교” (HAVING 서브쿼리)

-- 전체 평균 주문 금액보다 부서 평균이 높은 부서만
SELECT dept_id, AVG(amount) AS dept_avg
FROM orders
GROUP BY dept_id
HAVING AVG(amount) > (
SELECT AVG(amount)
FROM orders
);

• HAVING 절에서 집계 결과를 서브쿼리와 비교하는 패턴

---

5-4. 인라인 뷰로 Top-N per Group 흉내내기

(실제 서비스에서는 Window 함수를 쓰는게 일반적이지만, 서브쿼리로도 가능)

-- 각 부서별 급여 상위 1명
SELECT e.*
FROM emp e
WHERE 1 = (
SELECT COUNT(*)
FROM emp e2
WHERE e2.dept_id = e.dept_id
AND e2.salary >= e.salary
);

• 같은 부서 안에서 자기보다 급여가 크거나 같은 사람 수가 1명(=본인)인 경우만 선택
• 데이터 많으면 비효율 → 실제 운영에서는 ROW_NUMBER() OVER(PARTITION BY … ORDER BY …)가 훨씬 낫다.