서브쿼리와 뷰 - 질문 안의 질문

이 장에서 배우는 것

조인과 집계를 다룬 앞 장에서는 여러 표를 하나로 묶어 묻는 법을 살펴봤다. 이번 장은 한 걸음 더 나아가 "질문 안에 또 다른 질문을 넣는" 방법을 다룬다. 서브쿼리(subquery)로 평균값이나 존재 여부 같은 조건을 쿼리 안에서 직접 계산하고, 뷰(view)로 자주 쓰는 질문에 이름을 붙이고, 윈도 함수(window function)로 순위를 매기는 법을 SQL 연구소의 온라인 서점 데이터로 직접 실습한다.

• 스칼라 서브쿼리와 인라인 뷰가 무엇이고 어디에 쓰는지 구분한다
• IN, EXISTS, 상관 서브쿼리의 차이와 NULL 관련 함정을 안다
• 자주 쓰는 질문을 뷰로 저장하는 이유와 성능·보안상 주의점을 안다
• 윈도 함수로 그룹별 순위를 매기는 기본 문법을 익힌다

문제 상황

SQL 연구소 서비스팀에 하루 사이 세 가지 요청이 들어왔다.

먼저 MD가 "카테고리 평균가보다 비싼 책만 따로 보고 싶다"고 요청했다. 평균을 먼저 구하고 그 값을 각 책의 가격과 비교해야 하는데, 하나의 SELECT 문 안에서 평균값과 개별 행을 동시에 다루는 방법이 마땅치 않다.

다음으로 마케팅팀이 "리뷰를 한 번도 남기지 않은 회원에게 리뷰 작성 쿠폰을 보내고 싶다"고 했다. review 표는 회원마다 있을 수도 없을 수도 있는 목록이라, "리뷰가 없는 회원"이라는 조건을 표현하려면 다른 표에 없는 값을 찾는 방법이 필요하다.

마지막으로 큐레이션 화면 담당자가 "카테고리마다 가격이 가장 비싼 책 3권씩만 뽑아 달라"고 했다. GROUP BY는 그룹을 한 줄로 뭉개 버리므로, 그룹마다 여러 행을 남기면서 순위를 매기는 데는 맞지 않는다.

세 요청 모두 겉보기엔 다르지만 "쿼리 안에 또 다른 쿼리가 필요하다"는 점에서 같다. 이번 장에서 하나씩 풀어 본다.

서브쿼리: 질문 안에 넣는 또 다른 질문

서브쿼리는 SELECT 문 안에 들어가는 또 다른 SELECT 문이다. 바깥 쿼리에서 어느 자리에 쓰이느냐에 따라 스칼라 서브쿼리, 인라인 뷰, 중첩 서브쿼리로 부르는 이름이 달라진다.

스칼라 서브쿼리: 값 하나를 돌려주는 서브쿼리

스칼라 서브쿼리(scalar subquery)는 정확히 한 행, 한 열의 값 하나만 돌려주는 서브쿼리다. 값 하나가 들어갈 자리라면 SELECT 목록이든 WHERE 절의 비교식이든 어디에나 쓸 수 있다. 예를 들어 (SELECT AVG(price) FROM book)은 책 전체의 평균 가격이라는 값 하나를 돌려주므로, 각 책의 가격 옆에 나란히 놓고 비교할 수 있다. 다만 서브쿼리가 실수로 여러 행을 돌려주면 오류가 나는데, 이 함정은 뒤에서 다시 다룬다.

인라인 뷰: FROM 절에 쓰는 서브쿼리

인라인 뷰(inline view)는 FROM 절에 표 대신 자리 잡은 서브쿼리다. 집계나 필터를 먼저 끝낸 결과를 마치 하나의 표처럼 다시 조인하고 싶을 때 쓴다. 예를 들어 카테고리별 평균 가격을 먼저 구한 다음, 그 결과를 category 표와 다시 조인해 카테고리 이름을 붙이는 식이다. 인라인 뷰는 반드시 별칭을 줘야 하며, 자주 쓰는 인라인 뷰는 아예 뷰로 저장해 두는 편이 낫다는 점은 뒤에서 다룬다.

IN과 EXISTS, 그리고 상관 서브쿼리

