서브쿼리 연산자

서브쿼리는 SQL 안에 SELECT를 하나 더 넣는 문법이지만, 핵심은 괄호가 아니라 안쪽 쿼리가 돌려주는 결과의 모양입니다. 값 하나인지, 한 열의 목록인지, 여러 열로 된 행의 목록인지에 따라 바깥에 쓸 연산자가 달라집니다. 이 규칙을 확인하지 않으면, 표본 데이터에서는 우연히 한 행이어서 성공하다가 데이터가 늘어나는 순간 오류가 납니다.

이 글에서는 공개 글의 평균보다 조회수가 높은 글, 활성 태그가 붙은 글, 회원마다 가장 먼저 작성한 글을 차례로 찾습니다. 세 질문은 모두 "먼저 값을 구한 뒤 그 값을 쓴다"는 구조지만, 안쪽 결과의 행과 열의 수가 서로 다릅니다.

서브쿼리 예제 데이터

앞 장을 실행했는지나 누적된 테이블의 행 수에 기대지 않도록, 이 글에서 쓸 게시글과 태그를 고정합니다. 공개 글의 평균 조회수는 40, 100, 60, 80을 더해 4로 나눈 70이고, 활성 태그는 database와 java 두 개입니다. 조회수가 200인 3번은 초안이어서 평균의 모집단에 들어가지 않습니다.

DROP TABLE IF EXISTS ch6_subquery_post_tag_demo;
DROP TABLE IF EXISTS ch6_subquery_tag_demo;
DROP TABLE IF EXISTS ch6_subquery_post_demo;

