SQL 쿼리문 — SELECT·조인·서브쿼리·집계·윈도우·CTE

SQL은 선언적 언어다 — 무엇을 원하는지만 적고, 어떻게 꺼낼지는 옵티마이저가 정한다. 그래서 SQL을 잘 쓰는 핵심은 문법 암기가 아니라 **"이 질의가 논리적으로 어떤 순서로 평가되는가"**를 그리는 것이다. 이 한 장은 실제로 손으로 쓰는 쿼리를 따라가며 그 그림을 잡는다. 예시는 모두 PostgreSQL 기준이다. (출처: PostgreSQL 17 — Queries, Silberschatz DBSC 7e Ch 3.)

SELECT의 논리적 실행 순서 — 적는 순서 ≠ 도는 순서

우리는 SELECT부터 적지만, DB는 FROM부터 평가한다. 이 순서를 외우면 "WHERE에선 왜 별칭을 못 쓰지?" 같은 의문이 한 번에 풀린다.

다이어그램 로딩 중…

SELECT(⑤)는 WHERE(②)보다 뒤에 평가된다. 그래서 SELECT price*0.9 AS sale로 만든 별칭 sale은 WHERE에서 못 쓴다(아직 계산 전). 반대로 ORDER BY(⑦)는 SELECT 뒤라 별칭을 쓸 수 있다. HAVING(④)은 그룹에 대한 필터라 집계 결과를 조건에 걸 수 있지만 WHERE(②)는 그룹 전이라 집계함수를 못 쓴다.

SELECT member_id, COUNT(*) AS cnt
FROM orders
WHERE total_amount > 0       -- ② 개별 행 필터 (집계 불가)
GROUP BY member_id           -- ③
HAVING COUNT(*) >= 5         -- ④ 그룹 필터 (집계 가능)
ORDER BY cnt DESC            -- ⑦ SELECT 별칭 cnt 사용 OK
LIMIT 10;

조인 — 표를 잇는 법

조인은 두 테이블을 키로 이어 한 결과로 만든다. 종류를 *"기준 테이블의 행을 남기느냐"*로 외우면 쉽다.

다이어그램 로딩 중…

-- 모든 회원과 그 주문 수 (주문 0건 회원도 포함하려면 LEFT)
SELECT m.id, m.email, COUNT(o.id) AS order_cnt
FROM member m
LEFT JOIN orders o ON o.member_id = m.id
GROUP BY m.id, m.email;

여기서 흔한 함정 두 가지. (1) LEFT JOIN인데 오른쪽 테이블 조건을 WHERE에 쓰면 — NULL이 걸러져 사실상 INNER JOIN으로 퇴화한다. 오른쪽 조건은 ON 절에 넣어야 외부 조인이 유지된다. (2) COUNT(*) 대신 COUNT(o.id)를 써야 매칭 없는 행(NULL)을 0으로 센다. SELF JOIN은 같은 테이블을 두 별칭으로 이어 계층(상사-부하)·연속 비교에 쓴다.

서브쿼리 — 쿼리 안의 쿼리

서브쿼리는 놓이는 위치로 나뉜다. (1) 스칼라 서브쿼리(값 하나) — SELECT 절이나 비교식에. (2) 인라인 뷰(테이블 하나) — FROM 절에. (3) 상관 서브쿼리(correlated) — 바깥 행마다 다시 평가되는, 바깥을 참조하는 서브쿼리.

-- 상관 서브쿼리: 자기 카테고리 평균보다 비싼 상품
SELECT p.name, p.price
FROM product p
WHERE p.price > (
  SELECT AVG(p2.price) FROM product p2
  WHERE p2.category_id = p.category_id   -- 바깥 p 참조 → 행마다 재평가
);

IN vs EXISTS는 자주 비교된다. EXISTS는 첫 매칭에서 멈추고(존재 여부만), 상관 서브쿼리와 잘 맞는다. NOT IN은 서브쿼리 결과에 NULL이 하나라도 있으면 전체가 UNKNOWN이 되어 0행이라는 치명적 함정이 있다 — "없는 것"을 찾을 땐 NOT EXISTS가 안전하다. (다만 현대 옵티마이저는 IN/EXISTS를 흔히 같은 세미조인으로 변환하므로 성능은 대개 비슷하고, 차이는 NULL 의미론에서 갈린다.)

-- 주문이 한 번도 없는 회원 — NOT EXISTS 권장
SELECT m.email FROM member m
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.member_id = m.id);

집계 — GROUP BY와 집계 함수

GROUP BY는 행을 그룹으로 접고, 집계 함수(COUNT·SUM·AVG·MIN·MAX)는 각 그룹을 값 하나로 줄인다. 규칙 하나 — SELECT에 온 비집계 열은 모두 GROUP BY에 있어야 한다(PostgreSQL은 PK로 묶이면 예외 허용).

SELECT category_id,
       COUNT(*)            AS cnt,
       AVG(price)::numeric(10,2) AS avg_price,
       MAX(price)          AS top_price
