SQL 서브쿼리와 집합 연산

게임 운영자는 평균보다 강한 플레이어, 아직 접속하지 않은 계정, 두 시즌에 모두 참가한 이용자를 자주 찾는다. 모두 ‘한 질의의 결과를 다른 질의와 비교한다’는 공통 구조를 가진다. 서브쿼리는 값을 질의 안에 공급하고, 집합 연산은 두 결과 집합 자체를 결합한다.

한 줄 정의

서브쿼리는 다른 SQL 문장 안에서 값이나 행의 존재 여부를 계산하는 질의이고, UNION·INTERSECT·EXCEPT는 호환되는 두 SELECT 결과를 합집합·교집합·차집합으로 계산한다.

실습용 시즌 데이터

DROP SCHEMA IF EXISTS subquery_demo CASCADE;
CREATE SCHEMA subquery_demo;
SET search_path TO subquery_demo;

CREATE TABLE players (
    player_id bigint PRIMARY KEY,
    nickname text NOT NULL UNIQUE
);

CREATE TABLE season_scores (
    season_code text NOT NULL,
    player_id bigint NOT NULL REFERENCES players(player_id),
    rating integer NOT NULL CHECK (rating >= 0),
    PRIMARY KEY (season_code, player_id)
);

CREATE TABLE login_events (
    event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    player_id bigint REFERENCES players(player_id),
    logged_at timestamptz NOT NULL
);

CREATE TABLE season_a_participants (player_id bigint NOT NULL REFERENCES players);
CREATE TABLE season_b_participants (player_id bigint NOT NULL REFERENCES players);

INSERT INTO players VALUES
(1, 'Nova'), (2, 'Mira'), (3, 'SoloFox'), (4, 'Luna'), (5, 'Echo');

INSERT INTO season_scores VALUES
('S1', 1, 1200), ('S1', 2, 900), ('S1', 3, 1500), ('S1', 4, 600),
('S2', 1, 1300), ('S2', 2, 1400), ('S2', 5, 1000);

INSERT INTO login_events (player_id, logged_at) VALUES
(1, '2026-08-01 10:00+09'),
(2, '2026-08-02 11:00+09'),
(4, '2026-08-03 12:00+09'),
(NULL, '2026-08-04 13:00+09'); -- 소유자를 잃은 레거시 이벤트

INSERT INTO season_a_participants VALUES (1), (2), (3);
INSERT INTO season_b_participants VALUES (2), (3), (4);

login_events의 NULL은 실무에서 마이그레이션·익명 이벤트·삭제된 계정 때문에 생길 수 있는 상태를 재현한다. 이 한 행이 NOT IN의 결과를 어떻게 바꾸는지가 핵심 실습이다.

스칼라 서브쿼리: 하나의 값을 비교 기준으로 사용

스칼라 서브쿼리는 한 행의 한 열을 반환해 하나의 값처럼 쓰인다. 0행이면 NULL이지만 두 행 이상이면 오류다. S1 평균을 한 번 계산해 각 플레이어 점수와 비교한다.

\bar r_{S1}=\frac{1200+900+1500+600}{4}=1050

SET search_path TO subquery_demo;

SELECT p.nickname, s.rating,
       (SELECT ROUND(AVG(rating), 2)
        FROM season_scores
        WHERE season_code = 'S1') AS season_average
FROM season_scores AS s
JOIN players AS p USING (player_id)
WHERE s.season_code = 'S1'
  AND s.rating > (
      SELECT AVG(rating)
      FROM season_scores
      WHERE season_code = 'S1'
  )
ORDER BY s.rating DESC;

 nickname | rating | season_average
----------+--------+---------------
 SoloFox  |   1500 |       1050.00
 Nova     |   1200 |       1050.00

상관 서브쿼리: 바깥 행에 따라 기준이 달라질 때

상관 서브쿼리는 내부 질의가 바깥 질의의 열을 참조한다. 다음 질의에서 내부의 season_code = s.season_code가 현재 바깥 점수 행의 시즌을 평균 계산 기준으로 전달한다. 의미상 각 행마다 해당 시즌 평균과 비교한다.

