SECTION 05. JOIN 작성의 필수 규칙

상세 페이지

JOIN은 빠르기 전에 정확해야 한다. 잘못된 조인 키와 1:N 결과 증폭을 먼저 막고, 존재 여부만 필요한 경우에는 JOIN이 필수인지 살펴보자.

권장 난이도: 초급 후반~중급 초반 · 진행 방식: BAD SQL → GOOD SQL → 핵심 한 문장.

예상 실행계획·고급 인덱스·조인 알고리즘은 강의 후반부로 옮겼다. 상세 예외와 원본 실습 근거는 SECTION 05 원문 백업에서 확인할 수 있다.

REMINDER 05-01. 조인 조건의 테이블과 컬럼을 정확히 연결하자

상황: 주문번호 1001에 해당하는 회원 닉네임과 매장명을 조회한다. 실제 기본 테이블 startdbpg.tr_ord, startdbpg.ms_mbr, startdbpg.ms_shop으로 확인한 사례다.

BAD — 매장 컬럼을 자기 자신과 비교

SELECT o.ord_no, m.nick_nm, s.shop_nm
FROM   startdbpg.tr_ord o
JOIN   startdbpg.ms_mbr m
  ON   m.mbr_id = o.mbr_id
JOIN   startdbpg.ms_shop s
  ON   s.shop_id = s.shop_id  -- 주문과 매장을 연결하지 않는 조건
WHERE  o.ord_no = 1001;

SQL

복사

GOOD — 주문의 매장 ID와 매장의 매장 ID를 연결

SELECT o.ord_no, m.nick_nm, s.shop_nm
FROM   startdbpg.tr_ord o
JOIN   startdbpg.ms_mbr m
  ON   m.mbr_id = o.mbr_id
JOIN   startdbpg.ms_shop s
  ON   s.shop_id = o.shop_id
WHERE  o.ord_no = 1001;

SQL

복사

실데이터 검증 결과: 주문번호 1001은 실제 존재하며, 매장 테이블에는 300건이 있다. 위 조건으로 조회한 결과 BAD는 300건, GOOD은 1건이었다. BAD는 주문과 매장을 연결하지 않으므로 일치 회원의 주문 1건에 매장 300건이 모두 결합되었다.

건수 검증 SQL

SELECT 'BAD' AS sql_type, COUNT(*) AS cnt
FROM   startdbpg.tr_ord o
JOIN   startdbpg.ms_mbr m
  ON   m.mbr_id = o.mbr_id
JOIN   startdbpg.ms_shop s
  ON   s.shop_id = s.shop_id
WHERE  o.ord_no = 1001
UNION ALL
SELECT 'GOOD' AS sql_type, COUNT(*) AS cnt
FROM   startdbpg.tr_ord o
JOIN   startdbpg.ms_mbr m
  ON   m.mbr_id = o.mbr_id
JOIN   startdbpg.ms_shop s
  ON   s.shop_id = o.shop_id
WHERE  o.ord_no = 1001;

SQL

복사

핵심: s.shop_id = s.shop_id는 매장 ID가 NULL이 아닌 행에서 항상 참이어서 두 테이블 간 관계를 제한하지 못한다. 이런 조건은 사실상 불필요한 카티션 곱(Cartesian Product)을 만들어 조회 건수를 증폭시킬 수 있다. JOIN ON 절은 실제로 연결해야 할 테이블의 컬럼을 비교하는지 확인하자.

예외·주의: 자기 비교 col = col은 컬럼이 NULL이면 참이 아니다. 따라서 모든 데이터에서 완전한 CROSS JOIN과 동일하다고 단정할 수는 없다. 이 사례는 특정 주문에서 측정된 결과이며 다른 주문번호 또는 데이터에 따라 반환 행 수는 달라질 수 있다. SQL이 정상적으로 실행되어도 업무 관계가 올바른지는 별도로 검증해야 한다.

REMINDER 05-02. M:1:M 조인

상황: 회원별 2023년 3월 주문금액과 이벤트 응모 건수를 동시에 조회한다. 회원을 중심으로 주문(M):회원(1):이벤트 응모(M)의 M:1:M 관계가 성립한다. BIG 계열이 아닌 실제 기본 테이블 startdbpg.ms_mbr, startdbpg.tr_ord, startdbpg.tr_event_entry를 사용한다. 검증 대상 회원은 실제 데이터가 존재하는 M0190이다.

BAD — 두 상세 테이블을 직접 JOIN하고 집계

SELECT m.mbr_id
      ,SUM(o.ord_amt) AS ord_amt
      ,COUNT(e.entry_no) AS entry_cnt
FROM   startdbpg.ms_mbr m
LEFT JOIN startdbpg.tr_ord o
  ON   o.mbr_id = m.mbr_id
 AND   o.ord_dtm >= TIMESTAMP '2023-03-01'
 AND   o.ord_dtm <  TIMESTAMP '2023-04-01'
LEFT JOIN startdbpg.tr_event_entry e
  ON   e.mbr_id = m.mbr_id
 AND   e.entry_dtm >= TIMESTAMP '2023-03-01'
 AND   e.entry_dtm <  TIMESTAMP '2023-04-01'
WHERE  m.mbr_id = 'M0190'
GROUP BY m.mbr_id;

