SQL 6회차 : OUTER JOIN과 데이터 누락 다루기

SQL 6회차 : OUTER JOIN과 데이터 누락 다루기 — LEFT/RIGHT JOIN, 누락 데이터 찾기, 탐지 쿼리 실습

한눈에 보는 요약

INNER JOIN이 “매칭되는 데이터만(교집합)”을 보여준다면, OUTER JOIN은 “한쪽을 기준으로 매칭이 없어도 남겨두는” JOIN입니다. 이 특성 덕분에 OUTER JOIN은 리포트 작성뿐 아니라 누락 데이터 찾기에 특히 강력합니다.

대표 패턴은 (1) 주문 없는 회원: members를 기준으로 LEFT JOIN orders 후, orders가 NULL인 회원을 찾습니다. (2) 재고 없는 상품: products를 기준으로 LEFT JOIN inventory 후, 재고 행이 없거나 수량이 0인 상품을 찾습니다.

실습에서는 “고아 레코드(참조 불일치)”, “누락 행(존재해야 하는데 없음)”, “의도치 않게 LEFT JOIN이 INNER JOIN으로 바뀌는 실수(WHERE 조건)”를 함께 다루며, 현업에서 바로 쓰는 탐지 쿼리를 작성합니다.

목차

• 1. OUTER JOIN이 필요한 순간: 데이터 누락을 ‘보이게’ 만들기
• 2. LEFT JOIN / RIGHT JOIN 동작 방식과 선택 기준
• 3. 누락 탐지의 핵심: “LEFT JOIN + IS NULL” 패턴
• 4. 대표 패턴 2가지: “주문 없는 회원”, “재고 없는 상품”
• 5. 실습용 스키마(회원·주문·상품·재고)
• 6. 실습: 누락/불일치 데이터 탐지 쿼리 작성
• 7. 자주 하는 실수와 디버깅 체크리스트
• 8. 따라하기: 점검 쿼리 작성 순서
• 블로그 최적화 정보

---

핵심 포인트

• OUTER JOIN은 “없음을 포함”시키는 JOIN이라, 누락 데이터 탐지에 최적입니다.
• 가장 많이 쓰는 탐지 공식: LEFT JOIN 후 WHERE 오른쪽키 IS NULL.
• RIGHT JOIN은 개념상 가능하지만, 실무에서는 가독성 때문에 “테이블 순서 바꾼 LEFT JOIN”으로 대체하는 경우가 많습니다.
• LEFT JOIN에서 오른쪽 테이블 조건을 WHERE에 두면 결과가 줄어 사실상 INNER JOIN처럼 동작할 수 있습니다(가장 흔한 실수).
• 누락은 크게 2종류입니다: (1) “존재해야 하는 행이 없음(누락)” (2) “참조해야 할 대상이 없음(고아/불일치)”. 둘은 대응이 다릅니다.

1) OUTER JOIN이 필요한 순간: 데이터 누락을 ‘보이게’ 만들기

데이터베이스에서 “누락”은 생각보다 자주 발생합니다. 예를 들어 회원은 가입했는데 주문이 한 번도 없을 수 있고, 상품은 등록됐는데 재고가 아직 입력되지 않았을 수도 있습니다. 이런 상황에서 INNER JOIN을 사용하면 매칭되는 데이터만 나오기 때문에, ‘없음’ 자체가 결과에서 사라져 버립니다.

반면 OUTER JOIN(특히 LEFT JOIN)은 기준 테이블의 행을 유지하면서 매칭이 없을 경우 오른쪽 컬럼들을 NULL로 채워줍니다. 이 NULL을 이용하면 “어떤 데이터가 비어 있는지”를 SQL로 깔끔하게 탐지할 수 있습니다.

정리: INNER JOIN은 “있는 것만 보기”, OUTER JOIN은 “없음까지 포함해 보기”.
데이터 품질 점검/운영 모니터링에서는 OUTER JOIN이 사실상 필수 도구입니다.

2) LEFT JOIN / RIGHT JOIN 동작 방식과 선택 기준

OUTER JOIN은 크게 LEFT, RIGHT(그리고 DBMS에 따라 FULL)로 나뉩니다. 이번 회차의 중심은 LEFT/RIGHT입니다.

구분	기준(무조건 남는 쪽)	매칭이 없을 때	실무 사용 팁
LEFT JOIN	왼쪽 테이블	오른쪽 컬럼이 NULL	가장 많이 사용(기준을 명확히 세우기 쉬움)
RIGHT JOIN	오른쪽 테이블	왼쪽 컬럼이 NULL	대부분 “테이블 순서 바꾼 LEFT JOIN”으로 대체 가능