SET search_path TO subquery_demo;

SELECT s.season_code, p.nickname, s.rating
FROM season_scores AS s
JOIN players AS p USING (player_id)
WHERE s.rating > (
    SELECT AVG(peer.rating)
    FROM season_scores AS peer
    WHERE peer.season_code = s.season_code
)
ORDER BY s.season_code, s.rating DESC;

 season_code | nickname | rating
-------------+----------+-------
 S1          | SoloFox  |   1500
 S1          | Nova     |   1200
 S2          | Mira     |   1400
 S2          | Nova     |   1300

상관 서브쿼리가 항상 느린 것은 아니다. PostgreSQL 옵티마이저가 변환할 수도 있으므로 형태만 보고 결론 내리지 말고 EXPLAIN (ANALYZE, BUFFERS)로 실제 계획을 확인한다. 시즌별 평균을 먼저 집계한 뒤 JOIN하는 방식도 대안이다.

EXISTS와 NOT EXISTS: 값이 아니라 행의 존재를 묻기

EXISTS는 서브쿼리가 한 행이라도 반환하면 참이다. 반환 열의 값은 중요하지 않아 관례적으로 SELECT 1을 쓴다. 같은 플레이어에게 로그인 이벤트가 여러 개여도 바깥 플레이어는 한 번만 결과에 나온다.

SET search_path TO subquery_demo;

SELECT p.nickname
FROM players AS p
WHERE EXISTS (
    SELECT 1
    FROM login_events AS l
    WHERE l.player_id = p.player_id
)
ORDER BY p.player_id;

SELECT p.nickname AS never_logged_in
FROM players AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM login_events AS l
    WHERE l.player_id = p.player_id
)
ORDER BY p.player_id;

 nickname
----------
 Nova
 Mira
 Luna

 never_logged_in
-----------------
 SoloFox
 Echo

IN과 NOT IN의 NULL 함정

IN은 왼쪽 값이 서브쿼리 결과 중 하나와 같은지 비교하므로 짧은 포함 검사에 편하다. 하지만 NOT IN의 오른쪽 결과에 NULL이 하나라도 있고 일치하는 값이 없으면 비교 결과는 TRUE가 아니라 UNKNOWN(NULL)이 된다. WHERE는 TRUE인 행만 남기므로 미접속 플레이어가 모두 사라진다.

x \;\mathrm{NOT\ IN}\; (1,2,\mathrm{NULL}) \equiv (x\ne1)\land(x\ne2)\land(x\ne\mathrm{NULL})

TRUE \land TRUE \land UNKNOWN = UNKNOWN

SET search_path TO subquery_demo;

-- 잘못된 결과: 서브쿼리에 NULL이 있어 0행
SELECT nickname
FROM players
WHERE player_id NOT IN (SELECT player_id FROM login_events);

-- NULL을 제거하면 의도한 결과가 나오지만 조건 누락 위험이 있다.
SELECT nickname
FROM players
WHERE player_id NOT IN (
    SELECT player_id FROM login_events WHERE player_id IS NOT NULL
)
ORDER BY player_id;

 nickname
----------
(0 rows)

 nickname
----------
 SoloFox
 Echo

미존재 여부를 표현할 때는 상관 NOT EXISTS를 우선 고려한다. NULL의 영향을 받지 않고 의도도 ‘일치하는 행이 없다’로 명확하다. NOT IN을 쓴다면 서브쿼리 열의 NOT NULL 보장을 확인한다.

집합 연산을 수식으로 이해하기

시즌 A 참가자 집합을 $A=\{1,2,3\}$ , 시즌 B를 $B=\{2,3,4\}$ 라고 두면 연산 결과는 다음과 같다.

A \cup B=\{1,2,3,4\},\quad A \cap B=\{2,3\},\quad A\setminus B=\{1\}