SQL

복사

GOOD — 주문·이벤트 응모를 각각 집계한 후 JOIN

SELECT m.mbr_id
      ,COALESCE(o.ord_amt, 0) AS ord_amt
      ,COALESCE(e.entry_cnt, 0) AS entry_cnt
FROM   startdbpg.ms_mbr m
LEFT JOIN (
    SELECT mbr_id, SUM(ord_amt) AS ord_amt
    FROM   startdbpg.tr_ord
    WHERE  mbr_id = 'M0190'
      AND  ord_dtm >= TIMESTAMP '2023-03-01'
      AND  ord_dtm <  TIMESTAMP '2023-04-01'
    GROUP BY mbr_id
) o ON o.mbr_id = m.mbr_id
LEFT JOIN (
    SELECT mbr_id, COUNT(*) AS entry_cnt
    FROM   startdbpg.tr_event_entry
    WHERE  mbr_id = 'M0190'
      AND  entry_dtm >= TIMESTAMP '2023-03-01'
      AND  entry_dtm <  TIMESTAMP '2023-04-01'
    GROUP BY mbr_id
) e ON e.mbr_id = m.mbr_id
WHERE  m.mbr_id = 'M0190';

SQL

복사

실제 데이터 검증

-- 해당 회원의 주문·이벤트 응모 건수를 독립적으로 확인
SELECT 'ORDER' AS data_type, COUNT(*) AS cnt,
       SUM(ord_amt) AS ord_amt
FROM   startdbpg.tr_ord
WHERE  mbr_id = 'M0190'
  AND  ord_dtm >= TIMESTAMP '2023-03-01'
  AND  ord_dtm <  TIMESTAMP '2023-04-01'
UNION ALL
SELECT 'EVENT', COUNT(*), NULL::numeric
FROM   startdbpg.tr_event_entry
WHERE  mbr_id = 'M0190'
  AND  entry_dtm >= TIMESTAMP '2023-03-01'
  AND  entry_dtm <  TIMESTAMP '2023-04-01';

SQL

복사

실측 결과: 주문 4건 / 총 39,500원, 이벤트 응모 2건. 두 상세 테이블을 직접 JOIN하면 4 × 2 = 8개 조합이 만들어진다.

항목	BAD: 직접 JOIN 후 집계	GOOD: 각각 선집계 후 JOIN
집계 이전 JOIN 행 수	8행	1행
ord_amt 합계	79,000원 (2배 오류)	39,500원 (정확)
entry_cnt	8건 (4배 오류)	2건 (정확)
최종 결과 행 수	1행	1행

실제 실행계획 확인: EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)로 두 SQL을 실행했다.

•

BAD: Nested Loop Left Join에서 rows=4 다음 rows=8로 증가했고, 이벤트 2건을 담은 Materialize가 loops=4로 반복되었다. 집계 노드는 GroupAggregate.

•

GOOD: 주문·이벤트 각각 GroupAggregate로 회원별 1행으로 집계하고, 그 결과를 Nested Loop Left Join했다.

•

재측정 실행시간은 BAD 1.806ms, GOOD 1.653ms였으며 양쪽 모두 Buffers: shared hit=254였다. 이번 실습은 속도 차이가 아니라 집계 오류를 보여주는 사례다. 실행시간은 매번 달라질 수 있다.

핵심: M:1:M 조인에서 독립된 두 상세 테이블을 동시에 펼치면 각 행이 곱해져 SUM·COUNT가 증폭된다. 서로 다른 상세 테이블의 측정값을 회원 단위로 계산해야 한다면 각각 먼저 GROUP BY 한 뒤 1:1 결과를 JOIN하자.

예외·주의: 기간 조건은 LEFT JOIN의 의미를 보존하기 위해 각 테이블의 ON 절에 넣었다. 응모가 한 건도 없더라도 회원을 유지하려는 경우 WHERE e.entry_dtm ...처럼 오른쪽 테이블을 직접 필터링하면 결과가 바뀔 수 있다. 주문과 응모 간 구체적인 업무 연결 관계가 존재하는 경우에는 회원 ID만으로 두 집합을 결합할 수 있는지 먼저 확인해야 한다. 실제 데이터 분포·인덱스에 따라 실행계획과 성능은 달라진다.

REMINDER 05-03. 복합키 일부를 누락한 JOIN을 주의하자

상황: 커피매장과는 별개의 교육용 업무로, 회원별 월 요금 청구와 실제 분할 납부 내역을 관리한다. 회원 M1의 2024년 1월·2월 청구 및 납부를 조회한다. startdbpg에 실제로 생성한 일반 테이블을 사용하며 _big 테이블을 사용하지 않는다.

테이블 관계 (실제 PK·FK 설정 완료)

•

tr_mbr_fee_pay (회원요금결제): PRIMARY KEY (mbr_id, pay_ym); tot_amt=월 요금, unpaid_amt=미납금액.

•

tr_mbr_fee_pay_det (회원요금결제상세): PRIMARY KEY (mbr_id, pay_ym, pay_seq); pay_amt=실제 납부금액, pay_dt=납부일자.

•

