[DB] 서브쿼리 – 스칼라 서브쿼리, 다중 행/열 서브쿼리, 상관 서브쿼리

🧩 서브쿼리(Subquery)란 무엇인가?

 

서브쿼리는 하나의 SQL 쿼리 문 안에 포함된 또 다른 SELECT 쿼리를 의미한다.

복잡한 문제를 여러 단계로 나누어 생각해야 할 때, 이 단계들을 하나의 쿼리로 합쳐주는 강력한 기술이다.

예를 들어, "평균 가격보다 비싼 상품 찾기"는 다음과 같이 하나의 쿼리로 해결할 수 있다.

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products); -- 서브쿼리 결과: 평균 가격

 

동작 원리:

1. 바깥쪽의 메인 쿼리가 실행되기 전, 괄호 () 안에 있는 서브쿼리가 먼저 실행된다.
2. 데이터베이스는 서브쿼리의 실행 결과를 메인 쿼리에게 전달한다.
3. 메인 쿼리는 그 결과를 사용해 최종 작업을 수행한다.

 

---

🧩 서브쿼리의 종류와 위치

 

서브쿼리는 사용되는 위치와 반환하는 결과의 형태에 따라 다양하게 활용된다.

 

1. 스칼라 서브쿼리 (Scalar Subquery)

• 특징: 결과가 단일 행 / 단일 열, 즉 하나의 값(Scalar)만 반환하는 서브쿼리
• 사용 위치: SELECT, WHERE, HAVING 절 등 단일 값이 필요한 모든 곳에서 사용 가능
• 연산자: =, >, < 등 일반적인 비교 연산자와 함께 사용
• ⚠️ 서브쿼리가 두 개 이상의 행을 반환하면 오류가 발생하므로, 결과가 반드시 단일 행임을 보장해야 한다.

 

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

 

 

2. 다중 행 서브쿼리 (Multi-row Subquery)

• 특징: 결과가 여러 행 / 단일 열, 즉 값의 목록(List)을 반환하는 서브쿼리
• 사용 위치: WHERE, HAVING 절에서 사용
• 연산자: IN, ANY, ALL 등 목록과 비교할 수 있는 연산자와 함께 사용
  • IN: 목록에 포함된 값 중 하나라도 일치하면 참 (가장 직관적이고 흔하게 사용됨)
  • > ANY: 목록의 최솟값보다 크면 참
  • > ALL: 목록의 최댓값보다 커야 참
  • ⚠️ 실무에서는 ANY, ALL 보다 MIN(), MAX() 집계 함수를 사용하는 것이 더 명확할 수 있다.

SELECT *
FROM orders
WHERE user_id IN (
    SELECT user_id 
    FROM users 
    WHERE address LIKE '서울%'
);

 

 

3. 다중 열 서브쿼리 (Multi-column Subquery)

• 특징: 결과가 여러 열을 반환하는 서브쿼리
• 사용 위치: WHERE, HAVING 절에서 여러 열을 동시에 비교할 때 사용
  • WHERE (column1, column2) IN (SELECT col1, col2 FROM ...) 형태로 사용

SELECT order_id, user_id, order_date
FROM orders
WHERE (user_id, order_date) IN (
    SELECT user_id, MIN(order_date)
    FROM orders
    GROUP BY user_id
);

 

 

---

🧩 상관 서브쿼리 (Correlated Subquery): 메인 쿼리와의 대화

 

상관 서브쿼리는 서브쿼리가 독립적으로 실행되지 않고, 메인 쿼리의 각 행(Row)과 연관 관계를 맺고 동작하는 고급 기법이다.

즉, 메인 쿼리가 한 행을 읽을 때마다, 그 행의 값을 서브쿼리에 전달하여 실행하는 방식이다.

 

⚠️ 성능 주의: 상관 서브쿼리는 메인 쿼리의 행 수만큼 서브쿼리가 반복 실행될 수 있으므로, 조인으로 재작성하는 것을 고려해야 한다.

 

활용 예시: "각 상품을 자신이 속한 카테고리의 평균 가격과 비교하기"

SELECT name, category, price
FROM products p1 -- 메인 쿼리
WHERE price >= (
    SELECT AVG(price)
    FROM products p2
    WHERE p2.category = p1.category -- 메인 쿼리의 category를 받아 실행되는 상관 서브쿼리
);

 