WHERE 절 안에서 목록을 돌려주는 서브쿼리를 IN과 함께 쓰면 "이 목록에 속하는가"를 물을 수 있다. 반면 EXISTS는 서브쿼리가 값을 몇 개 돌려주는지보다 "한 행이라도 있는가"만 확인한다. 그래서 EXISTS 서브쿼리 안에는 흔히 SELECT 1처럼 아무 값이나 써 넣는다.

같은 조건을 IN과 EXISTS 중 무엇으로 표현할지 고르는 기준

상황	적합한 방식	이유
서브쿼리 결과에 NULL이 섞일 수 있을 때	EXISTS	NOT IN은 목록에 NULL이 하나만 있어도 결과가 통째로 비어 버린다
값 목록과 직접 비교하면 충분할 때	IN	목록 비교라는 의도가 코드에 그대로 드러난다
존재 여부만 확인하면 될 때	EXISTS	일치하는 첫 행을 찾으면 곧바로 검사를 멈춘다
바깥 행의 여러 조건을 서브쿼리에 걸어야 할 때	EXISTS	상관 조건을 자유롭게 추가할 수 있다

서브쿼리가 바깥 쿼리의 열을 참조하면 상관 서브쿼리(correlated subquery)가 된다. 예를 들어 "이 책의 카테고리 평균가"를 구하는 서브쿼리는 바깥 쿼리의 b.category_id를 참조하므로, 바깥 쿼리가 행을 하나씩 훑을 때마다 서브쿼리도 그 행에 맞춰 다시 실행된다. 이 특성 때문에 상관 서브쿼리는 표현력이 좋은 대신, 행이 많아지면 그만큼 반복 실행되어 느려질 수 있다.

뷰: 자주 쓰는 질문에 이름 붙이기

CREATE VIEW로 SELECT 문에 이름을 붙여 두면 그 뒤로는 표처럼 조회할 수 있다. 뷰는 데이터를 따로 저장하지 않고, 조회할 때마다 정의된 SELECT 문을 다시 실행한다는 점이 표와 다르다.

뷰의 첫째 쓸모는 재사용이다. 카테고리 평균가처럼 여러 화면에서 반복해 쓰는 집계를 뷰 하나로 감싸 두면, 같은 조인과 GROUP BY를 매번 다시 쓰지 않아도 된다. 둘째 쓸모는 보안이다. member 표 전체 대신 email처럼 민감한 열을 뺀 뷰만 만들어, 고객센터 계정에는 표 대신 그 뷰에만 조회 권한을 준다. 회원 등급이나 지역은 봐야 하지만 이메일까지는 볼 필요 없는 업무라면 이 방식이 안전하다.

반대로 주의할 점도 있다. 뷰는 결과를 저장해 두지 않으므로, 내부 쿼리가 무거우면 뷰를 조회할 때마다 그 비용이 그대로 든다. 뷰 위에 또 다른 뷰를 쌓아 올리면 실제로 어떤 쿼리가 실행되는지 추적하기 어려워진다. 그리고 집계나 DISTINCT, 여러 표의 조인이 섞인 뷰는 대개 조회 전용이라 그 뷰를 통해 데이터를 수정할 수 없다.

윈도 함수 맛보기: 그룹을 유지한 채 순위 매기기

GROUP BY는 같은 그룹에 속한 행들을 한 줄로 뭉갠다. 반면 윈도 함수는 행을 그대로 둔 채 각 행에 그룹 안에서의 순위 같은 계산 열을 하나 더 붙인다. OVER (PARTITION BY 열 ORDER BY 열) 구문에서 PARTITION BY는 순위를 매길 그룹을 나누고, ORDER BY는 그 그룹 안에서 무엇을 기준으로 줄 세울지 정한다. RANK()는 값이 같으면 같은 순위를 주고 다음 순위를 건너뛰며, ROW_NUMBER()는 값이 같아도 무조건 순서대로 번호를 매긴다. 이 장에서는 RANK()로 카테고리별 가격 순위만 가볍게 맛본다.

완성 코드

-- SQL 연구소 온라인 서점 데이터가 이미 적재되어 있다고 가정한다

-- 1) 카테고리별 평균 가격과 책 수를 보여주는 뷰
CREATE VIEW v_category_avg_price AS
SELECT category_id, AVG(price) AS avg_price, COUNT(*) AS book_count
FROM book
GROUP BY category_id;