상세에서 청구로 이어지는 복합 FK: (mbr_id, pay_ym) REFERENCES tr_mbr_fee_pay(mbr_id, pay_ym). 기존 ms_mbr와는 다른 업무를 가정하므로 해당 테이블로 연결되는 FK는 없다.

테이블 생성 SQL (실제 DB에서 실행 완료)

CREATE TABLE startdbpg.tr_mbr_fee_pay (
    mbr_id       varchar(40) NOT NULL,
    pay_ym       varchar(6) NOT NULL,
    tot_amt      numeric(18,3) NOT NULL,
    unpaid_amt   numeric(18,3) NOT NULL,
    CONSTRAINT tr_mbr_fee_pay_pk PRIMARY KEY (mbr_id, pay_ym),
    CONSTRAINT tr_mbr_fee_pay_ym_chk
        CHECK (pay_ym ~ '^[0-9]{4}(0[1-9]|1[0-2])$'),
    CONSTRAINT tr_mbr_fee_pay_amt_chk
        CHECK (tot_amt >= 0 AND unpaid_amt >= 0 AND unpaid_amt <= tot_amt)
);

CREATE TABLE startdbpg.tr_mbr_fee_pay_det (
    mbr_id       varchar(40) NOT NULL,
    pay_ym       varchar(6) NOT NULL,
    pay_seq      integer NOT NULL,
    pay_amt      numeric(18,3) NOT NULL,
    pay_dt       date NOT NULL,
    CONSTRAINT tr_mbr_fee_pay_det_pk PRIMARY KEY (mbr_id, pay_ym, pay_seq),
    CONSTRAINT tr_mbr_fee_pay_det_fk01
        FOREIGN KEY (mbr_id, pay_ym)
        REFERENCES startdbpg.tr_mbr_fee_pay (mbr_id, pay_ym),
    CONSTRAINT tr_mbr_fee_pay_det_amt_chk
        CHECK (pay_seq > 0 AND pay_amt > 0)
);

SQL

복사

실습 데이터 (실제 입력 완료)

회원요금결제 mbr_id	pay_ym	tot_amt	unpaid_amt
M1	202401	10000	0
M1	202402	10000	1000
회원요금결제상세 mbr_id	pay_ym	pay_seq	pay_amt	pay_dt
---	---	---:	---:	---
M1	202401	1	10000	2024-01-20
M1	202402	1	8000	2024-02-20
M1	202402	2	1000	2024-02-21

BAD — 복합키에서 pay_ym 조인 누락

SELECT p.mbr_id, p.pay_ym AS bill_ym,
       d.pay_ym AS detail_ym, d.pay_seq, d.pay_amt
FROM   startdbpg.tr_mbr_fee_pay p
JOIN   startdbpg.tr_mbr_fee_pay_det d
  ON   d.mbr_id = p.mbr_id
WHERE  p.mbr_id = 'M1'
ORDER BY p.pay_ym, d.pay_ym, d.pay_seq;

SQL

복사

회원ID만 연결하므로 월별 청구 2행 × 납부 상세 3행 = 6행이 발생한다. 1월 청구에 2월 납부 2건, 2월 청구에 1월 납부 1건이 잘못 결합된다.

GOOD — 업무 관계에 필요한 복합키 두 컬럼 모두 JOIN

SELECT p.mbr_id, p.pay_ym AS bill_ym,
       d.pay_ym AS detail_ym, d.pay_seq, d.pay_amt
FROM   startdbpg.tr_mbr_fee_pay p
JOIN   startdbpg.tr_mbr_fee_pay_det d
  ON   d.mbr_id = p.mbr_id
 AND   d.pay_ym = p.pay_ym
WHERE  p.mbr_id = 'M1'
ORDER BY p.pay_ym, d.pay_ym, d.pay_seq;

SQL

복사

청구 월과 상세 월까지 일치시켜 정확히 3행만 연결한다.

실측 검증 결과: 월별 납부금액 집계 차이

청구월	BAD: 결과행	BAD: 납부합계	GOOD: 결과행	GOOD: 납부합계
202401	3	19000 (오류)	1	10000
202402	3	19000 (오류)	2	9000
합계	6	월별 중복 집계	3	월별 실제 납부

검증 SQL:

WITH joined AS (
    SELECT 'BAD' AS sql_type, p.pay_ym AS bill_ym, d.pay_amt
    FROM   startdbpg.tr_mbr_fee_pay p
    JOIN   startdbpg.tr_mbr_fee_pay_det d
      ON   d.mbr_id = p.mbr_id
    WHERE  p.mbr_id = 'M1'
    UNION ALL
    SELECT 'GOOD' AS sql_type, p.pay_ym AS bill_ym, d.pay_amt
    FROM   startdbpg.tr_mbr_fee_pay p
    JOIN   startdbpg.tr_mbr_fee_pay_det d
      ON   d.mbr_id = p.mbr_id
     AND   d.pay_ym = p.pay_ym
    WHERE  p.mbr_id = 'M1'
)
SELECT sql_type, bill_ym,
       COUNT(*) AS joined_rows, SUM(pay_amt) AS paid_sum
FROM   joined
GROUP BY sql_type, bill_ym
ORDER BY sql_type, bill_ym;

SQL

복사