동작 원리:

1. 메인 쿼리가 한 행을 읽는다.
2. 그 행의 값을 서브쿼리에게 전달한다.
3. 서브쿼리가 실행되고, 결과 값을 메인 쿼리에게 전달한다.
4. 메인 쿼리는 그 결과를 사용해 최종 작업을 수행하고, 다시 다음 행을 읽는다.

 

이처럼 서브쿼리가 메인 쿼리의 값을 알아야만 실행될 수 있을 때 상관 서브쿼리를 사용해야 한다.

 

---

✅ EXISTS: 상관 서브쿼리의 대표적인 활용 사례

 

EXISTS 키워드는 서브쿼리의 결과 값이 아닌, 오직 결과 행의 존재 여부만 확인한다.

결과 행이 하나라도 있으면 TRUE를 반환하며, 대용량 데이터 처리 시 IN 보다 효율적일 수 있다.

 

예를 들어, "한 번이라도 주문된 상품 찾기"를 IN과 EXISTS를 사용하여 각각 해결한 결과는 다음과 같다.

-- IN 사용
SELECT product_id, name, price
FROM products
WHERE product_id IN (SELECT DISTINCT product_id FROM orders);

-- EXISTS 사용
SELECT product_id, name, price
FROM products p -- 메인 쿼리
WHERE EXISTS (
    SELECT 1 -- 행의 존재 여부만 확인하므로, 관례적으로 1 사용
    FROM orders o
    WHERE o.product_id = p.product_id -- 메인 쿼리의 product_id를 받아 실행되는 상관 서브쿼리
);

 

---

🧩 SELECT 절의 서브쿼리 (스칼라 서브쿼리)

 

SELECT 절에 위치하는 서브쿼리는 그 자체가 하나의 '열'처럼 동작하며, 반드시 단일 값(스칼라)을 반환해야 한다.

 

활용 예시: "각 상품 정보와 함께, 해당 상품의 총 주문 횟수를 함께 조회하기"

SELECT
    p.product_id,
    p.name,
    p.price,
    -- SELECT 절의 서브쿼리는 상관 서브쿼리일 때 더 강력
    (SELECT COUNT(*)
     FROM orders o
     WHERE o.product_id = p.product_id) AS order_count
FROM
    products p;

 

---

🧩 FROM 절의 서브쿼리 (테이블 서브쿼리, 인라인 뷰)

 

FROM 절에 위치하는 서브쿼리는 그 실행 결과가 하나의 독립된 가상 테이블(Inline View)처럼 사용된다.

집계나 그룹핑된 결과를 다시 한번 조인하거나 필터링해야 할 때 매우 유용하다.

 

⚠️ 주의: FROM 절의 서브쿼리는 반드시 별칭(Alias)을 가져야 한다.

 

활용 예시: "각 상품 카테고리별로, 가장 비싼 상품의 이름과 가격을 조회하기"

SELECT
    p.product_id,
    p.name,
    p.price
FROM
    products p
JOIN
    -- 인라인 뷰 (가상의 테이블)
    (SELECT category, MAX(price) AS max_price
     FROM products
     GROUP BY category) AS cmp -- FROM 절의 서브쿼리는 반드시 별칭 필요
ON
    p.category = cmp.category AND p.price = cmp.max_price;

 

---

🤔 서브쿼리 vs 조인: 무엇을 선택할까?

 

대부분의 문제는 서브쿼리와 조인 두 가지 방법으로 모두 해결할 수 있다.

 

• 성능: 일반적으로 데이터베이스 쿼리 옵티마이저는 조인을 더 효율적으로 처리하는 경우가 많다.
• 가독성: 문제 해결의 논리적 단계를 명확하게 보여주는 서브쿼리가 코드를 이해하기 더 쉬울 때가 많다.

 

✅ 실무 가이드라인: 우선적으로 조인을 고려하되, 쿼리가 너무 복잡해지거나 가독성이 현저히 떨어질 경우 서브쿼리를 사용하는 것이 좋다. 성능이 중요한 경우, 반드시 쿼리 실행 계획을 분석하여 최적의 방법을 선택해야 한다.