RIGHT JOIN은 문법적으로 존재하지만, 읽는 사람이 “기준 테이블이 무엇인지” 한 번 더 생각하게 만드는 경우가 많습니다. 그래서 팀 컨벤션으로 LEFT JOIN을 우선 사용하고, 기준을 오른쪽에 두고 싶다면 테이블 순서를 바꿔 LEFT JOIN으로 쓰는 방식을 선호합니다.

-- RIGHT JOIN 예시(기준: 오른쪽)
SELECT *
FROM orders o
RIGHT JOIN members m
  ON o.member_id = m.member_id;

-- 같은 의미를 보통 이렇게 씁니다(기준을 왼쪽으로)
SELECT *
FROM members m
LEFT JOIN orders o
  ON o.member_id = m.member_id;

3) 누락 탐지의 핵심: “LEFT JOIN + IS NULL” 패턴

누락 탐지에서 가장 많이 쓰는 공식은 다음입니다.

• 기준 테이블 A를 LEFT JOIN으로 대상 테이블 B에 연결
• 매칭이 없으면 B의 키 컬럼은 NULL
• 따라서 WHERE B.key IS NULL로 “없음”만 골라냄

-- 누락 탐지 기본 공식(안티 조인 패턴)
SELECT A.*
FROM A
LEFT JOIN B
  ON A.key = B.key
WHERE B.key IS NULL;

여기서 중요한 포인트는 “NULL을 어떤 컬럼으로 체크하느냐”입니다. 보통은 조인에 사용한 B의 키(PK 또는 유일키)를 체크합니다. 그래야 “조인이 정말로 실패했는지”를 가장 확실하게 판별할 수 있습니다.

4) 대표 패턴 2가지: “주문 없는 회원”, “재고 없는 상품”

패턴 A. 주문 없는 회원(가입했는데 주문이 0건)

기준은 회원(members)입니다. 회원은 존재하지만 주문(orders)이 없는 회원을 찾고 싶으므로 members를 LEFT JOIN하고 orders 쪽 키가 NULL인 행을 찾습니다.

SELECT
  m.member_id,
  m.member_name,
  m.signup_date
FROM members m
LEFT JOIN orders o
  ON m.member_id = o.member_id
WHERE o.order_id IS NULL
ORDER BY m.signup_date DESC;

이 결과는 마케팅/CRM 관점에서 매우 자주 쓰입니다. 예를 들어 가입 후 30일이 지났는데 주문이 없는 회원을 추려 “첫 구매 유도 캠페인” 대상으로 삼는 식입니다.

패턴 B. 재고 없는 상품(재고 레코드가 없거나 수량이 0)

기준은 상품(products)입니다. 재고(inventory)가 없는 상품을 찾고 싶으므로 products를 LEFT JOIN합니다. 여기서 “없음”에는 두 유형이 있을 수 있습니다.

• 재고 행 자체가 없음: inventory의 product_id가 NULL
• 재고는 있지만 수량이 0: inventory.qty = 0

-- 재고가 '없는' 상품(행이 없거나, 수량이 0인 경우)
SELECT
  p.product_id,
  p.product_name,
  COALESCE(i.qty, 0) AS qty
FROM products p
LEFT JOIN inventory i
  ON p.product_id = i.product_id
WHERE i.product_id IS NULL
   OR i.qty = 0
ORDER BY p.product_id;

재고 테이블이 “창고별(warehouse_id)로 여러 행”을 가질 수 있다면, 먼저 집계를 해서 상품별 총재고를 만들고(예: SUM(qty) GROUP BY product_id), 그 집계 결과를 products에 LEFT JOIN하는 방식이 더 안전합니다.

5) 실습용 스키마(회원·주문·상품·재고)

실습을 위해 아래처럼 최소 컬럼을 가진 테이블을 가정합니다(컬럼명은 환경에 맞게 조정하시면 됩니다).

-- 회원
CREATE TABLE members (
  member_id    BIGINT PRIMARY KEY,
  member_name  VARCHAR(100) NOT NULL,
  signup_date  DATE NOT NULL
);

-- 주문(헤더)
CREATE TABLE orders (
  order_id     BIGINT PRIMARY KEY,
  member_id    BIGINT NOT NULL,
  order_date   DATE NOT NULL,
  order_status VARCHAR(20) NOT NULL
);