실제 실행계획 (EXPLAIN ANALYZE BUFFERS COSTS OFF): 아래는 전체 조회 컬럼(tot_amt, unpaid_amt, 납부일 포함)과 ORDER BY p.pay_ym, d.pay_ym, d.pay_seq를 사용하여 측정한 계획이다.

BAD — 실제 계획

Sort (actual time=0.114..0.116 rows=6 loops=1)
  Sort Key: p.pay_ym, d.pay_ym, d.pay_seq
  Sort Method: quicksort  Memory: 25kB
  Buffers: shared hit=10
  ->  Nested Loop (actual time=0.028..0.034 rows=6 loops=1)
        Buffers: shared hit=4
        ->  Bitmap Heap Scan on tr_mbr_fee_pay p (actual time=0.010..0.011 rows=2 loops=1)
              Recheck Cond: ((mbr_id)::text = 'M1'::text)
              Heap Blocks: exact=1
              Buffers: shared hit=2
              ->  Bitmap Index Scan on tr_mbr_fee_pay_pk (actual time=0.004..0.004 rows=2 loops=1)
                    Index Cond: ((mbr_id)::text = 'M1'::text)
                    Buffers: shared hit=1
        ->  Materialize (actual time=0.008..0.010 rows=3 loops=2)
              Buffers: shared hit=2
              ->  Bitmap Heap Scan on tr_mbr_fee_pay_det d (actual time=0.010..0.012 rows=3 loops=1)
                    Recheck Cond: ((mbr_id)::text = 'M1'::text)
                    Heap Blocks: exact=1
                    Buffers: shared hit=2
                    ->  Bitmap Index Scan on tr_mbr_fee_pay_det_pk (actual time=0.005..0.006 rows=3 loops=1)
                          Index Cond: ((mbr_id)::text = 'M1'::text)
                          Buffers: shared hit=1
Planning:
  Buffers: shared hit=126
Planning Time: 0.678 ms
Execution Time: 0.250 ms

Plain Text

복사

GOOD — 실제 계획

Merge Join (actual time=0.027..0.038 rows=3 loops=1)
  Merge Cond: ((d.pay_ym)::text = (p.pay_ym)::text)
  Buffers: shared hit=4
  ->  Index Scan using tr_mbr_fee_pay_det_pk on tr_mbr_fee_pay_det d (actual time=0.011..0.012 rows=3 loops=1)
        Index Cond: ((mbr_id)::text = 'M1'::text)
        Buffers: shared hit=2
  ->  Index Scan using tr_mbr_fee_pay_pk on tr_mbr_fee_pay p (actual time=0.012..0.012 rows=2 loops=1)
        Index Cond: ((mbr_id)::text = 'M1'::text)
        Buffers: shared hit=2
Planning:
  Buffers: shared hit=195
Planning Time: 1.055 ms
Execution Time: 0.232 ms

Plain Text

복사

실행계획 읽기: BAD는 Nested Loop가 청구 2행마다 납부 3행을 반복 결합하여 6행을 만들었다. GOOD에서는 Merge Cond: d.pay_ym = p.pay_ym으로 결제월까지 확인해 3행이 생성되었다. 작은 교육용 데이터이므로 두 실행시간(0.250ms·0.232ms)의 차이에 성능 의미를 부여하지 않는다.

핵심: 복합 PK·FK 관계에서 JOIN에 필요한 키 일부를 빼면 같은 회원의 서로 다른 결제월 데이터까지 결합되어 조회 건수와 집계금액이 틀어진다. JOIN 조건을 적기 전 어떤 컬럼들의 조합이 한 업무 건을 식별하는지 확인하자.

예외·주의: 모든 JOIN에 PK 컬럼 전체를 사용하라는 뜻은 아니다. 실제 업무 관계가 1:N이면 관련 FK 컬럼만으로 JOIN하는 것이 정상이다. 이 실습에서는 상세가 월 청구의 하위 데이터이므로 (mbr_id, pay_ym) 두 컬럼을 사용해야 한다. 참고로 복합 FK 제약이 DB에 있어도 SELECT의 잘못된 JOIN 조건을 자동으로 고쳐주지는 않는다. 두 테이블과 M1 데이터는 교육을 위해 신규 생성했으며 실제 운영 회원요금 수납 기록이 아니다.

REMINDER 05-04. LEFT JOIN의 ON과 WHERE를 구분하자

상황: GOLD 등급 회원을 모두 조회하고, 픽업 완료(PKUP) 주문이 있다면 주문번호도 함께 표시한다. 실제 일반 테이블 startdbpg.ms_mbr, startdbpg.tr_ord에서 검증했다. ms_mbr에는 GOLD 회원 3,146명이 있고 그중 22명은 픽업 완료 주문이 전혀 없다.

BAD — 오른쪽 테이블 조건을 WHERE에 지정

SELECT m.mbr_id, o.ord_no
FROM   startdbpg.ms_mbr m
LEFT JOIN startdbpg.tr_ord o
  ON   o.mbr_id = m.mbr_id
WHERE  m.mbr_gd = 'GOLD'
  AND  o.ord_st = 'PKUP';

SQL

복사