CREATE TABLE ch6_subquery_post_demo (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  author_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(80) NOT NULL,
  status VARCHAR(16) NOT NULL,
  view_count BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(6) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE ch6_subquery_tag_demo (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL,
  is_active BOOLEAN NOT NULL
) ENGINE = InnoDB;

CREATE TABLE ch6_subquery_post_tag_demo (
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  CONSTRAINT fk_ch6_subquery_post_tag_post
    FOREIGN KEY (post_id) REFERENCES ch6_subquery_post_demo (id),
  CONSTRAINT fk_ch6_subquery_post_tag_tag
    FOREIGN KEY (tag_id) REFERENCES ch6_subquery_tag_demo (id)
) ENGINE = InnoDB;

INSERT INTO ch6_subquery_post_demo
  (id, author_id, title, status, view_count, created_at)
VALUES
  (1, 1, 'SELECT 기초', 'PUBLISHED', 40, '2026-07-01 09:00:00'),
  (2, 1, 'JOIN 실습', 'PUBLISHED', 100, '2026-07-02 09:00:00'),
  (3, 2, '초안 작성', 'DRAFT', 200, '2026-07-01 10:00:00'),
  (4, 2, 'EXISTS 복습', 'PUBLISHED', 60, '2026-07-03 09:00:00'),
  (5, 3, 'VIEW 정리', 'PUBLISHED', 80, '2026-07-01 11:00:00');

INSERT INTO ch6_subquery_tag_demo (id, code, name, is_active)
VALUES
  (1, 'database', '데이터베이스', TRUE),
  (2, 'java', 'Java', TRUE),
  (3, 'legacy', '이전 분류', FALSE);

INSERT INTO ch6_subquery_post_tag_demo (post_id, tag_id)
VALUES
  (1, 1),
  (2, 1),
  (2, 2),
  (3, 3),
  (4, 3),
  (5, 2);

목록을 값 하나처럼 비교하면

공개 글의 조회수 목록을 값 하나처럼 =의 오른쪽에 두어 봅니다. 공개 글이 우연히 한 개뿐이라면 실행되지만, 두 번째 글이 들어오는 순간 값 하나가 와야 할 자리의 규칙을 어기게 됩니다.

SELECT id, title, view_count
FROM ch6_subquery_post_demo
WHERE view_count = (
  SELECT view_count
  FROM ch6_subquery_post_demo
  WHERE status = 'PUBLISHED'
);

ERROR 1242 (21000): Subquery returns more than 1 row

바깥의 =는 오른쪽에 값 하나를 요구하는데, 안쪽 쿼리는 열은 하나지만 공개 글마다 한 행씩 네 행을 돌려줍니다. 행의 수를 하나로 줄이는 집계나 유일한 조건이 없기 때문입니다. 이 오류는 데이터의 양이 아니라 결과의 행 수, 곧 카디널리티(cardinality)의 규칙 문제입니다.

LIMIT 1을 붙여 오류만 감추면 어느 행을 고르는지 정해지지 않아 더 위험합니다. 그 대신 질문이 평균이라는 값 하나인지, 허용하는 값의 목록인지, 특정한 행 하나인지부터 다시 말해야 합니다.

서브쿼리 결과의 세 가지 모양

GROUP BY가 없는 AVG(view_count)처럼 한 행과 한 열을 보장하는 스칼라 서브쿼리(scalar subquery)는 =, >, <= 같은 값 하나를 비교하는 연산자와 함께 씁니다. 한 열의 여러 행에는 IN, ANY, ALL처럼 집합을 받는 연산자가 필요합니다. 두 열 이상의 행 목록은 (a, b) IN (SELECT x, y ...)처럼 열의 수가 같고 타입이 맞는 행 생성자로 비교하며, 이때 바깥의 두 열과 안쪽의 두 열은 순서와 뜻이 같아야 합니다.

스칼라 서브쿼리와 IN, EXISTS, 행 값의 비교는 표준 SQL의 개념입니다. MySQL 8.4는 = ANY와 IN을 모두 지원하므로 팀에서 익숙한 쪽을 정해 씁니다. 여러 열의 IN은 편리하지만, 인덱스를 쓰는지와 NULL이 섞였을 때의 뜻은 실행 계획과 데이터로 확인합니다.

모양에 맞는 연산자를 고른다

전체 평균은 집계로 값 하나를, 활성 태그는 id의 목록을, 회원별 최초 게시글은 (author_id, created_at) 쌍의 목록을 만듭니다. 바깥의 연산자가 받는 모양이 SQL에서 보이도록 씁니다.

-- 한 값: 공개 글의 평균 조회수보다 높은 게시글
SELECT id, title, view_count
FROM ch6_subquery_post_demo
WHERE status = 'PUBLISHED'
  AND view_count > (
    SELECT AVG(view_count)
    FROM ch6_subquery_post_demo
    WHERE status = 'PUBLISHED'
  )
ORDER BY view_count DESC, id;

-- 한 열 목록: 활성 태그가 붙은 게시글
SELECT id, title
FROM ch6_subquery_post_demo
WHERE id IN (
  SELECT pt.post_id
  FROM ch6_subquery_post_tag_demo AS pt
  WHERE pt.tag_id IN (
    SELECT id FROM ch6_subquery_tag_demo WHERE is_active = TRUE
  )
)
ORDER BY id;

-- 두 열 행 목록: 회원마다 가장 먼저 작성한 게시글
SELECT id, author_id, title, created_at
FROM ch6_subquery_post_demo
WHERE (author_id, created_at) IN (
  SELECT author_id, MIN(created_at)
  FROM ch6_subquery_post_demo
  GROUP BY author_id
)
ORDER BY author_id, id;

id | title     | view_count
2  | JOIN 실습 | 100
5  | VIEW 정리 | 80

id | title
1  | SELECT 기초
2  | JOIN 실습
5  | VIEW 정리

id | author_id | title       | created_at
1  | 1         | SELECT 기초 | 2026-07-01 09:00:00.000000
3  | 2         | 초안 작성   | 2026-07-01 10:00:00.000000
5  | 3         | VIEW 정리   | 2026-07-01 11:00:00.000000

첫 쿼리는 기준값 70 하나와 비교해 100과 80인 두 글을 찾았습니다. 둘째 쿼리는 활성 태그가 붙은 글의 id 집합 1, 2, 5와 비교했으며, 비활성인 legacy만 붙은 4번은 빠졌습니다. 셋째 쿼리는 회원과 시각의 쌍을 비교했는데, 상태를 거르지 않았으므로 회원 2의 최초 글은 초안인 3번입니다.

같은 최소 시각을 가진 글이 둘이면 둘 다 나오는 것도 셋째 쿼리의 결과 규칙에 들어갑니다. 정확히 한 행만 돌려주려면 최소 시각만으로는 부족하고, 같은 시각을 모두 허용할지 id까지 넣은 순위를 정할지 정해야 합니다. 서브쿼리의 모양을 맞춘 뒤에도 결과 한 행의 유일성은 따로 판단합니다.

안쪽 쿼리만 따로 확인한다

긴 SQL을 한 번에 디버깅하지 말고, 서브쿼리마다 따로 실행해 열의 이름과 행의 수, NULL이 들어 있는지를 확인합니다. 다음 진단 쿼리는 세 서브쿼리의 실제 행 수를 한 표로 만듭니다.

WITH
average_value AS (
  SELECT AVG(view_count) AS value
  FROM ch6_subquery_post_demo
  WHERE status = 'PUBLISHED'
),
active_tag_ids AS (
  SELECT id AS tag_id FROM ch6_subquery_tag_demo WHERE is_active = TRUE
),
first_pairs AS (
  SELECT author_id, MIN(created_at) AS created_at
  FROM ch6_subquery_post_demo
  GROUP BY author_id
)
SELECT 'scalar average' AS contract, COUNT(*) AS rows_found, 1 AS columns_found
FROM average_value
UNION ALL
SELECT 'active id list', COUNT(*), 1 FROM active_tag_ids
UNION ALL
SELECT 'members-time rows', COUNT(*), 2 FROM first_pairs;

contract          | rows_found | columns_found
scalar average    | 1          | 1
active id list    | 2          | 1
members-time rows | 3          | 2

평균은 언제나 한 행이지만, 활성 태그와 회원별 쌍은 데이터에 따라 0개 이상입니다. 예상과 다르면 바깥 쿼리보다 안쪽의 WHERE와 GROUP BY를 먼저 고칩니다. 각 안쪽 SELECT의 결과를 1×1, N×1, N×2처럼 적어 두면 바깥 연산자와 맞는지 바로 확인할 수 있습니다.

ANY와 ALL, 그리고 빈 입력

IN과 = ANY는 같다는 비교에서는 같은 뜻이지만, ANY와 ALL은 부등호와도 함께 쓸 수 있습니다. view_count > ALL (...)은 모든 값보다 커야 하고, view_count > ANY (...)는 적어도 한 값보다 크면 됩니다. 실제 의도가 최솟값이나 최댓값과의 비교라면 MIN이나 MAX가 더 읽기 쉬운지 검토하고, 연산자의 이름보다 "모든"과 "어느 하나"라는 말이 분명히 드러나는 쪽을 고릅니다.

안쪽 결과가 비어 있을 때는 세 비교가 서로 다른 결과를 냅니다. 이 테이블에는 상태가 ARCHIVED인 글이 없으므로, 그 조건의 서브쿼리는 빈 결과를 돌려줍니다.

SELECT
  80 > ALL (SELECT view_count FROM ch6_subquery_post_demo WHERE status = 'ARCHIVED') AS gt_all,
  80 > ANY (SELECT view_count FROM ch6_subquery_post_demo WHERE status = 'ARCHIVED') AS gt_any,
  80 > (SELECT AVG(view_count) FROM ch6_subquery_post_demo WHERE status = 'ARCHIVED') AS gt_avg;

gt_all | gt_any | gt_avg
1      | 0      | NULL

MySQL 8.4의 ALL 설명대로 ALL은 반례가 하나도 없으므로 참이고, ANY는 조건을 만족하는 값이 하나도 없으므로 거짓입니다. 반면 GROUP BY가 없는 AVG는 0행이 아니라 NULL 한 행을 돌려주므로 마지막 비교는 알 수 없음입니다. 그래서 공개 글이 하나도 없으면 본문의 평균 쿼리는 아무 행도 돌려주지 않으며, 그 빈 화면을 어떻게 보여 줄지도 정해 두어야 합니다.

여러 열의 비교에서 어느 한 열이 NULL이면 결과가 참이 아니라 알 수 없음이 될 수 있으므로, 후보 키처럼 NOT NULL인 열의 조합이 안전합니다. IN의 목록에 NULL이 섞이는 문제는 다음 글의 NOT IN에서 다시 다룹니다.

서브쿼리를 쓸 때 따질 것

애플리케이션이 서브쿼리의 기준값과 바깥 조회를 두 번의 요청으로 나누어 실행하면, 그사이에 데이터가 바뀔 수 있습니다. 한 SQL 문장 안의 서브쿼리들은 같은 읽기 시점을 공유하지만, 트랜잭션의 격리와 잠금은 별도의 문제입니다(8장 3편). 큰 목록을 애플리케이션으로 가져와 IN의 파라미터로 다시 보내는 방식은 네트워크와 파라미터의 크기, 시점의 차이를 함께 늘리므로, 데이터베이스 안에서 나타낼 수 있는 관계는 한 문장으로 두고 실행 계획을 측정합니다.

스칼라 집계가 NULL일 때 COALESCE로 0을 넣는 것이 언제나 맞지는 않습니다. 공개 글이 없다는 것과 평균 조회수가 0회라는 것은 다른 상태입니다. 서브쿼리의 비교에서 문자열과 숫자가 암묵적으로 변환되면 인덱스와 결과가 달라질 수 있으므로, 외래 키와 후보 키의 타입은 같게 유지합니다.

서브쿼리에 ORDER BY만 넣고 LIMIT이 없다면 집합의 뜻은 바뀌지 않으며, 아무 행이나 하나 고르려고 ORDER BY 없이 LIMIT을 쓰지 않습니다. 다음 글의 EXISTS는 안쪽 열의 값이 아니라 행이 있는지만 묻기 때문에, SELECT 목록을 1로 적어 그 의도를 드러냅니다. 같은 SQL을 MySQL이 실제로 구체화(materialization)나 세미 조인(semi-join) 가운데 어떤 방식으로 실행하는지는 아래 더 깊이 단락의 전략과 7장 3편의 실행 계획 읽기로 확인하되, 그 전에 여기서 정한 결과의 규칙이 지켜지는지부터 봅니다.

괄호 안의 SQL을 가린 채 바깥 연산자가 기대하는 모양부터 적고, 이어서 안쪽 SELECT만 실행해 실제의 모양과 맞춰 봅니다. 둘이 다르면 LIMIT으로 누르지 말고 질문이나 연산자를 고칩니다.

더 깊이: 옵티마이저가 IN 서브쿼리를 세미 조인으로 바꾸는 방법

WHERE id IN (SELECT post_id FROM ...)을 그대로 내부 조인으로 바꾸면, 안쪽에 짝이 여럿인 바깥 행이 그 수만큼 되풀이됩니다. 하지만 IN이 묻는 것은 짝이 있는지뿐이므로, MySQL은 이런 서브쿼리를 짝이 있는 바깥 행을 한 번씩만 돌려주는 세미 조인(semi-join)으로 바꿀 수 있습니다. 그러려면 서브쿼리가 WHERE나 ON의 맨 위 단계에 있는 IN, = ANY, EXISTS여야 하며, AND로 이은 항 가운데 하나여도 됩니다. 또 UNION이 없는 SELECT 하나여야 하고 HAVING과 집계 함수, LIMIT이 없어야 하며, 안쪽의 DISTINCT와 ORDER BY는 허용되지만 무시됩니다.

세미 조인을 실행하는 전략은 여러 가지입니다. 서브쿼리의 테이블을 바깥으로 끌어내 보통의 내부 조인으로 실행하는 테이블 풀아웃(table pullout), 조인처럼 실행한 뒤 임시 테이블로 중복을 걸러 내는 Duplicate Weedout, 안쪽에서 첫 짝을 찾으면 같은 값의 나머지는 건너뛰는 FirstMatch가 있습니다. 인덱스를 이용해 안쪽 값의 그룹마다 하나씩만 읽는 LooseScan과, 서브쿼리의 결과를 인덱스가 있는 임시 테이블로 만들어 조인하는 구체화도 있습니다. 어느 전략을 골랐는지는 EXPLAIN의 Extra에 나오는 Start temporary와 End temporary, FirstMatch(...), LooseScan(...)나, select_type이 MATERIALIZED인 행으로 알 수 있습니다.

이 전략들은 optimizer_switch의 semijoin, firstmatch, loosescan, duplicateweedout, materialization 플래그로 켜고 끄며, 모두 기본으로 켜져 있습니다(MySQL 8.4의 세미 조인). 어느 전략으로 실행되든 결과는 IN의 뜻과 같으므로, 이 글에서 정한 행 수와 빈 입력의 규칙은 실행 방식과 상관없이 그대로 지켜집니다.

정리

• 서브쿼리는 문법의 위치보다 돌려주는 행과 열의 수를 먼저 봅니다.
• 값 하나는 단일 비교로, 한 열의 목록은 IN과 ANY, ALL로, 여러 열은 같은 모양의 행 비교로 다룹니다.
• 카디널리티의 오류를 LIMIT으로 감추지 말고, 유일성과 같은 값의 규칙을 정합니다.
• 빈 입력에서 ALL은 참, ANY는 거짓, 스칼라 집계와의 비교는 알 수 없음이 됩니다.

연습 문제

1. 평균보다 높고 활성 태그가 붙은 글

공개 글의 전체 평균보다 조회수가 높고, 활성 태그가 붙은 게시글만 조회해 보세요. id와 title, view_count를 보여 주고 조회수와 큰 id의 순서로 정렬합니다. 공개 글이 없을 때 평균을 0으로 바꾸지 않습니다.

SELECT id, title, view_count
FROM ch6_subquery_post_demo
WHERE status = 'PUBLISHED'
  AND view_count > (
    SELECT AVG(view_count)
    FROM ch6_subquery_post_demo
    WHERE status = 'PUBLISHED'
  )
  AND id IN (
    SELECT pt.post_id
    FROM ch6_subquery_post_tag_demo AS pt
    JOIN ch6_subquery_tag_demo AS t ON t.id = pt.tag_id
    WHERE t.is_active = TRUE
  )
ORDER BY view_count DESC, id DESC;

id | title     | view_count
2  | JOIN 실습 | 100
5  | VIEW 정리 | 80

2. 모든 값보다 큰 글과 어느 값보다 큰 글

공개 글의 조회수 목록과 비교해 view_count > ALL (...)과 view_count > ANY (...)를 만족하는 게시글을 각각 찾아 보세요. 두 조건은 각각 어떤 집계와 같은 뜻인가요?

SELECT id FROM ch6_subquery_post_demo
WHERE view_count > ALL (SELECT view_count FROM ch6_subquery_post_demo WHERE status = 'PUBLISHED')
ORDER BY id;

SELECT id FROM ch6_subquery_post_demo
WHERE view_count > ANY (SELECT view_count FROM ch6_subquery_post_demo WHERE status = 'PUBLISHED')
ORDER BY id;

id
3

id
2
3
4
5

3. IN과 JOIN의 행 수

활성 태그가 붙은 게시글을 본문의 IN 대신 게시글, 연결 테이블, 태그의 JOIN으로 찾으면 결과는 몇 행이 되나요? 왜 다른가요?

SELECT p.id
FROM ch6_subquery_post_demo AS p
JOIN ch6_subquery_post_tag_demo AS pt ON pt.post_id = p.id
JOIN ch6_subquery_tag_demo AS t ON t.id = pt.tag_id
WHERE t.is_active = TRUE
ORDER BY p.id;

id
1
2
2
5

다음 글 EXISTS와 상관 서브쿼리에서는 바깥 행마다 관계가 있는지를 묻는 상관 서브쿼리와 EXISTS를 다룹니다.