연산	의미와 중복 처리
UNION	양쪽에 하나라도 있는 행. DISTINCT가 기본이라 중복을 제거한다.
UNION ALL	양쪽 행을 그대로 이어 붙인다. 중복 제거가 필요 없으면 의미가 정확하고 보통 더 빠르다.
INTERSECT	양쪽 모두에 있는 행. 두 시즌 연속 참가자를 찾는다.
EXCEPT	왼쪽에는 있고 오른쪽에는 없는 행. 방향이 중요하다.

UNION과 UNION ALL: 전체 시즌 참가자

SET search_path TO subquery_demo;

-- 고유 참가자
SELECT player_id FROM season_a_participants
UNION
SELECT player_id FROM season_b_participants
ORDER BY player_id;

-- 시즌별 참가 기록을 모두 보존
SELECT player_id FROM season_a_participants
UNION ALL
SELECT player_id FROM season_b_participants
ORDER BY player_id;

-- UNION
 player_id
-----------
         1
         2
         3
         4

-- UNION ALL
 player_id
-----------
         1
         2
         2
         3
         3
         4

UNION 계열의 각 SELECT는 열 개수가 같고 대응 열의 자료형이 호환되어야 한다. 결과 전체 정렬은 마지막 ORDER BY에서 출력 열 이름이나 위치를 사용한다.

INTERSECT와 EXCEPT: 잔존과 이탈 분석

SET search_path TO subquery_demo;

-- A와 B에 모두 참가한 플레이어
SELECT p.nickname
FROM players AS p
JOIN (
    SELECT player_id FROM season_a_participants
    INTERSECT
    SELECT player_id FROM season_b_participants
) AS retained USING (player_id)
ORDER BY p.player_id;

-- A에는 참가했지만 B에는 없는 플레이어
SELECT p.nickname
FROM players AS p
JOIN (
    SELECT player_id FROM season_a_participants
    EXCEPT
    SELECT player_id FROM season_b_participants
) AS churned USING (player_id)
ORDER BY p.player_id;

 nickname
----------
 Mira
 SoloFox

 nickname
----------
 Nova

우선순위와 ALL을 놓치지 않기

INTERSECT는 UNION과 EXCEPT보다 강하게 결합하고, UNION과 EXCEPT는 왼쪽부터 평가된다. 여러 연산을 섞을 때는 의도를 괄호로 명시한다. UNION·INTERSECT·EXCEPT는 기본적으로 중복을 제거하지만 ALL을 붙이면 다중집합 연산이 된다.

count_{A\;INTERSECT\;ALL\;B}(x)=\min(count_A(x),count_B(x))

count_{A\;EXCEPT\;ALL\;B}(x)=\max(count_A(x)-count_B(x),0)

어떤 도구를 선택할까

비교 기준이 정확히 한 값이면 스칼라 서브쿼리를 쓴다. 다중 행 가능성을 집계나 제약으로 통제한다.
바깥 행마다 비교 집단이 달라지면 상관 서브쿼리를 고려하고 실행 계획을 확인한다.
일치 행의 존재·부재만 중요하면 EXISTS·NOT EXISTS로 의도를 드러낸다.
NOT IN은 오른쪽 열의 NULL 가능성을 먼저 확인한다. 확신이 없으면 NOT EXISTS가 안전하다.
같은 모양의 결과 두 개를 합집합·교집합·차집합으로 설명할 수 있으면 집합 연산을 사용한다. 중복이 의미 있으면 ALL을 명시한다.

참고 자료

PostgreSQL 18 공식 문서 — 서브쿼리 표현식(EXISTS·IN·NOT IN) (2026-08-11 확인)
PostgreSQL 18 공식 문서 — 값 표현식과 스칼라 서브쿼리 (2026-08-11 확인)
PostgreSQL 18 공식 문서 — SELECT와 UNION·INTERSECT·EXCEPT (2026-08-11 확인)
PostgreSQL 18 공식 문서 — UNION 계열 자료형 결정 (2026-08-11 확인)

댓글을 불러오는 중…