SQL 5회차 : INNER JOIN / 관계 이해 — 주문·주문상세·상품으로 “주문 내역서” 만들기

SQL 5회차 : INNER JOIN / 관계 이해 — 주문·주문상세·상품으로 “주문 내역서” 만들기

한눈에 보는 요약

조인은 “정규화로 나뉜 테이블을 다시 한 화면으로 조합”하기 위해 필요합니다. 주문 테이블은 주문의 머리(헤더), 주문상세는 상품 라인(라인아이템), 상품은 상품 마스터 정보를 담고 있으므로 한 번의 조회로 주문 내역서를 만들려면 JOIN이 필수입니다.

INNER JOIN은 두 테이블(또는 그 이상)의 “매칭되는 행만” 남기는 방식이며, ON에는 “어떤 키로 연결할지(관계)”를, WHERE에는 “필터링(기간/고객/상태 등)”을 주로 넣는 것이 안전합니다.

실습에서는 주문(orders) + 주문상세(order_items) + 상품(products)을 결합하고, 라인 금액과 주문 합계를 계산해 “주문 내역서” 형태의 결과를 만듭니다.

목차

• 1. 조인이 왜 필요한가(정규화된 데이터 조합)
• 2. 관계(relationship) 이해: PK/FK와 1:N, N:M
• 3. INNER JOIN 핵심 개념과 동작 방식
• 4. ON 조건 설계 원칙: “연결”과 “필터” 분리
• 5. 실습 스키마: 주문·주문상세·상품
• 6. 실습: 주문 내역서 쿼리 만들기(라인 + 합계)
• 7. 자주 하는 실수와 검증 쿼리
• 8. 따라하기: 단계별 실습 가이드

---

핵심 포인트

• 정규화는 중복을 줄이지만, 화면/리포트에서는 “다시 조합”이 필요합니다. 조인은 이 간극을 메워줍니다.
• orders(헤더) ↔ order_items(라인) ↔ products(마스터) 구조는 전형적인 1:N + N:M(브릿지) 패턴입니다.
• INNER JOIN은 매칭되는 행만 남기므로, 누락 데이터(고아 레코드)가 있으면 결과가 “사라질 수” 있습니다.
• ON은 관계(키 매칭)를, WHERE는 조회 조건(기간/고객/상태)을 담는 습관이 디버깅과 확장에 유리합니다.
• 주문 내역서에서 라인 금액(qty * unit_price)과 주문 합계는 GROUP BY 또는 윈도우 함수로 계산할 수 있습니다.

상세 설명

1) 조인이 왜 필요한가: 정규화된 데이터 조합

실무 데이터는 보통 정규화(정리)되어 있습니다. 예를 들어 “주문”을 생각해보면, 한 주문에는 여러 상품이 포함될 수 있습니다. 이때 주문 테이블에 상품명/가격/수량을 그대로 반복 저장하면 주문이 커질수록 같은 정보가 중복되고, 상품명 변경 같은 이벤트가 발생할 때 과거 주문 데이터와 불일치가 생길 위험이 커집니다.

그래서 일반적으로는 다음처럼 역할을 분리합니다.

• orders: 주문번호, 주문일자, 고객, 주문상태 등 “주문 헤더”
• order_items: 주문번호 + 상품ID + 수량 + 판매단가 등 “주문 라인(상세)”
• products: 상품ID, 상품명, 카테고리, 기본가격 등 “상품 마스터”

정규화는 저장 관점에서 매우 유리하지만, 조회할 때는 “한 번에 보고 싶은 형태(주문 내역서)”를 만들기 위해 분리된 테이블을 다시 결합해야 합니다. 이때 사용하는 도구가 JOIN입니다.

2) 관계(relationship) 이해: PK/FK와 1:N, N:M

JOIN을 잘하려면 문법보다 먼저 “관계”를 이해해야 합니다. 관계형 데이터베이스에서 관계는 주로 기본키(PK)와 외래키(FK)로 표현됩니다.

• PK(Primary Key): 테이블에서 행을 유일하게 식별하는 키
• FK(Foreign Key): 다른 테이블의 PK를 참조하는 컬럼

관계 요약 표

주문 내역서 실습에서 사용하는 3개 테이블의 관계를 표로 정리하면 다음과 같습니다.