LEFT JOIN으로 보존한 회원 행이라도 매칭되는 주문이 없으면 o.ord_st는 NULL이다. 이때 WHERE o.ord_st = 'PKUP'은 참이 아니므로 그 회원이 결과에서 사라진다. 실제 계획도 외부 조인이 아닌 Nested Loop(내부 조인)로 처리되었다.

GOOD — 오른쪽 테이블 조건을 ON에 지정

SELECT m.mbr_id, o.ord_no
FROM   startdbpg.ms_mbr m
LEFT JOIN startdbpg.tr_ord o
  ON   o.mbr_id = m.mbr_id
 AND   o.ord_st = 'PKUP'
WHERE  m.mbr_gd = 'GOLD';

SQL

복사

ON 조건은 픽업 완료 주문 중 어떤 행이 회원과 매칭될지 정한다. 매칭되는 주문이 없어도 LEFT JOIN은 회원 행을 유지하고 o.ord_no에 NULL을 반환한다.

실제 데이터 비교

측정 항목	BAD: 상태 조건 WHERE	GOOD: 상태 조건 ON
GOLD 등급 회원(원본 데이터)	3,146명	3,146명
조회 결과에 포함된 회원	3,124명	3,146명
ord_no IS NULL인 결과	0건	22건
최종 반환 행 수	270,117행	270,139행
실제 JOIN 노드	Nested Loop	Nested Loop Left Join
Buffers	shared hit=248171	shared hit=248171
재측정 실행시간	367.408ms	375.619ms

누락 회원 검증 SQL: 실제 GOLD 회원 M3391은 주문이 한 건도 없으며 BAD에서는 0행, GOOD에서는 ord_no=NULL인 1행이 반환되었다.

-- 결과: GOOD, M3391, NULL (BAD는 결과 없음)
SELECT 'BAD' AS sql_type, m.mbr_id, o.ord_no
FROM   startdbpg.ms_mbr m
LEFT JOIN startdbpg.tr_ord o
  ON   o.mbr_id = m.mbr_id
WHERE  m.mbr_gd = 'GOLD'
  AND  o.ord_st = 'PKUP'
  AND  m.mbr_id = 'M3391'
UNION ALL
SELECT 'GOOD' AS sql_type, m.mbr_id, o.ord_no
FROM   startdbpg.ms_mbr m
LEFT JOIN startdbpg.tr_ord o
  ON   o.mbr_id = m.mbr_id
 AND   o.ord_st = 'PKUP'
WHERE  m.mbr_gd = 'GOLD'
  AND  m.mbr_id = 'M3391';

SQL

복사

미매칭 회원 건수 검증 SQL

SELECT COUNT(*) AS gold_members,
       COUNT(*) FILTER (
           WHERE NOT EXISTS (
               SELECT 1
               FROM   startdbpg.tr_ord o
               WHERE  o.mbr_id = m.mbr_id
                 AND  o.ord_st = 'PKUP'
           )
       ) AS no_pickup_members
FROM   startdbpg.ms_mbr m
WHERE  m.mbr_gd = 'GOLD';
-- 실제 결과: gold_members=3146, no_pickup_members=22

SQL

복사

실측 실행계획 — BAD

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT m.mbr_id, o.ord_no
FROM   startdbpg.ms_mbr m
LEFT JOIN startdbpg.tr_ord o
  ON   o.mbr_id = m.mbr_id
WHERE  m.mbr_gd = 'GOLD'
  AND  o.ord_st = 'PKUP';

SQL

복사

Nested Loop (actual time=0.071..344.879 rows=270117 loops=1)
  Buffers: shared hit=248171
  ->  Seq Scan on ms_mbr m (actual time=0.034..2.823 rows=3146 loops=1)
        Filter: ((mbr_gd)::text = 'GOLD'::text)
        Rows Removed by Filter: 6853
        Buffers: shared hit=132
  ->  Index Scan using tr_ord_fk01 on tr_ord o (actual time=0.007..0.095 rows=86 loops=3146)
        Index Cond: ((mbr_id)::text = (m.mbr_id)::text)
        Filter: ((ord_st)::text = 'PKUP'::text)
        Rows Removed by Filter: 0
        Buffers: shared hit=248039
Planning:
  Buffers: shared hit=330
Planning Time: 3.142 ms
Execution Time: 367.408 ms

Plain Text

복사

실측 실행계획 — GOOD

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT m.mbr_id, o.ord_no
FROM   startdbpg.ms_mbr m
LEFT JOIN startdbpg.tr_ord o
  ON   o.mbr_id = m.mbr_id
 AND   o.ord_st = 'PKUP'
WHERE  m.mbr_gd = 'GOLD';

SQL

복사

Nested Loop Left Join (actual time=0.052..352.127 rows=270139 loops=1)
  Buffers: shared hit=248171
  ->  Seq Scan on ms_mbr m (actual time=0.024..2.818 rows=3146 loops=1)
        Filter: ((mbr_gd)::text = 'GOLD'::text)
        Rows Removed by Filter: 6853
        Buffers: shared hit=132
  ->  Index Scan using tr_ord_fk01 on tr_ord o (actual time=0.007..0.097 rows=86 loops=3146)
        Index Cond: ((mbr_id)::text = (m.mbr_id)::text)
        Filter: ((ord_st)::text = 'PKUP'::text)
        Rows Removed by Filter: 0
        Buffers: shared hit=248039