-- 2) 스칼라 서브쿼리: 각 책 가격과 전체 평균 가격 차이
SELECT
    b.id,
    b.title,
    b.price,
    (SELECT AVG(price) FROM book) AS avg_price_all,
    b.price - (SELECT AVG(price) FROM book) AS price_gap
FROM book AS b
ORDER BY price_gap DESC
LIMIT 5;

-- 3) 상관 서브쿼리: 같은 카테고리 평균가보다 비싼 책
SELECT
    b.id,
    b.title,
    b.category_id,
    b.price
FROM book AS b
WHERE b.price > (
    SELECT AVG(b2.price)
    FROM book AS b2
    WHERE b2.category_id = b.category_id
)
ORDER BY b.category_id, b.price DESC;

-- 4) 인라인 뷰(뷰를 곧장 조인): 카테고리 이름과 함께 평균가 보기
SELECT
    c.name AS category_name,
    v.book_count,
    ROUND(v.avg_price, 0) AS avg_price
FROM v_category_avg_price AS v
JOIN category AS c ON c.id = v.category_id
ORDER BY v.avg_price DESC;

-- 5) NOT EXISTS: 리뷰를 한 번도 쓰지 않은 회원
SELECT m.id, m.name, m.grade
FROM member AS m
WHERE NOT EXISTS (
    SELECT 1
    FROM review AS r
    WHERE r.member_id = m.id
);

-- 6) IN: 배송 완료 주문을 한 번이라도 받은 회원
SELECT m.id, m.name
FROM member AS m
WHERE m.id IN (
    SELECT o.member_id
    FROM orders AS o
    WHERE o.status = 'DELIVERED'
);

-- 7) 윈도 함수: 카테고리별 가격 순위 3위까지
SELECT category_name, title, price, price_rank
FROM (
    SELECT
        c.name AS category_name,
        b.title,
        b.price,
        RANK() OVER (
            PARTITION BY b.category_id
            ORDER BY b.price DESC
        ) AS price_rank
    FROM book AS b
    JOIN category AS c ON c.id = b.category_id
) AS ranked
WHERE price_rank <= 3
ORDER BY category_name, price_rank;

줄별 해설

1번 CREATE VIEW는 category_id별로 AVG(price)와 COUNT(*)를 미리 구해 v_category_avg_price라는 이름을 붙인다. 이 뷰는 저장된 값이 아니라 쿼리 정의라서, 4번처럼 조회할 때마다 GROUP BY가 다시 실행된다.

2번은 SELECT 목록 안에 스칼라 서브쿼리 (SELECT AVG(price) FROM book)를 두 번 써서 전체 평균가와 각 책의 가격 차이를 한 행씩 나란히 보여준다. 이 서브쿼리는 바깥 쿼리의 어떤 열도 참조하지 않으므로 상관 서브쿼리가 아니라 한 번만 계산된다.

3번은 WHERE 절 서브쿼리 안에서 b2.category_id = b.category_id로 바깥 쿼리의 열을 참조한다. 바깥 쿼리가 책을 한 권씩 훑을 때마다 그 책과 같은 카테고리의 평균가를 다시 계산하므로 전형적인 상관 서브쿼리다.

4번은 v_category_avg_price를 표처럼 FROM 절에 두고 category와 곧장 조인한다. 뷰를 인라인 뷰처럼 재사용하는 방식이다.

5번은 NOT EXISTS 안에서 r.member_id = m.id로 회원과 리뷰를 연결한다. 그 회원이 쓴 리뷰가 하나도 없으면 서브쿼리가 아무 행도 돌려주지 않으므로 NOT EXISTS가 참이 된다.

6번은 orders에서 상태가 DELIVERED인 주문의 member_id 목록을 만들고, 그 목록에 속하는 회원만 IN으로 걸러낸다. member_id는 NULL일 수 없는 외래 키이므로 이 자리는 NOT IN이 아니라면 IN을 써도 안전하다.

7번은 안쪽 SELECT에서 RANK() OVER(PARTITION BY b.category_id ORDER BY b.price DESC)로 카테고리마다 가격이 비싼 순서대로 순위를 매긴 뒤, 그 결과를 ranked라는 인라인 뷰로 감싸고 바깥 WHERE에서 price_rank가 3 이하인 행만 남긴다. 윈도 함수가 계산한 열은 같은 SELECT의 WHERE에서 바로 걸러낼 수 없어 한 번 더 감싸야 한다는 점은 뒤에서 다시 짚는다.

실행 결과