-- 상품
CREATE TABLE products (
  product_id    BIGINT PRIMARY KEY,
  product_name  VARCHAR(200) NOT NULL,
  category      VARCHAR(100)
);

-- 재고(상품별 1행이라고 가정)
CREATE TABLE inventory (
  product_id BIGINT PRIMARY KEY,
  qty        INT NOT NULL
);

실무에서는 외래키(FK)로 불일치를 막는 것이 가장 좋지만, 운영 과정에서 데이터가 유입되는 방식(배치, 연동, 임시 적재 등)에 따라 불일치가 생길 수 있습니다. 그래서 이번 실습은 “불일치가 발생했다고 가정했을 때 어떻게 탐지할 것인가”에 초점을 둡니다.

6) 실습: 누락/불일치 데이터 탐지 쿼리 작성

실습 1. “주문 없는 회원” 찾기 + 조건 확장

기본형은 이미 보셨습니다. 여기에 실무에서 자주 붙는 조건(예: 가입 후 30일 경과)을 추가해 보겠습니다. DBMS별 날짜 함수는 다르므로, 아래는 개념 중심 예시입니다.

-- 가입 후 일정 기간이 지났는데도 주문이 없는 회원(개념 예시)
SELECT
  m.member_id,
  m.member_name,
  m.signup_date
FROM members m
LEFT JOIN orders o
  ON m.member_id = o.member_id
WHERE o.order_id IS NULL
  AND m.signup_date <= (CURRENT_DATE - INTERVAL '30' DAY)
ORDER BY m.signup_date;

핵심은 “누락 탐지 조건(o.order_id IS NULL)”과 “대상 범위 조건(가입일, 상태 등)”을 동시에 관리하는 것입니다. 이런 쿼리는 운영 대시보드나 자동 리포트로 돌리기 좋습니다.

실습 2. “재고 없는 상품”에서 ‘창고별 재고’로 확장

inventory가 창고별로 여러 행을 갖는 경우, 상품별 총재고를 먼저 만든 뒤 products와 LEFT JOIN하는 패턴이 정석입니다.

-- inventory가 (product_id, warehouse_id, qty) 구조라고 가정
WITH inv_sum AS (
  SELECT
    product_id,
    SUM(qty) AS total_qty
  FROM inventory
  GROUP BY product_id
)
SELECT
  p.product_id,
  p.product_name,
  COALESCE(s.total_qty, 0) AS total_qty
FROM products p
LEFT JOIN inv_sum s
  ON p.product_id = s.product_id
WHERE s.product_id IS NULL
   OR s.total_qty = 0
ORDER BY p.product_id;

이렇게 하면 “재고 행이 전혀 없는 상품”과 “재고 행은 있지만 합계가 0인 상품”을 동시에 잡아낼 수 있습니다.

실습 3. 불일치(고아 레코드) 탐지: 참조해야 할 대상이 없는 경우

누락과 불일치는 비슷해 보이지만 결이 다릅니다. 누락은 “있어야 할 데이터가 없음”이고, 불일치는 “참조가 깨짐”입니다. 예를 들어 orders.member_id가 members에 없거나, inventory.product_id가 products에 없는 경우가 이에 해당합니다.

-- (A) orders에 있는데 members에 없는 회원(고아 주문) 찾기
SELECT o.*
FROM orders o
LEFT JOIN members m
  ON o.member_id = m.member_id
WHERE m.member_id IS NULL;

-- (B) inventory에 있는데 products에 없는 상품(고아 재고) 찾기
SELECT i.*
FROM inventory i
LEFT JOIN products p
  ON i.product_id = p.product_id
WHERE p.product_id IS NULL;

이 쿼리는 “INNER JOIN 결과에서 사라지는 데이터”의 원인을 밝히는 데도 유용합니다. JOIN 결과가 예상보다 작다면, 고아 레코드가 있는지부터 점검하는 습관이 좋습니다.

실습 4. 누락 탐지의 결과를 “리포트용”으로 다듬기

현업에서는 단순히 ID만 나오는 것보다 “왜 문제인지”를 같이 보여주는 리포트 형태가 선호됩니다. 예를 들어 재고 없는 상품을 찾을 때 NULL/0을 정규화하고, 상태 컬럼을 만들어 한 번에 이해할 수 있게 합니다.