테이블	주요 역할	PK 예시	FK 예시	관계
orders	주문 헤더(주문 1건)	order_id	-	orders 1 : N order_items
order_items	주문 라인(주문 내 상품 1줄)	(order_id, line_no) 또는 order_item_id	order_id, product_id	브릿지(중간) 테이블
products	상품 마스터	product_id	-	products 1 : N order_items

핵심은 orders와 products가 직접 1:N으로 연결되는 것이 아니라, 주문상세(order_items)가 중간에서 “주문-상품”을 이어준다는 점입니다. 결과적으로 orders ↔ products는 N:M 관계가 되고, 이를 풀기 위해 order_items 같은 브릿지 테이블이 필요합니다.

3) INNER JOIN 핵심 개념과 동작 방식

INNER JOIN은 두 테이블을 연결할 때 ON 조건으로 매칭되는 행만 결과에 남깁니다. 쉽게 말해 “교집합”입니다. 주문상세에 있는 order_id가 orders에 없거나(고아 레코드), product_id가 products에 없으면(삭제/미등록 등) INNER JOIN 결과에서 해당 행은 보이지 않습니다.

따라서 INNER JOIN을 쓸 때는 “결과가 사라졌다”가 아니라 “매칭되지 않는 데이터가 있다”를 의심해야 합니다. 이 특성 때문에 INNER JOIN은 데이터 일관성이 보장될 때 가장 깔끔한 결과를 내고, 반대로 누락이 있을 수 있는 상황에서는 LEFT JOIN 같은 대안도 검토합니다(이번 회차는 INNER JOIN에 집중합니다).

4) ON 조건 설계 원칙: “연결”과 “필터” 분리

초보 단계에서 가장 흔한 실수는 ON/WHERE의 역할을 섞어 쓰거나, JOIN 조건을 빠뜨려서 의도치 않은 결과(카테시안 곱, 행 폭증)를 만드는 것입니다. 다음 원칙을 기준으로 설계하면 안정적입니다.

• ON: 관계를 표현하는 “키 매칭”을 넣습니다. 보통 PK = FK 형태입니다.
• WHERE: 조회 조건(기간/고객/상태/카테고리 등) 필터를 넣습니다.
• 추가 매칭 조건: 동일 키로 여러 의미가 섞인 경우(예: tenant_id, shop_id 등)는 ON에 함께 포함해 “같은 범위에서만 연결”되도록 합니다.

-- 권장 패턴: ON에는 관계(키), WHERE에는 필터(조건)
SELECT
  o.order_id,
  o.order_date,
  oi.product_id,
  p.product_name,
  oi.quantity,
  oi.unit_price
FROM orders o
INNER JOIN order_items oi
  ON o.order_id = oi.order_id
INNER JOIN products p
  ON oi.product_id = p.product_id
WHERE o.order_date >= DATE '2025-01-01'
  AND o.order_status = 'PAID';

위 예시는 관계는 order_id와 product_id로 연결하고, 조회 목적(결제 완료 주문, 기간)은 WHERE에 둔 형태입니다. 이렇게 분리하면 나중에 조건이 늘어나도 “연결이 바뀐 것인지, 조회 범위가 바뀐 것인지”를 빠르게 구분할 수 있습니다.

또 하나 중요한 팁은 별칭(alias)을 적극적으로 사용하는 것입니다. 조인이 늘어날수록 같은 이름의 컬럼(order_id 등)이 여러 테이블에 등장하므로, o.order_id처럼 명시해야 실수와 모호성을 줄일 수 있습니다.

5) 실습 스키마: 주문·주문상세·상품

아래는 학습용 예시 스키마입니다. 실제 운영 DB와 컬럼명이 다를 수 있으니, 개념과 관계(키)를 중심으로 보시면 됩니다.

-- 주문(헤더)
CREATE TABLE orders (
  order_id      BIGINT PRIMARY KEY,
  customer_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),
  list_price    DECIMAL(10,2) NOT NULL
);