아래 결과는 SQL 연구소 데이터로 실행했을 때 나올 수 있는 예시 결과이며, 실제 값은 적재된 데이터에 따라 달라진다.

3) 카테고리 평균보다 비싼 책

id    title              category_id  price
----  -----------------  -----------  -----
12    클린 아키텍처         1            32000
15    리팩터링 2판          1            29000
31    언어의 온도           3            15000

5) 리뷰를 한 번도 쓰지 않은 회원

id    name    grade
----  ------  -----
7     한지우   BASIC
19    오세훈   SILVER

7) 카테고리별 가격 순위 3위까지

category_name  title            price  price_rank
-------------  ---------------  -----  ----------
에세이          언어의 온도        15000  1
에세이          여덟 단어          14000  2
에세이          흐르는 강물처럼      13000  3
프로그래밍      클린 아키텍처       32000  1
프로그래밍      리팩터링 2판       29000  2
프로그래밍      이펙티브 SQL       27000  3

실무에서 자주 틀리는 것

NOT IN에 NULL이 섞인 서브쿼리를 그대로 쓴다

NOT IN은 목록 안에 NULL이 하나만 있어도 모든 비교가 알 수 없음(UNKNOWN)이 되어 결과가 통째로 비어 버린다. orders.coupon_id는 쿠폰을 쓰지 않은 주문에서 NULL이므로 이 함정에 그대로 걸린다.

-- 틀린 코드: 한 번도 쓰이지 않은 쿠폰을 찾으려 했지만 결과가 항상 0건이다
SELECT c.id, c.code
FROM coupon AS c
WHERE c.id NOT IN (
    SELECT o.coupon_id FROM orders AS o
);

-- 고친 코드: NOT EXISTS는 NULL이 섞여도 안전하다
SELECT c.id, c.code
FROM coupon AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.coupon_id = c.id
);

스칼라 서브쿼리가 여러 행을 돌려줄 수 있다는 것을 잊는다

책 한 권에 저자가 둘 이상이면, 저자 이름을 스칼라 서브쿼리로 가져오려는 시도는 실행 중 행이 두 개 이상 돌아와 오류가 난다.

-- 틀린 코드: 공저인 책에서 "subquery returned more than 1 row" 오류가 난다
SELECT
    b.id,
    b.title,
    (SELECT a.name
     FROM book_author AS ba
     JOIN author AS a ON a.id = ba.author_id
     WHERE ba.book_id = b.id) AS author_name
FROM book AS b;

-- 고친 코드: 여러 저자를 한 값으로 묶어 스칼라 서브쿼리 자리에 맞춘다
SELECT
    b.id,
    b.title,
    (SELECT GROUP_CONCAT(a.name, ', ')
     FROM book_author AS ba
     JOIN author AS a ON a.id = ba.author_id
     WHERE ba.book_id = b.id AND ba.role = 'AUTHOR') AS author_name
FROM book AS b;

같은 값을 매번 다시 계산하는 상관 서브쿼리를 그대로 둔다

완성 코드 3번처럼 카테고리 평균가를 상관 서브쿼리로 구하면, 같은 카테고리에 책이 여러 권 있어도 행마다 평균을 다시 계산한다. 카테고리 수가 책 수보다 훨씬 적다면 뷰로 한 번만 집계하고 조인하는 편이 낫다.

-- 비효율적인 코드: 책이 많을수록 카테고리 평균 계산이 반복된다
SELECT b.id, b.title, b.price
FROM book AS b
WHERE b.price > (
    SELECT AVG(b2.price) FROM book AS b2 WHERE b2.category_id = b.category_id
);

-- 고친 코드: 카테고리 평균을 한 번만 구해 두고 조인해서 비교한다
SELECT b.id, b.title, b.price
FROM book AS b
JOIN v_category_avg_price AS v ON v.category_id = b.category_id
WHERE b.price > v.avg_price;

윈도 함수가 만든 열을 같은 SELECT의 WHERE에서 바로 거르려 한다

WHERE 절은 윈도 함수가 계산되기 전 단계에서 행을 거르므로, 같은 SELECT 안에서 방금 만든 순위 열을 WHERE로 바로 참조할 수 없다.

-- 틀린 코드: price_rank를 WHERE에서 바로 쓸 수 없어 오류가 난다
SELECT
    c.name AS category_name,
    b.title,
    b.price,
    RANK() OVER (PARTITION BY b.category_id ORDER BY b.price DESC) AS price_rank
