SQL: EXISTS / NOT EXISTS

※ SQL: EXISTS / NOT EXISTS.
 
안녕하세요. 듀스트림입니다.
 
오늘 포스팅은 predicate 구문인 EXISTS / NOT EXISTS에 대한 내용입니다.

Predicate 구문?
• SQL에서 TRUE / FALSE / UNKNOWN 중 하나로 평가되는 논리식(logical expression)을 뜻합니다.
• WHERE, HAVING, ON 절 안에서 사용되어 어떤 행을 선택(필터링) 할지를 결정합니다.

※ ON절은 문법적으로 가능은 하지만 의미적으로 맞지 않아 실제로 사용하지는 않습니다.

---

1. EXISTS / NOT EXISTS

EXISTS / NOT EXISTS는 "조건이 참인가?"를 판별하는 불리언 평가식(Boolean predicate)이고 데이터를 반환하지 않습니다.
 

• EXISTS: 서브쿼리의 결과 집합이 하나라도 존재하면 TRUE, 비어 있으면 FALSE를 반환합니다.
  • 서브쿼리의 컬럼 목록은 의미가 없으며, 보통 SELECT 1 또는 SELECT *를 사용합니다(둘 다 동일한 의미).
  • EXISTS (서브쿼리)는 논리적으로 세미조인(semi-join) 과 같습니다.
    (조건을 만족하는 행이 여러 개 있더라도 첫 번째로 일치하는 행을 찾는 즉시 TURE로 평가하고 단 한 번만 결과에 반환(중복 제거 효과).)
• NOT EXISTS: 서브쿼리의 결과가 비어 있을 때만 TRUE.
  • 논리적으로 안티조인(anti-join) 에 해당합니다.
    (즉, 상대 테이블에 일치되는 행이 하나도 없는 왼쪽 행만 반환합니다.)

언제 EXISTS/NOT EXISTS를 사용?
• EXISTS: 해당 키로 연관된 행이 하나라도 있으면 포함 (예: 주문이 있는 고객)
• NOT EXISTS: 해당 키로 연관된 행이 전혀 없으면 포함 (예: 주문이 없는 신규 고객 목록)

---

2. EXISTS의 특성

1. NULL 안전성:
   
   • EXISTS/NOT EXISTS는 NULL 값에 영향을 받지 않습니다.
     (서브쿼리 "행의 존재 여부"만 확인하므로 NOT IN 과 달리 NULL로 인한 3값 논리(Three-valued logic: UNKNOWN) 함정이 없습니다.)
2. 상관 서브쿼리(correlated subquery) 최적화:
   
   • 대부분의 DBMS는 상관 조건을 인덱스로 탐색하거나 세미/안티 조인으로 재작성합니다.
3. 서브쿼리 SELECT 목록 무의미:
   • SELECT 1이든 SELECT *든 존재 여부만 검사합니다. (일반적으로 SELECT 1을 관례로 사용합니다.)

---

3. EXISTS vs 다른 표현

• EXISTS vs IN
  
  • IN은 비교 대상이 집합 값 목록일 때 직관적이지만, NULL을 포함한 서브쿼리 결과와 결합될 때 NOT IN은 전부 거짓(UNKNOWN)이 되어 빈 결과가 될 수 있습니다.
  • 안전한 부재(anti) 검사는 NOT EXISTS가 권장됩니다.
• NOT EXISTS vs LEFT JOIN … IS NULL
  • 둘 다 보통 안티조인으로 최적화되지만, 복잡한 조건(특히 JOIN 조건이 아닌 WHERE 조건에 상대 테이블 필터가 있을 때)에서는 의미가 달라지거나 중복/필터 위치 착오로 결과가 틀어질 수 있습니다.
  • 가장 의미가 명확하고 NULL에도 안전한 방식은 NOT EXISTS 입니다.

PostgreSQL(ANSI SQL): LEFT JOIN ... IS NULL과 NOT EXISTS의 차이

---

4. PostgreSQL 옵티마이저 관점

 