-- 주문상세(라인아이템) - 브릿지
CREATE TABLE order_items (
  order_item_id BIGINT PRIMARY KEY,
  order_id      BIGINT NOT NULL,
  product_id    BIGINT NOT NULL,
  quantity      INT NOT NULL,
  unit_price    DECIMAL(10,2) NOT NULL,
  CONSTRAINT fk_order_items_orders
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
  CONSTRAINT fk_order_items_products
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

여기서 핵심은 order_items가 orders와 products를 동시에 참조한다는 점입니다. 이 구조를 이해하면 “주문 내역서”는 결국 orders → order_items → products 순서로 조인해서 만든다는 그림이 머릿속에 잡힙니다.

6) 실습: 주문 내역서 쿼리 만들기(라인 + 합계)

주문 내역서(Invoice/Order Statement)는 보통 “주문 헤더 정보 + 라인아이템 목록 + 금액 계산”을 한 결과로 보여줍니다. SQL에서는 한 번의 SELECT로 라인 목록을 만들고, 필요하면 합계를 같이 계산합니다.

코드 예시: 라인아이템 기반 주문 내역서

-- (1) 라인아이템 중심: 주문 내역서(상세행) 만들기
-- 포인트: 라인 금액(line_total) = quantity * unit_price
SELECT
  o.order_id,
  o.order_date,
  o.customer_id,
  o.order_status,
  oi.order_item_id,
  oi.product_id,
  p.product_name,
  p.category,
  oi.quantity,
  oi.unit_price,
  (oi.quantity * oi.unit_price) AS line_total
FROM orders o
INNER JOIN order_items oi
  ON o.order_id = oi.order_id
INNER JOIN products p
  ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN DATE '2025-01-01' AND DATE '2025-12-31'
ORDER BY o.order_id, oi.order_item_id;

이 쿼리는 “내역서의 각 줄”을 만드는 형태입니다. 주문 1건이 여러 줄을 가지므로 결과는 주문 단위가 아니라 라인아이템 단위로 늘어납니다. 현업 리포트에서 흔히 보는 “상품별 수량/금액”이 바로 이 결과입니다.

다음은 주문 합계(order_total)를 함께 보여주는 방식입니다. DBMS에 따라 윈도우 함수가 가능하면 가장 직관적이며, 어렵게 느껴지면 GROUP BY로 주문 합계를 따로 만든 뒤 조인하는 방법도 있습니다.

-- (2) 윈도우 함수로 주문 합계까지 함께 표시(가능한 DBMS 기준)
SELECT
  o.order_id,
  o.order_date,
  o.customer_id,
  o.order_status,
  oi.order_item_id,
  p.product_name,
  oi.quantity,
  oi.unit_price,
  (oi.quantity * oi.unit_price) AS line_total,
  SUM(oi.quantity * oi.unit_price) OVER (PARTITION BY o.order_id) AS order_total
FROM orders o
INNER JOIN order_items oi
  ON o.order_id = oi.order_id
INNER JOIN products p
  ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN DATE '2025-01-01' AND DATE '2025-12-31'
ORDER BY o.order_id, oi.order_item_id;

SUM(...) OVER (PARTITION BY o.order_id)는 “같은 주문번호끼리 묶어서 합계를 계산하되, 라인별 행은 유지”합니다. 결과적으로 한 주문의 각 줄에 동일한 주문 합계가 반복 표기되어, 내역서 형태에 잘 맞습니다.

-- (3) 윈도우 함수가 어렵거나 지원이 약한 경우: 주문 합계를 별도 집계 후 조인
WITH order_totals AS (
  SELECT
    oi.order_id,
    SUM(oi.quantity * oi.unit_price) AS order_total
  FROM order_items oi
  GROUP BY oi.order_id
)
SELECT
  o.order_id,
  o.order_date,
  o.customer_id,
  o.order_status,
  oi.order_item_id,
  p.product_name,
  oi.quantity,
  oi.unit_price,
  (oi.quantity * oi.unit_price) AS line_total,
  ot.order_total
FROM orders o
INNER JOIN order_items oi
  ON o.order_id = oi.order_id
INNER JOIN products p
  ON oi.product_id = p.product_id
INNER JOIN order_totals ot
  ON o.order_id = ot.order_id
WHERE o.order_date BETWEEN DATE '2025-01-01' AND DATE '2025-12-31'
ORDER BY o.order_id, oi.order_item_id;

집계를 먼저 만든 뒤 조인하는 방식은 범용적입니다. 다만 CTE/서브쿼리를 한 번 더 쓰기 때문에 쿼리 구조가 길어질 수 있고, 성능은 인덱스/통계/데이터량에 따라 달라질 수 있습니다.