FROM product
GROUP BY category_id
HAVING COUNT(*) >= 3;       -- 상품 3개 이상인 카테고리만

FILTER 절을 쓰면 조건부 집계를 깔끔하게 쓴다(PostgreSQL): COUNT(*) FILTER (WHERE status = 'paid'). GROUPING SETS·ROLLUP·CUBE는 소계·총계를 한 번에 뽑는다.

윈도우 함수 — 그룹으로 접지 않고 "옆을 본다"

집계가 행을 줄인다면, 윈도우 함수는 행은 그대로 두고 각 행 옆에 그룹 계산 결과를 붙인다. 순위·누적합·이전 행 비교 — SQL을 한 단계 끌어올리는 도구다.

SELECT
  member_id, created_at, total_amount,
  ROW_NUMBER() OVER w        AS seq,        -- 회원별 주문 순번
  SUM(total_amount) OVER w   AS running_sum,-- 누적 결제액
  LAG(total_amount) OVER w   AS prev_amount,-- 직전 주문액
  RANK() OVER (PARTITION BY member_id ORDER BY total_amount DESC) AS amount_rank
FROM orders
WINDOW w AS (PARTITION BY member_id ORDER BY created_at);

PARTITION BY는 그룹을 나누되 행은 유지(없으면 전체가 한 파티션), ORDER BY는 파티션 안 순서.
ROW_NUMBER(1,2,3,4)·RANK(1,2,2,4 — 공동순위 후 건너뜀)·DENSE_RANK(1,2,2,3)의 차이가 면접 단골.
LAG/LEAD로 전/다음 행을, SUM(...) OVER로 누적/이동 합을 구한다.

"회원별 가장 최근 주문 1건" 같은 Top-N per group은 윈도우의 대표 패턴이다.

SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t WHERE rn = 1;

CTE(WITH) — 쿼리를 이름 붙여 조립

복잡한 서브쿼리를 위에서 이름 붙여 읽기 쉽게 만드는 게 **CTE(Common Table Expression)**다. 재귀 CTE는 계층(조직도·카테고리 트리)·그래프 탐색을 SQL로 푼다.

WITH paid AS (                       -- ① 결제 완료 주문만
  SELECT member_id, total_amount FROM orders WHERE status = 'paid'
), per_member AS (                   -- ② 회원별 합
  SELECT member_id, SUM(total_amount) AS spent FROM paid GROUP BY member_id
)
SELECT m.email, pm.spent
FROM per_member pm JOIN member m ON m.id = pm.member_id
WHERE pm.spent > 100000;

-- 재귀 CTE: 카테고리 트리 펼치기
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth FROM category WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM category c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth;

참고 — 옛 PostgreSQL은 CTE를 *항상 별도 실행(최적화 울타리)*했지만, PostgreSQL 12+부터는 부작용 없는 단순 CTE를 바깥 쿼리로 인라인해 최적화한다. 울타리가 필요하면 WITH ... AS MATERIALIZED로 강제한다. (출처: PostgreSQL — WITH Queries.)

DML과 UPSERT — 데이터를 바꾸는 문장

INSERT·UPDATE·DELETE가 데이터를 바꾸는 문장이다. PostgreSQL의 강력한 무기가 INSERT ... ON CONFLICT(UPSERT) — 있으면 갱신, 없으면 삽입을 원자적으로 한다.

INSERT INTO inventory (product_id, qty) VALUES (42, 10)
ON CONFLICT (product_id)
DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;   -- 충돌 시 수량 누적

UPDATE ... FROM·DELETE ... USING으로 다른 테이블과 조인해 갱신/삭제하고, RETURNING 절로 바뀐 행을 그 자리에서 돌려받는다(별도 SELECT 불필요).

UPDATE orders o SET status = 'shipped'
FROM shipment s WHERE s.order_id = o.id AND s.shipped_at IS NOT NULL
RETURNING o.id, o.status;

정리 — 손으로 쓰며 그림을 그려라

SQL은 *실행 순서(FROM→…→LIMIT)*라는 한 장의 그림 위에 조인·서브쿼리·집계·윈도우·CTE를 얹는 일이다. 같은 결과도 상관 서브쿼리 / 조인 / 윈도우로 여러 갈래 적을 수 있고, 무엇이 빠른지는 실행계획이 답한다([07 쿼리 최적화]에서 다룬다). 그래서 다음 순서로 익히면 빠르다 — (1) 실행 순서 → (2) 조인 종류 → (3) 서브쿼리 NULL 함정 → (4) 윈도우 Top-N → (5) CTE 조립.

한 줄 요약 — WHERE는 행, HAVING은 그룹, 윈도우는 접지 않은 옆 계산, CTE는 이름 붙인 조립. NOT IN엔 NULL 함정, LEFT JOIN 조건은 ON에.

(출처: PostgreSQL 17 Documentation — Queries / Window Functions / WITH / Silberschatz DBSC 7e Ch 3·5 / 김연희 데이터베이스 개론 6·7장.)