• PostgreSQL은 EXISTS/NOT EXISTS를 내부적으로 Semi Join / Anti Join 으로 재작성할 수 있으며, 상관 키 컬럼에 인덱스가 있으면 매우 효율적입니다.
• EXPLAIN 계획에서 Semi Join, Anti Join, Nested Loop, Hash Anti Join 등이 보일 수 있습니다.
• 서브쿼리 안에 추가 필터(예: order_date >= ...)를 넣어 탐색 범위를 축소하면 효과적입니다.

---

5. 테스트

▸ 테스트용 데이터 생성

-- 고객/주문 테이블 생성
CREATE TABLE customers (
  customer_id   BIGSERIAL PRIMARY KEY,
  customer_name TEXT NOT NULL,
  region        TEXT NOT NULL
);

CREATE TABLE orders (
  order_id     BIGSERIAL PRIMARY KEY,
  customer_id  BIGINT NOT NULL,
  order_date   DATE   NOT NULL,
  amount       NUMERIC(12,2) NOT NULL DEFAULT 0,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 샘플 데이터: 고객 100,000명
INSERT INTO customers (customer_name, region)
SELECT
  'customer_' || gs::text,
  CASE WHEN gs % 5 = 0 THEN 'APAC'
       WHEN gs % 5 = 1 THEN 'EMEA'
       WHEN gs % 5 = 2 THEN 'AMER'
       WHEN gs % 5 = 3 THEN 'KOREA'
       ELSE 'JAPAN' END
FROM generate_series(1, 100000) AS gs;

-- 샘플 주문: 고객의 약 60%만 주문을 보유하도록 생성 (고객 1명당 0~5건 사이, 날짜와 금액은 랜덤 분포)
WITH active_customers AS (
  SELECT customer_id
  FROM customers
  WHERE customer_id % 10 <> 0 -- 10,20,30,... 은 주문이 전혀 없도록
)
INSERT INTO orders (customer_id, order_date, amount)
SELECT
  ac.customer_id,
  DATE '2024-01-01' + (random() * 600)::int,           -- 2024-01-01 ~ 2025-08-... 사이
  round( (random() * 500000)::numeric, 2 )             -- 0 ~ 500,000
FROM active_customers ac,
     generate_series(1, (1 + (random() * 5)::int)) g;  -- 고객마다 1~6건

-- 인덱스 생성
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
CREATE INDEX idx_customers_region ON customers (region);

-- 통계 갱신
VACUUM ANALYZE customers;
VACUUM ANALYZE orders;

 
▸ 테스트 쿼리

-- EXISTS: 주문이 1건이라도 존재하는 고객
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);


-- EXISTS: 2025년 주문이 있는 고객
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date >= DATE '2025-01-01'
    AND o.order_date <  DATE '2026-01-01'
);


-- NOT EXISTS: 주문이 하나도 없는 고객
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);


-- EXISTS: 2025년에 주문이 있고, 누적 주문금액이 10만원 이상인 고객
WITH agg AS (
  SELECT o.customer_id, SUM(o.amount) AS total_amount
  FROM orders o
  GROUP BY o.customer_id
)
SELECT c.customer_id, c.customer_name, a.total_amount
FROM customers c
JOIN agg a ON a.customer_id = c.customer_id
WHERE a.total_amount >= 100000
  AND EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= DATE '2025-01-01'
      AND o.order_date <  DATE '2026-01-01'
  )
ORDER BY a.total_amount DESC
LIMIT 20;


-- EXISTS: 한국 고객 중 주문이 있는 고객
SELECT c.customer_id, c.customer_name, c.region
FROM customers c
WHERE c.region = 'KOREA'
  AND EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
  );

 

---

팁은..

• 서브쿼리의 조건절에 필터를 강하게 줘서 탐색 범위를 축소시키면 성능이 좋아집니다.
• EXISTS는 내부적으로 OR 조건도 효율적으로 평가합니다.
  → 첫 TRUE 조건이 발견되면 바로 중단되므로, 불필요한 full scan을 피할 수 있습니다.

 
오늘은 여기까지~