EXISTS와 상관 서브쿼리

DROP TABLE IF EXISTS ch6_exists_post_tag_demo;
DROP TABLE IF EXISTS ch6_exists_post_demo;
DROP TABLE IF EXISTS ch6_exists_tag_demo;
DROP TABLE IF EXISTS ch6_exists_comment_demo;

CREATE TABLE ch6_exists_comment_demo (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  parent_id BIGINT UNSIGNED NULL,
  content VARCHAR(200) NOT NULL,
  CONSTRAINT fk_ch6_exists_comment_parent
    FOREIGN KEY (parent_id) REFERENCES ch6_exists_comment_demo (id)
) ENGINE = InnoDB;

CREATE TABLE ch6_exists_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_exists_post_demo (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  title VARCHAR(80) NOT NULL,
  status VARCHAR(16) NOT NULL,
  created_at DATETIME(6) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE ch6_exists_post_tag_demo (
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  CONSTRAINT fk_ch6_exists_post_tag_post
    FOREIGN KEY (post_id) REFERENCES ch6_exists_post_demo (id),
  CONSTRAINT fk_ch6_exists_post_tag_tag
    FOREIGN KEY (tag_id) REFERENCES ch6_exists_tag_demo (id)
) ENGINE = InnoDB;

INSERT INTO ch6_exists_comment_demo (id, parent_id, content)
VALUES
  (1, NULL, '첫 댓글'),
  (2, 1, '첫 댓글의 답글'),
  (3, NULL, '답글 없는 댓글'),
  (4, 2, '더 이상 답글 없는 댓글');

INSERT INTO ch6_exists_tag_demo (id, code, name, is_active)
VALUES
  (1, 'database', '데이터베이스', TRUE),
  (2, 'java', 'Java', TRUE),
  (3, 'legacy', '이전 분류', FALSE),
  (4, 'empty', '게시글 없음', TRUE);

INSERT INTO ch6_exists_post_demo (id, title, status, created_at)
VALUES
  (1, 'EXISTS 기초', 'PUBLISHED', '2026-07-01 09:00:00'),
  (2, 'Java 초안', 'DRAFT', '2026-07-10 09:00:00'),
  (3, '과거 게시글', 'PUBLISHED', '2026-05-01 09:00:00');

INSERT INTO ch6_exists_post_tag_demo (post_id, tag_id)
VALUES
  (1, 1),
  (2, 2),
  (3, 3);

SELECT id, content
FROM ch6_exists_comment_demo
WHERE id NOT IN (
  SELECT parent_id
  FROM ch6_exists_comment_demo
)
ORDER BY id;

expected comments without replies | 2 rows
actual result                     | 0 rows
warning                           | no SQL error; NULL changes predicate truth

-- 어떤 답글의 부모로도 참조되지 않는 댓글
SELECT candidate.id, candidate.content
FROM ch6_exists_comment_demo AS candidate
WHERE NOT EXISTS (
  SELECT 1
  FROM ch6_exists_comment_demo AS reply
  WHERE reply.parent_id = candidate.id
)
ORDER BY candidate.id;

-- 게시된 글이 하나라도 연결된 활성 태그
SELECT t.id, t.code, t.name
FROM ch6_exists_tag_demo AS t
WHERE t.is_active = TRUE
  AND EXISTS (
    SELECT 1
    FROM ch6_exists_post_tag_demo AS pt
    JOIN ch6_exists_post_demo AS p ON p.id = pt.post_id
    WHERE pt.tag_id = t.id
      AND p.status = 'PUBLISHED'
  )
ORDER BY t.code, t.id;

result        | id | code/content
no reply      | 3  | 답글 없는 댓글
no reply      | 4  | 더 이상 답글 없는 댓글
published tag | 1  | database

WITH exists_result AS (
  SELECT t.id AS tag_id
  FROM ch6_exists_tag_demo AS t
  WHERE EXISTS (
    SELECT 1
    FROM ch6_exists_post_tag_demo AS pt
    JOIN ch6_exists_post_demo AS p ON p.id = pt.post_id
    WHERE pt.tag_id = t.id
      AND p.status = 'PUBLISHED'
  )
),
join_result AS (
  SELECT DISTINCT t.id AS tag_id
  FROM ch6_exists_tag_demo AS t
  JOIN ch6_exists_post_tag_demo AS pt ON pt.tag_id = t.id
  JOIN ch6_exists_post_demo AS p ON p.id = pt.post_id
  WHERE p.status = 'PUBLISHED'
)
SELECT
  (SELECT COUNT(*) FROM exists_result) AS exists_count,
  (SELECT COUNT(*) FROM join_result) AS join_count,
  (SELECT COUNT(*) FROM exists_result e
   LEFT JOIN join_result j USING (tag_id)
   WHERE j.tag_id IS NULL) AS only_exists;

EXPLAIN ANALYZE
SELECT t.id
FROM ch6_exists_tag_demo AS t
WHERE EXISTS (
  SELECT 1
  FROM ch6_exists_post_tag_demo AS pt
  JOIN ch6_exists_post_demo AS p ON p.id = pt.post_id
  WHERE pt.tag_id = t.id
    AND p.status = 'PUBLISHED'
);