Planning:
  Buffers: shared hit=330
Planning Time: 1.571 ms
Execution Time: 375.619 ms

Plain Text

복사

실행계획 읽기: BAD는 WHERE가 오른쪽 테이블의 NULL을 거부하므로 옵티마이저가 Nested Loop로 처리했고, GOOD은 미매칭 회원을 남겨야 하므로 Nested Loop Left Join이 유지되었다. 두 계획 모두 주문 인덱스 tr_ord_fk01(mbr_id)를 loops=3146번 접근했다. 따라서 이 실습은 속도 향상이 아니라 결과의 의미 차이를 검증하는 사례다. 실행시간은 캐시·부하에 따라 변동한다.

핵심: LEFT JOIN에서 오른쪽 테이블 조건을 WHERE에 두면 매칭되지 않은 왼쪽 행이 사라질 수 있다. 왼쪽 회원을 모두 보존하고 특정 주문만 붙이려면 주문 상태 조건을 ON에 둔다.

예외·주의: 오른쪽 조건을 무조건 ON으로 옮기는 것은 규칙이 아니다. 실제 업무 요구가 '픽업 완료 주문이 있는 GOLD 회원만 조회'라면, JOIN(INNER JOIN)을 명시하는 편이 의도에 맞는다. 또한 ON과 WHERE의 조건 배치는 단순한 성능 튜닝이 아니라 조회 결과 자체를 바꾸는 행위다.

REMINDER 05-05. 불필요한 JOIN은 제거하되 데이터 무결성을 먼저 확인하자

상황: 2023년 3월의 주문 건수만 집계하면 되는데, 결과에 사용하지 않는 회원 테이블까지 INNER JOIN한다. 실제 일반 테이블 startdbpg.tr_ord와 startdbpg.ms_mbr을 대상으로 실행하여 결과와 실행계획을 비교했다.

BAD — 주문 건수에 불필요한 회원 테이블 JOIN

SELECT COUNT(*) AS ord_cnt
FROM   startdbpg.tr_ord o
JOIN   startdbpg.ms_mbr m
  ON   m.mbr_id = o.mbr_id
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01';

SQL

복사

GOOD — 회원 테이블을 제거하고 NULL 의미를 보존

SELECT COUNT(*) AS ord_cnt
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01'
  AND  o.mbr_id IS NOT NULL;

SQL

복사

왜 o.mbr_id IS NOT NULL을 추가하는가? 실제 tr_ord.mbr_id는 NULL 허용이다. 원래의 INNER JOIN은 회원 ID가 NULL인 주문을 제외한다. 따라서 단순히 JOIN만 삭제하면 NULL 주문까지 집계되어 향후 결과가 달라질 수 있다. IS NOT NULL을 남겨 원래 의미를 보존한다.

실제 무결성 검증

•

tr_ord_fk01: FOREIGN KEY (mbr_id) REFERENCES startdbpg.ms_mbr(mbr_id)이며 convalidated=true로 확인했다.

•

tr_ord.mbr_id의 is_nullable=YES. 따라서 NOT NULL은 보장되지 않는다.

•

2023년 3월 전체 주문 17,582건, 해당 기간 회원 ID NULL 0건.

•

해당 기간 유효한 회원 ID인데 회원 마스터에 존재하지 않는 주문 0건(NOT EXISTS 확인).

•

실제 BAD와 GOOD 모두 ord_cnt = 17582 반환.

검증 SQL (BAD·GOOD 결과 비교)

SELECT 'BAD' AS sql_type, COUNT(*) AS ord_cnt
FROM   startdbpg.tr_ord o
JOIN   startdbpg.ms_mbr m
  ON   m.mbr_id = o.mbr_id
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01'
UNION ALL
SELECT 'GOOD' AS sql_type, COUNT(*) AS ord_cnt
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01'
  AND  o.mbr_id IS NOT NULL;

SQL

복사

실측 결과

항목	BAD	GOOD
주문 건수	17,582	17,582
접근·JOIN	tr_ord • ms_mbr • Hash Join	tr_ord만 접근, JOIN 없음
Buffers: shared hit	292	262
실행시간 (1회 측정)	19.336ms	7.506ms

BAD 실제 실행계획

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT COUNT(*) AS ord_cnt
FROM   startdbpg.tr_ord o
JOIN   startdbpg.ms_mbr m
  ON   m.mbr_id = o.mbr_id
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01';

SQL

복사