SELECT
  p.product_id,
  p.product_name,
  COALESCE(i.qty, 0) AS qty,
  CASE
    WHEN i.product_id IS NULL THEN '재고레코드없음'
    WHEN i.qty = 0 THEN '재고0'
    ELSE '정상'
  END AS stock_status
FROM products p
LEFT JOIN inventory i
  ON p.product_id = i.product_id
WHERE i.product_id IS NULL
   OR i.qty = 0
ORDER BY stock_status, p.product_id;

이 방식은 운영팀/MD/기획자와 협업할 때 특히 유용합니다. SQL 결과만으로도 “조치해야 할 이유”가 바로 전달되기 때문입니다.

7) 자주 하는 실수와 디버깅 체크리스트

실수 1. LEFT JOIN인데 WHERE에서 오른쪽 조건을 걸어 INNER JOIN처럼 만들어버림

가장 흔한 함정입니다. 예를 들어 “회원과 주문을 LEFT JOIN했는데, 결제완료 주문만 보고 싶다”는 이유로 WHERE o.order_status = 'PAID'를 쓰면, 주문이 없는 회원은 o.order_status가 NULL이라 필터에서 탈락합니다. 즉 “주문 없는 회원”이 사라져버립니다.

-- 안 좋은 예: 주문이 없는 회원이 사라짐(사실상 INNER JOIN처럼 됨)
SELECT m.member_id, m.member_name
FROM members m
LEFT JOIN orders o
  ON m.member_id = o.member_id
WHERE o.order_status = 'PAID';

이럴 때는 목적에 따라 두 가지로 나뉩니다.

• “주문 없는 회원도 포함하고, 주문이 있으면 그중 PAID만 붙여라” → 조건을 ON으로 이동
• “PAID 주문이 있는 회원만 필요하다” → 애초에 INNER JOIN이 맞음

-- 목적: 주문 없는 회원도 유지 + 주문이 있으면 PAID만 매칭
SELECT m.member_id, m.member_name, o.order_id, o.order_status
FROM members m
LEFT JOIN orders o
  ON m.member_id = o.member_id
 AND o.order_status = 'PAID';

실수 2. NULL 체크 컬럼을 잘못 선택함

누락 탐지에서 NULL 체크는 보통 “오른쪽 테이블의 조인 키”로 합니다. 오른쪽의 일반 컬럼(예: order_status)은 데이터 자체가 NULL일 수도 있어, 조인 실패인지 데이터 결측인지 구분이 흐려질 수 있습니다.

실수 3. 1:N 관계에서 중복을 ‘누락’으로 오해

members : orders가 1:N이면, 주문이 있는 회원은 결과에서 여러 행으로 늘어납니다. 이때 단순히 행 수로 판단하면 오해가 생길 수 있습니다. 회원 단위 점검이 목적이면 DISTINCT, GROUP BY, EXISTS/NOT EXISTS를 함께 고려합니다.

-- 회원 단위로 '주문 유무'만 보고 싶다면(개념 예시)
SELECT
  m.member_id,
  m.member_name,
  CASE WHEN o.order_id IS NULL THEN '주문없음' ELSE '주문있음' END AS order_flag
FROM members m
LEFT JOIN (
  SELECT DISTINCT member_id, 1 AS has_order, MIN(order_id) AS order_id
  FROM orders
  GROUP BY member_id
) o
  ON m.member_id = o.member_id;

8) 따라하기: 점검 쿼리 작성 순서

1. 기준 테이블을 먼저 정합니다. “무조건 남겨야 하는 목록”이 무엇인지(회원 전체인지, 상품 전체인지)를 결정하면 LEFT/RIGHT 선택이 자연스럽습니다.

2. 연결 키(PK=FK)를 ON에 정확히 씁니다. 누락 탐지의 신뢰도는 조인 키의 정확성에 달려 있습니다. 가능하면 유일키/PK 기준으로 연결하세요.

3. 누락을 정의합니다. “행이 없음”인지(키 NULL), “값이 0/비어있음”인지(qty=0), “참조 불일치”인지(고아 레코드)부터 명확히 구분합니다.

4. WHERE 조건 위치를 점검합니다. 오른쪽 테이블 조건을 WHERE에 두면 누락이 사라질 수 있습니다. “누락도 유지해야 하는가?”를 기준으로 ON/WHERE 위치를 결정하세요.

5. 리포트형으로 다듬습니다. COALESCE로 NULL을 0으로 바꾸고, CASE로 상태를 붙이면 공유/협업이 쉬운 결과가 됩니다.

---

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.