7) 자주 하는 실수와 검증 쿼리

INNER JOIN 학습에서 가장 중요한 것은 “정확히 연결했는지”를 확인하는 습관입니다. 아래 체크리스트를 자주 활용하시면 디버깅이 빨라집니다.

• JOIN 조건 누락: ON을 빼거나 잘못 써서 행이 폭증(카테시안 곱)합니다.
• 잘못된 키로 연결: product_id 대신 category로 연결하는 등, 유일하지 않은 컬럼을 조인 키로 쓰면 중복이 발생합니다.
• 고아 레코드(참조 불일치): order_items에 있는데 orders/products에 없는 데이터는 INNER JOIN 결과에서 사라집니다.
• 필터 위치 혼동: 관계 조건과 조회 조건을 섞으면 의도치 않게 결과가 줄거나 늘어납니다.

-- (A) 주문상세가 orders에 매칭되지 않는(고아) 데이터 찾기
SELECT oi.*
FROM order_items oi
LEFT JOIN orders o
  ON oi.order_id = o.order_id
WHERE o.order_id IS NULL;

-- (B) 주문상세가 products에 매칭되지 않는(고아) 데이터 찾기
SELECT oi.*
FROM order_items oi
LEFT JOIN products p
  ON oi.product_id = p.product_id
WHERE p.product_id IS NULL;

위 검증 쿼리는 INNER JOIN으로 조회했을 때 “왜 일부 라인이 안 보이는지”를 빠르게 확인하는 데 유용합니다. 실무에서는 이런 고아 데이터를 정리하거나, 비즈니스 룰에 따라 예외 처리(삭제, 보정, 대체 상품 등)를 설계합니다.

---

따라하기: 주문 내역서 만들기 실습 가이드

1. 테이블 관계를 먼저 그려보세요. orders(1) → order_items(N), products(1) → order_items(N) 구조가 머릿속에 들어오면 JOIN은 “그림을 SQL로 옮기는 작업”이 됩니다. 특히 order_items가 브릿지라는 사실이 핵심입니다.

2. 가장 작은 단위(라인아이템)부터 결과를 만드세요. 먼저 orders와 order_items를 INNER JOIN해 주문별 라인이 잘 붙는지 확인하고, 그 다음 products를 붙여 상품명이 나오는지 확인합니다. 한 번에 3개를 붙이기보다 단계적으로 확장하면 실수가 줄어듭니다.

3. ON에는 PK=FK 매칭만 넣고, WHERE에 조회 범위를 넣으세요. 예: ON o.order_id = oi.order_id, WHERE o.order_date BETWEEN ... 처럼 역할을 분리하면, 나중에 조건이 늘어도 구조가 흔들리지 않습니다.

4. 라인 금액과 주문 합계를 모두 만들어 보세요. line_total은 (quantity * unit_price)로 바로 계산하고, order_total은 윈도우 함수(PARTITION BY) 또는 집계 후 조인(CTE) 방식 중 하나를 선택해 구현합니다.

5. 검증 쿼리로 데이터 일관성을 확인하세요. 고아 레코드가 있으면 INNER JOIN 결과가 빠질 수 있습니다. LEFT JOIN + IS NULL 패턴으로 누락을 확인한 뒤, 데이터/비즈니스 룰 관점에서 원인을 정리해보시면 학습 깊이가 크게 올라갑니다.

추가로 생각해볼 점

• 주문 내역서가 커지면(기간이 길고 데이터가 많을수록) 조인 키(order_id, product_id)에 인덱스가 있는지, 그리고 WHERE 조건(날짜/상태)이 인덱스를 타는지 점검하는 습관이 중요합니다.
• 실무에서는 “주문 당시 판매단가”가 products의 list_price와 다를 수 있습니다. 따라서 단가는 products가 아니라 order_items에 저장하고(현재 예시처럼), JOIN은 상품명/카테고리 같은 마스터 정보 보강에 쓰는 경우가 많습니다.
• 상품이 삭제되거나 이름이 바뀌는 정책이 있다면, 과거 주문 내역의 재현성을 위해 상품 스냅샷(주문 시점 상품명)을 주문상세에 일부 저장하는 설계도 고려 대상입니다(정규화와 감사/재현성의 균형).

---

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