Aggregate (actual time=19.125..19.127 rows=1 loops=1)
  Buffers: shared hit=292
  ->  Hash Join (actual time=4.299..17.395 rows=17582 loops=1)
        Hash Cond: ((o.mbr_id)::text = (m.mbr_id)::text)
        Buffers: shared hit=292
        ->  Index Scan using tr_ord_x51 on tr_ord o (actual time=0.038..4.946 rows=17582 loops=1)
              Index Cond: ((ord_dtm >= '2023-03-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-04-01 00:00:00'::timestamp without time zone))
              Buffers: shared hit=262
        ->  Hash (actual time=4.154..4.155 rows=9999 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 500kB
              Buffers: shared hit=30
              ->  Index Only Scan using ms_mbr_pk on ms_mbr m (actual time=0.030..1.605 rows=9999 loops=1)
                    Heap Fetches: 0
                    Buffers: shared hit=30
Planning:
  Buffers: shared hit=340
Planning Time: 1.652 ms
Execution Time: 19.336 ms

Plain Text

복사

GOOD 실제 실행계획

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT COUNT(*) AS ord_cnt
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01'
  AND  o.mbr_id IS NOT NULL;

SQL

복사

Aggregate (actual time=7.346..7.348 rows=1 loops=1)
  Buffers: shared hit=262
  ->  Index Scan using tr_ord_x51 on tr_ord o (actual time=0.041..5.620 rows=17582 loops=1)
        Index Cond: ((ord_dtm >= '2023-03-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-04-01 00:00:00'::timestamp without time zone))
        Filter: (mbr_id IS NOT NULL)
        Buffers: shared hit=262
Planning:
  Buffers: shared hit=145
Planning Time: 0.677 ms
Execution Time: 7.506 ms

Plain Text

복사

실행계획 읽기: BAD는 tr_ord_x51로 17,582건을 찾은 뒤, 회원 테이블 ms_mbr_pk를 9,999건 읽고 Hash Join을 수행했다. GOOD은 같은 주문 17,582건을 읽고 mbr_id IS NOT NULL만 검사하여 Aggregate로 집계했다. 회원 접근과 Hash Join이 제거되어 공유 버퍼 접근도 감소했다.

핵심: 조회·필터·집계에 필요하지 않은 테이블이라면, 조인을 제거해도 결과가 보존되는지 PK·FK·NULL 조건부터 검증한 뒤 제거하자. 이 사례는 실제로 불필요한 Hash Join이 수행됨을 확인했다.

예외·주의: 회원 마스터의 존재 여부 자체를 검증해야 하는 업무라면 JOIN을 임의로 제거하면 안 된다. 실제 FK가 없거나 검증되지 않았으면 IS NOT NULL 조건만으로 회원 존재를 보장할 수 없다. 또한 PostgreSQL이 모든 불필요한 JOIN을 자동 제거하는 것은 아니다. 본 사례의 실행시간은 단일 측정 결과이므로 성능 차이는 반복 실행 및 환경에 따라 달라진다.

REMINDER 05-06. 존재 여부만 필요하면 JOIN + DISTINCT 대신 EXISTS를 고려하자

상황: 주문 이력이 한 건이라도 있는 GOLD 등급 회원의 ID를 중복 없이 조회한다. 실제 기본 테이블 startdbpg.ms_mbr과 startdbpg.tr_ord를 사용한다.

BAD — JOIN으로 주문을 펼친 후 DISTINCT로 중복 제거

SELECT DISTINCT m.mbr_id
FROM   startdbpg.ms_mbr m
JOIN   startdbpg.tr_ord o
  ON   o.mbr_id = m.mbr_id
WHERE  m.mbr_gd = 'GOLD';

SQL

복사

회원 한 명에게 주문이 여러 건 있으면 같은 회원 ID가 주문 건수만큼 JOIN 결과에 반복된다. 그 후 DISTINCT로 중복을 제거한다.

GOOD — EXISTS로 주문 존재 여부만 판단

SELECT m.mbr_id
FROM   startdbpg.ms_mbr m
WHERE  m.mbr_gd = 'GOLD'
  AND  EXISTS (
      SELECT 1
      FROM   startdbpg.tr_ord o
      WHERE  o.mbr_id = m.mbr_id
  );

SQL

복사

원하는 조건은 '주문이 있는 회원인가?'이므로 주문 자체의 모든 행을 결과로 결합할 이유가 없다. EXISTS는 존재 여부를 표현하고, 이번 실습에서는 실제로 Nested Loop Semi Join으로 처리되었다.

실제 데이터 검증

•

ms_mbr의 GOLD 회원: 3,146명

•

주문 이력이 있는 GOLD 회원: 3,124명

•

주문 이력이 없는 GOLD 회원: 22명

•

BAD, GOOD 각각 3,124행 반환. 양방향 차집합 검사(bad_only, good_only) 모두 0행.

•

ms_mbr.mbr_id는 실제 PRIMARY KEY (mbr_id)이며 NOT NULL이므로, 이 사례에서 DISTINCT가 회원 중복만 제거한다는 전제가 성립한다.

결과 동등성 검증 SQL

WITH bad AS (
    SELECT DISTINCT m.mbr_id
    FROM   startdbpg.ms_mbr m
    JOIN   startdbpg.tr_ord o
      ON   o.mbr_id = m.mbr_id
    WHERE  m.mbr_gd = 'GOLD'
),
good AS (
    SELECT m.mbr_id
    FROM   startdbpg.ms_mbr m
    WHERE  m.mbr_gd = 'GOLD'
      AND  EXISTS (
          SELECT 1
          FROM   startdbpg.tr_ord o
          WHERE  o.mbr_id = m.mbr_id
      )
)
SELECT (SELECT COUNT(*) FROM bad) AS bad_cnt,
       (SELECT COUNT(*) FROM good) AS good_cnt,
       (SELECT COUNT(*) FROM bad b
        WHERE NOT EXISTS (
            SELECT 1 FROM good g WHERE g.mbr_id = b.mbr_id
        )) AS bad_only,
       (SELECT COUNT(*) FROM good g
        WHERE NOT EXISTS (
            SELECT 1 FROM bad b WHERE b.mbr_id = g.mbr_id
        )) AS good_only;
-- 실제 결과: bad_cnt=3124, good_cnt=3124, bad_only=0, good_only=0

SQL

복사

실측 비교

항목	BAD: JOIN + DISTINCT	GOOD: EXISTS
반환 회원 수	3,124	3,124
주요 계획 노드	Nested Loop → Unique	Nested Loop Semi Join
주문과 JOIN한 행 수	270,123행	존재 확인이므로 중복 행 확장 없음
회원당 주문 인덱스 측정	rows=86 loops=3146	rows=1 loops=3146
Buffers: shared hit	9,654	9,571
단일 실행시간	132.985ms	28.876ms

BAD 실제 실행계획

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT DISTINCT m.mbr_id
FROM   startdbpg.ms_mbr m
JOIN   startdbpg.tr_ord o
  ON   o.mbr_id = m.mbr_id
WHERE  m.mbr_gd = 'GOLD';

SQL

복사

Unique (actual time=0.064..132.592 rows=3124 loops=1)
  Buffers: shared hit=9654
  ->  Nested Loop (actual time=0.054..100.901 rows=270123 loops=1)
        Buffers: shared hit=9654
        ->  Index Scan using ms_mbr_pk on ms_mbr m (actual time=0.027..3.971 rows=3146 loops=1)
              Filter: ((mbr_gd)::text = 'GOLD'::text)
              Rows Removed by Filter: 6853
              Buffers: shared hit=161
        ->  Index Only Scan using tr_ord_fk01 on tr_ord o (actual time=0.007..0.018 rows=86 loops=3146)
              Index Cond: (mbr_id = (m.mbr_id)::text)
              Heap Fetches: 0
              Buffers: shared hit=9493
Planning:
  Buffers: shared hit=306
Planning Time: 1.503 ms
Execution Time: 132.985 ms

Plain Text

복사

GOOD 실제 실행계획

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT m.mbr_id
FROM   startdbpg.ms_mbr m
WHERE  m.mbr_gd = 'GOLD'
  AND  EXISTS (
      SELECT 1
      FROM   startdbpg.tr_ord o
      WHERE  o.mbr_id = m.mbr_id
  );

SQL

복사

Nested Loop Semi Join (actual time=0.048..28.413 rows=3124 loops=1)
  Buffers: shared hit=9571
  ->  Seq Scan on ms_mbr m (actual time=0.022..2.788 rows=3146 loops=1)
        Filter: ((mbr_gd)::text = 'GOLD'::text)
        Rows Removed by Filter: 6853
        Buffers: shared hit=132
  ->  Index Only Scan using tr_ord_fk01 on tr_ord o (actual time=0.008..0.008 rows=1 loops=3146)
        Index Cond: (mbr_id = (m.mbr_id)::text)
        Heap Fetches: 0
        Buffers: shared hit=9439
Planning:
  Buffers: shared hit=324
Planning Time: 1.598 ms
Execution Time: 28.876 ms

Plain Text

복사

실행계획 읽기: BAD는 GOLD 회원 3,146명을 기준으로 주문을 JOIN해 270,123행을 만든 뒤 Unique로 3,124행으로 축소했다. GOOD은 Nested Loop Semi Join으로 각 회원에 대해 주문이 존재하는지만 확인한다. 주문 측 Index Only Scan의 rows=1 loops=3146이 그 증거다. 두 실행계획은 회원 테이블의 접근 경로(Index Scan 대 Seq Scan)도 다르므로, 실행시간의 모든 차이를 Semi Join 하나로만 설명해서는 안 된다.

핵심: 원하는 결과가 '관련 행이 존재하는가?'라면 JOIN + DISTINCT로 중복을 만든 후 제거하기보다 EXISTS로 존재 조건을 직접 표현하자. 실제 PostgreSQL 실행계획에서도 중복 확대 및 제거 작업이 없어지는 사례를 확인했다.

예외·주의: EXISTS가 항상 더 빠르거나 항상 Nested Loop Semi Join이 되는 것은 아니다. 데이터 분포·인덱스·통계에 따라 Hash Semi Join 등 다른 계획이 가능하고, 옵티마이저가 JOIN + DISTINCT를 다른 방식으로 최적화할 수도 있다. 본 실행시간은 단일 실행 측정값으로 반복 측정 결과나 보장된 성능 비율이 아니다. 주문 건수·금액·주문별 정보가 필요하다면 EXISTS만으로는 요구사항을 충족하지 못한다.

개발자가 기억할 체크리스트

JOIN의 두 키가 실제 업무 관계에 맞는가?

1:N 관계로 합계·건수가 증폭되지 않는가?

LEFT JOIN의 ON과 WHERE로 결과가 달라지지 않는가?

존재 확인에 불필요한 JOIN·DISTINCT가 없는가?

이 섹션의 한 줄 요약: JOIN은 빠르기 전에 정확해야 한다. 잘못된 조인 키와 1:N 결과 증폭을 먼저 막고, 존재 여부만 필요한 경우에는 JOIN이 필수인지 살펴보자.