FROM book AS b
JOIN category AS c ON c.id = b.category_id
WHERE price_rank <= 3;

-- 고친 코드: 인라인 뷰로 한 번 감싼 뒤 바깥 WHERE에서 거른다
SELECT category_name, title, price, price_rank
FROM (
    SELECT
        c.name AS category_name,
        b.title,
        b.price,
        RANK() OVER (PARTITION BY b.category_id ORDER BY b.price DESC) AS price_rank
    FROM book AS b
    JOIN category AS c ON c.id = b.category_id
) AS ranked
WHERE price_rank <= 3;

한눈에 보기

서브쿼리 종류와 쓰는 자리

종류	쓰는 위치	예시 상황
스칼라 서브쿼리	SELECT 절, WHERE 절의 비교 값	책 가격과 전체 평균가 비교
인라인 뷰	FROM 절	집계 결과를 다시 조인해서 쓸 때
IN 서브쿼리	WHERE 절 값 목록	특정 조건을 만족하는 id 목록에 속하는지
EXISTS / NOT EXISTS	WHERE 절 존재 확인	리뷰를 쓴 적 있는지, 주문한 적 있는지
상관 서브쿼리	WHERE/SELECT, 바깥 행 참조	카테고리 평균보다 비싼 책
윈도 함수	SELECT 절, OVER 절	그룹별 순위 매기기

뷰를 쓸 때 얻는 것과 조심할 것

구분	내용
재사용성	자주 쓰는 조인·집계를 이름 하나로 감싸 반복 작성을 줄인다
보안	민감한 열을 뺀 뷰만 특정 권한에 노출해 접근을 제한할 수 있다
성능	뷰는 저장된 결과가 아니라 쿼리를 감싼 것이라 내부 쿼리 비용이 그대로 든다
갱신 가능 여부	집계·DISTINCT·여러 표 조인이 섞인 뷰는 대개 조회 전용이다

연습 문제

1. member 표에서, 한 번도 주문한 적 없는 회원의 id와 이름을 NOT EXISTS를 이용해 조회하는 쿼리를 작성하라.
2. 카테고리별로 등록된 책 권수와 평균 가격을 보여주는 뷰 v_category_stat을 만들고, 이 뷰를 이용해 평균 가격이 20,000원 이상인 카테고리만 출력하는 쿼리를 작성하라.
3. 윈도 함수를 사용해 출판사(publisher)별로 가격이 가장 비싼 책을 한 권씩 뽑아 출판사 이름, 책 제목, 가격을 출력하는 쿼리를 작성하라.

정답과 해설

1번

SELECT m.id, m.name
FROM member AS m
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.member_id = m.id
);

orders.member_id는 NULL이 될 수 없는 외래 키라 NOT IN을 써도 결과는 같지만, EXISTS 계열을 기본값으로 쓰는 습관을 들이면 NULL이 섞일 수 있는 다른 상황에서도 같은 패턴을 안전하게 재사용할 수 있다.

2번

CREATE VIEW v_category_stat AS
SELECT
    b.category_id,
    COUNT(*) AS book_count,
    AVG(b.price) AS avg_price
FROM book AS b
GROUP BY b.category_id;

SELECT c.name AS category_name, v.book_count, ROUND(v.avg_price, 0) AS avg_price
FROM v_category_stat AS v
JOIN category AS c ON c.id = v.category_id
WHERE v.avg_price >= 20000
ORDER BY v.avg_price DESC;

뷰를 먼저 만들어 두면 "평균 가격 기준"을 바꿔 가며 여러 번 조회할 때 GROUP BY를 다시 쓰지 않아도 된다.

3번

SELECT publisher_name, title, price
FROM (
    SELECT
        p.name AS publisher_name,
        b.title,
        b.price,
        RANK() OVER (PARTITION BY b.publisher_id ORDER BY b.price DESC) AS rnk
    FROM book AS b
    JOIN publisher AS p ON p.id = b.publisher_id
) AS ranked
WHERE rnk = 1
ORDER BY publisher_name;

한 출판사에서 가장 비싼 책이 가격까지 같은 두 권이면 RANK()는 둘 다 1위로 남긴다. 반드시 한 권씩만 필요하다면 RANK() 대신 동점을 허용하지 않는 ROW_NUMBER()를 쓴다.