SQL 서브쿼리 는 쿼리 안에 괄호로 넣은 또 하나의 쿼리이고, WITH(CTE) 는 그 안쪽 쿼리에 이름을 붙여 맨 위로 꺼내 둔 것입니다. 지난 글까지는 쿼리 하나로 거르고, 묶고, 붙였는데요, 「평균보다 큰 주문만」, 「회원별 합계를 낸 다음 그 합계의 평균」처럼 계산을 두 번 해야 답이 나오는 질문은 쿼리 하나의 SELECT 로는 풀기 어렵습니다. 저도 SQL 을 처음 배우면서 괄호가 겹겹이 들어간 쿼리를 보면 어디서부터 읽어야 할지 몰랐는데요, 이번 글에서는 지난 글의 문구점 표로 서브쿼리를 WHERE 와 FROM 에 넣어 보고, 같은 쿼리를 WITH 로 바꿔 단계별로 나누는 법을 직접 따라 해 보겠습니다. 쿼리는 BigQuery(GoogleSQL) 문법으로 적었습니다. 지난 글: SQL JOIN 으로 표 합치기: INNER JOIN 과 LEFT JOIN 차이
SQL 기초 시리즈 11/12 · 전체 커리큘럼
오늘 배울 개념
엑셀로 치면 서브쿼리는 보조 셀 과 비슷합니다. 평균보다 큰 주문을 고르려면 먼저 빈 셀에 =AVERAGE(D2:D7) 로 평균을 구해 두고, 필터 조건에 그 셀을 쓰죠. SQL 에서는 그 보조 셀 자리에 (SELECT AVG(amount) FROM shop.orders) 를 괄호째 넣습니다. WITH 는 보조 계산을 아예 별도 시트로 만들고 시트 이름을 붙여 두는 것 에 가깝고요.
BigQuery 서브쿼리 문서는 서브쿼리를 다른 쿼리 문 안에 들어간 쿼리로 정의하고, 서브쿼리 안에서도 SELECT 문법을 모두 쓸 수 있다고 설명합니다. 괄호 안에 GROUP BY 든 JOIN 이든 지금까지 배운 것을 그대로 넣을 수 있다는 뜻인데요, 읽을 때는 안쪽 괄호부터 풀면 됩니다. 안쪽 쿼리가 무엇을 돌려주는지 먼저 확인하고, 그 결과를 바깥 쿼리가 어떻게 쓰는지 보는 순서입니다.
개념 그림: 안쪽 쿼리가 먼저 값 하나를 만들고, 바깥 쿼리가 그 값을 조건에 씁니다
따라 해 보기: SQL 서브쿼리를 WHERE·FROM 에 넣기
지난 글의 회원 표와 주문 표를 그대로 씁니다(가상 데이터). 106번은 회원 가입 없이 산 주문이라 member_id 가 NULL 입니다.
members (회원)
| member_id | name | city |
|---|---|---|
| 1 | 김하늘 | 서울 |
| 2 | 이도윤 | 부산 |
| 3 | 박서연 | 서울 |
| 4 | 최지우 | 대전 |
orders (주문)
| order_id | member_id | ordered_on | amount |
|---|---|---|---|
| 101 | 1 | 2026-10-01 | 7000 |
| 102 | 2 | 2026-10-03 | 4500 |
| 103 | 1 | 2026-10-07 | 2000 |
| 104 | 3 | 2026-10-12 | 6000 |
| 105 | 1 | 2026-10-20 | 1500 |
| 106 | NULL | 2026-10-21 | 3000 |
1. WHERE 에 값 하나: 평균보다 큰 주문 고르기: 9회 글에서 본 대로 WHERE 에는 AVG(amount) 같은 집계 함수를 바로 쓸 수 없는데요, 평균을 괄호 안에서 따로 구하면 됩니다.
SELECT order_id, member_id, amount
FROM shop.orders
WHERE amount > (SELECT AVG(amount) FROM shop.orders)
ORDER BY order_id;| order_id | member_id | amount |
|---|---|---|
| 101 | 1 | 7000 |
| 102 | 2 | 4500 |
| 104 | 3 | 6000 |
안쪽 쿼리는 여섯 주문의 평균 24,000 ÷ 6 = 4000.0 하나를 돌려줍니다(AVG 결과는 FLOAT64 라 소수점이 붙습니다). 이렇게 값 하나 를 돌려주는 서브쿼리를 스칼라 서브쿼리라고 하는데요, 서브쿼리 문서에 따르면 스칼라 서브쿼리는 열을 하나만 골라야 하고, 결과가 0행이면 NULL, 2행 이상이면 실행 중 오류 로 쿼리가 실패합니다. 평균은 늘 한 줄이니 안심하고 쓸 수 있죠.
2. WHERE 에 값 목록: 2,000원 이상 주문한 회원 찾기: 안쪽 쿼리가 여러 줄을 돌려준다면 > 대신 IN 으로 받습니다.
SELECT member_id, name
FROM shop.members
WHERE member_id IN (
SELECT member_id
FROM shop.orders
WHERE amount >= 2000
)
ORDER BY member_id;| member_id | name |
|---|---|
| 1 | 김하늘 |
| 2 | 이도윤 |
| 3 | 박서연 |
안쪽 쿼리는 101·102·103·104·106번 주문의 회원번호 1, 2, 1, 3, NULL 다섯 개를 돌려줍니다. 김하늘은 2,000원 이상 주문이 두 건(101·103번)이지만 결과에는 한 번만 나오는데요, WHERE 는 회원 표의 행을 남길지 말지만 정할 뿐 행을 늘리지 않기 때문입니다. 지난 글처럼 주문 표와 JOIN 했다면 김하늘이 두 줄이 됐을 거예요. 최지우는 4가 목록에 없어 빠집니다.
3. FROM 에 표: 회원 1인당 평균 매출 구하기: 「주문 1건의 평균」이 아니라 「회원 한 명이 쓴 금액의 평균」을 알고 싶다면, 회원별 합계를 먼저 내고 그 합계들의 평균을 내야 합니다. 첫 단계 결과를 표처럼 FROM 에 넣습니다.
SELECT
COUNT(*) AS members,
AVG(total) AS avg_total
FROM (
SELECT member_id, SUM(amount) AS total
FROM shop.orders
WHERE member_id IS NOT NULL
GROUP BY member_id
) AS member_totals;| members | avg_total |
|---|---|
| 3 | 7000.0 |
안쪽 쿼리의 결과는 김하늘 10,500원(7,000 + 2,000 + 1,500), 이도윤 4,500원, 박서연 6,000원 세 줄짜리 표이고, 바깥 쿼리는 이 표를 member_totals 라는 이름으로 받아 (10,500 + 4,500 + 6,000) ÷ 3 = 7000.0 을 냅니다. 비회원 주문은 회원 1인당 계산에 넣을 대상이 아니라 WHERE member_id IS NOT NULL 로 미리 뺐습니다. 서브쿼리 문서는 FROM 안의 서브쿼리를 테이블 서브쿼리 라고 부르고, 바깥 쿼리가 그 결과를 표처럼 다룬다고 설명합니다.
개념 그림: 회원별로 먼저 더한 표를 다시 평균 내면 「주문 1건 평균 4000.0」과 다른 「회원 1인 평균 7000.0」이 나옵니다
4. 같은 계산을 WITH 로: 회원마다 평균 이상인지 표시하기: 3번의 안쪽 쿼리를 맨 위로 꺼내 이름을 붙이면 WITH 가 됩니다. 이번에는 이름까지 붙이고, 8회 글의 CASE WHEN 으로 평균 이상인지 표시해 보겠습니다.
WITH member_totals AS (
SELECT member_id, COUNT(*) AS orders, SUM(amount) AS total
FROM shop.orders
WHERE member_id IS NOT NULL
GROUP BY member_id
)
SELECT m.name, t.orders, t.total,
CASE WHEN t.total >= (SELECT AVG(total) FROM member_totals)
THEN '평균 이상' ELSE '평균 미만' END AS grade
FROM member_totals AS t
INNER JOIN shop.members AS m
ON t.member_id = m.member_id
ORDER BY t.total DESC;| name | orders | total | grade |
|---|---|---|---|
| 김하늘 | 3 | 10500 | 평균 이상 |
| 박서연 | 1 | 6000 | 평균 미만 |
| 이도윤 | 1 | 4500 | 평균 미만 |
member_totals 는 3번에서 본 세 줄짜리 표이고, 아래쪽 본 쿼리는 그 이름을 실제 표처럼 두 번 씁니다. 한 번은 FROM 에서 회원 표와 JOIN 할 때, 또 한 번은 CASE 안의 스칼라 서브쿼리에서 평균 7000.0 을 구할 때죠. BigQuery 쿼리 문법 문서는 CTE(Common Table Expression, 공통 테이블 식)를 한 쿼리 안에서만 쓰는 임시 표처럼 동작 하는 것으로 설명합니다. 위 결과 표의 값은 모두 예시 표가 작아 손으로 세어 맞춘 값입니다.
서브쿼리와 WITH, 무엇이 다를까요?
세 가지 모두 「계산을 한 번 더 한다」는 점은 같고, 어디에 넣느냐와 이름이 있느냐 가 다릅니다. 위 쿼리를 기준으로 나란히 놓아 보겠습니다.
| 기준 | WHERE 안 서브쿼리 | FROM 안 서브쿼리 | WITH(CTE) |
|---|---|---|---|
| 돌려주는 것 | 값 하나(1번) 또는 값 목록(2번) | 표 | 표 |
| 이름 | 없음 | 괄호 뒤 별칭 | 맨 위에 붙인 이름 |
| 읽는 순서 | 안쪽 괄호부터 | 안쪽 괄호부터 | 위에서 아래로 |
| 한 쿼리에서 다시 쓰기 | 같은 괄호를 또 적어야 함 | 같은 괄호를 또 적어야 함 | 이름만 다시 쓰면 됨 |
| 잘 맞는 질문 | 평균보다 큰 행, 목록에 있는 행 | 두 단계 집계 한 번 | 단계가 셋 이상이거나 같은 중간 표를 여러 번 쓸 때 |
어느 쪽을 쓸지는 「괄호를 걷어 냈을 때 무엇을 계산하는 쿼리인지 한눈에 보이나?」로 정하시면 됩니다. 1번처럼 평균 하나를 끼워 넣는 정도라면 WHERE 안 서브쿼리가 오히려 짧고 읽기 쉽죠. 반면 괄호 안에 괄호가 또 들어가기 시작하면 WITH 로 꺼내는 편이 낫습니다. PostgreSQL WITH 문서도 WITH 의 기본 가치를 복잡한 쿼리를 더 단순한 부분으로 나누는 것 이라고 설명하고, 같은 예시를 WITH 없이 쓰면 하위 SELECT 를 두 겹으로 중첩해야 해서 따라가기 더 어렵다고 덧붙입니다.
한 가지 짚어 둘 점은 WITH 가 속도를 위한 도구는 아니라는 것입니다. 쿼리 문법 문서에 따르면 BigQuery 는 재귀가 아닌 CTE 의 결과를 따로 저장해 두지 않아서, 4번처럼 member_totals 를 두 곳에서 부르면 부를 때마다 다시 실행 하고, 그래서 이런 WITH 는 주로 읽기 쉽게 하는 데 쓸모가 있다고 적고 있습니다. 다른 DBMS 에서는 다를 수 있는데요, PostgreSQL 은 WITH 쿼리를 보통 부모 쿼리 한 번 실행에 한 번만 계산한다고 설명합니다.
실무에서는 어떻게 쓸까요? 도시별 1인당 매출 쿼리
월간 보고에 「도시별 구매 회원 수와 매출, 그리고 구매 회원 1인당 매출」을 넣는 담당자라고 가정해 볼게요. 회원별 합계를 내고, 거기에 도시를 붙이고, 도시로 다시 묶어야 하니 세 단계짜리 질문입니다.
- 단계를 말로 먼저 적습니다: ① 회원별 매출 합계 ② 회원에 도시 붙이기 ③ 도시별로 묶어 세고 평균 내기.
- 단계마다 CTE 하나를 만듭니다: 이름은 결과 표가 무엇인지 알 수 있게
member_totals,buyer_city처럼 짓습니다. - 중간 표를 하나씩 확인합니다: 맨 아래 본 쿼리를 잠시
SELECT * FROM member_totals로 바꿔 세 줄이 맞는지 보고, 다음 단계로 넘어갑니다. - 검산합니다: 도시별 매출을 더한 값에 비회원 주문 3,000원을 더해 주문 표 합계 24,000원과 같은지 봅니다.
WITH member_totals AS (
SELECT member_id, SUM(amount) AS total
FROM shop.orders
WHERE member_id IS NOT NULL
GROUP BY member_id
),
buyer_city AS (
SELECT m.city, t.total
FROM member_totals AS t
INNER JOIN shop.members AS m ON t.member_id = m.member_id
)
SELECT city, COUNT(*) AS buyers, SUM(total) AS revenue, AVG(total) AS per_buyer
FROM buyer_city
GROUP BY city
ORDER BY revenue DESC;| city | buyers | revenue | per_buyer |
|---|---|---|---|
| 서울 | 2 | 16500 | 8250.0 |
| 부산 | 1 | 4500 | 4500.0 |
서울은 김하늘 10,500원과 박서연 6,000원을 더해 16,500원이고 1인당 16,500 ÷ 2 = 8,250원입니다. 두 도시를 더한 21,000원에 비회원 3,000원을 더하면 24,000원이라 검산이 맞고, 대전은 최지우가 주문하지 않아 나오지 않습니다. WITH 안의 CTE 는 쉼표로 이어 여러 개를 둘 수 있는데요, buyer_city 가 위에 있는 member_totals 를 가져다 쓰는 것처럼 앞에서 만든 표를 뒤에서 이어받는 식으로 쓰면 위에서 아래로 한 단계씩 읽힙니다.
연습 문제
1. (쉬움) 금액이 가장 큰 주문의 주문 번호와 금액을 서브쿼리로 구해 보세요.
정답 보기
SELECT order_id, amount
FROM shop.orders
WHERE amount = (SELECT MAX(amount) FROM shop.orders);안쪽 쿼리가 최댓값 7000 하나를 돌려주니 101번, 7000 이 나옵니다. 최댓값이 같은 주문이 여러 건이면 모두 나온다는 점이 ORDER BY amount DESC LIMIT 1 과 다릅니다.
2. (보통) 평균 금액보다 큰 주문이 몇 건이고, 그 금액을 모두 더하면 얼마인지 구해 보세요.
정답 보기
SELECT COUNT(*) AS orders, SUM(amount) AS revenue
FROM shop.orders
WHERE amount > (SELECT AVG(amount) FROM shop.orders);평균 4000.0 보다 큰 101·102·104번이 남아 3건, 17,500원(7,000 + 4,500 + 6,000)입니다. 1번 쿼리의 결과 세 줄을 집계 함수로 한 줄로 줄인 셈이죠.
3. (보통) WITH 로 회원별 주문 건수를 먼저 구한 뒤, 주문이 2건 이상인 회원의 이름을 찾아보세요.
정답 보기
WITH member_orders AS (
SELECT member_id, COUNT(*) AS orders
FROM shop.orders
WHERE member_id IS NOT NULL
GROUP BY member_id
)
SELECT m.name, t.orders
FROM member_orders AS t
INNER JOIN shop.members AS m
ON t.member_id = m.member_id
WHERE t.orders >= 2;김하늘, 3건 한 줄이 나옵니다. 이도윤과 박서연은 1건씩입니다. 9회의 HAVING 으로도 풀 수 있는데요, WITH 로 나누면 건수를 센 표에 이름 붙이기와 거르기를 따로 읽을 수 있습니다.
자주 하는 실수
- NOT IN 목록에 NULL 이 섞입니다: 「한 번도 주문하지 않은 회원」을 찾으려고
WHERE member_id NOT IN (SELECT member_id FROM shop.orders)를 쓰면 최지우가 나올 것 같지만 결과가 0줄 입니다. 목록에 106번 주문의 NULL 이 들어 있기 때문인데요, 연산자 문서의 IN 규칙에 따르면 같은 값이 없고 NULL 과의 비교가 NULL 이 되면 NOT IN 은 TRUE 가 아니라 NULL 을 돌려주고, WHERE 는 TRUE 인 행만 남깁니다. 안쪽 쿼리에WHERE member_id IS NOT NULL을 붙이거나, 지난 글의 LEFT JOIN 뒤IS NULL로 찾는 방법을 쓰세요. - 스칼라 서브쿼리가 여러 줄을 돌려줍니다:
WHERE amount > (SELECT amount FROM shop.orders WHERE member_id = 1)처럼 쓰면 김하늘의 주문이 세 건이라 안쪽 결과가 세 줄이 되고, 앞에서 본 규칙대로 실행 중 오류가 납니다. 「그중 가장 큰 값보다 크다」가 뜻이라면MAX(amount)로 한 줄을 만들고, 「목록 중 하나와 같다」가 뜻이라면IN을 쓰면 됩니다. - CTE 순서를 거꾸로 씁니다: 쿼리 문법 문서에 따르면 RECURSIVE 키워드가 없는 WITH 에서 CTE 는 자기보다 뒤에 정의된 CTE 에게만 보입니다. 실무 쿼리에서
buyer_city를member_totals보다 위에 적으면buyer_city가 아직 정의되지 않은 이름을 부르게 되니, 먼저 계산할 단계를 위에 두세요.
개념 그림: NOT IN 목록에 NULL 이 하나라도 있으면 비교 결과가 TRUE 가 되지 못해 아무 행도 남지 않습니다
마치며
SQL 서브쿼리는 괄호 안에서 값 하나, 값 목록, 표를 먼저 만들어 바깥 쿼리에 넘기고, WITH 는 그 안쪽 쿼리에 이름을 붙여 위에서 아래로 읽히게 합니다. 짧은 조건 하나는 서브쿼리로, 단계가 셋 이상인 집계는 WITH 로 나누고, 중간 표를 하나씩 SELECT * 로 확인하는 습관을 들이시길 권합니다.
저도 시리즈를 따라가며 공부하고 있으니 전체 커리큘럼을 참고해 주세요.
다음 글에서는 지금까지 배운 것을 모두 엮어 가상 광고·주문 표로 채널별 비용·전환·ROAS 리포트 쿼리를 처음부터 끝까지 만들어 봅니다.
자주 묻는 질문
- 서브쿼리와 WITH(CTE) 차이는 무엇인가요?
- 둘 다 쿼리 안에서 계산을 한 번 더 하는 방법이고, 서브쿼리는 필요한 자리에 괄호째 넣는 반면 WITH 는 맨 위에 이름을 붙여 꺼내 둡니다. WITH 로 만든 표는 같은 쿼리 안에서 이름으로 여러 번 부를 수 있지만, BigQuery 는 재귀가 아닌 CTE 를 부를 때마다 다시 실행하므로 속도보다는 읽기 쉬움을 위한 도구입니다.
- NOT IN 서브쿼리 결과가 비어 있어요. 왜 그런가요?
- 안쪽 쿼리가 돌려준 목록에 NULL 이 섞여 있으면, 같은 값이 없는 행도 NOT IN 결과가 TRUE 가 아니라 NULL 이 되어 WHERE 에서 빠지기 때문입니다. 안쪽 쿼리에 WHERE 열 IS NOT NULL 을 붙이면 됩니다.
- FROM 안의 서브쿼리에 별칭을 꼭 붙여야 하나요?
- BigQuery 문법에서는 별칭이 선택 사항입니다. 다만 PostgreSQL 문서는 SQL 표준이 서브쿼리에 별칭을 요구하고, 다른 시스템으로 옮길 수 있는 쿼리라면 별칭을 붙이는 것이 좋은 습관이라고 설명합니다.
관련 글 더 보기
- SQL JOIN 으로 표 합치기: INNER JOIN 과 LEFT JOIN 차이 - SQL 기초 시리즈 10회, WITH 안에서도 쓰는 JOIN
- SQL GROUP BY 와 집계 함수, HAVING 으로 채널별 합계표 만들기 - SQL 기초 시리즈 9회, 서브쿼리 안에 넣는 집계
- SQL ORDER BY 정렬과 CASE WHEN 으로 금액대 나누기 - SQL 기초 시리즈 8회, 평균 이상·미만 표시에 쓴 CASE WHEN