MariaDB 강좌 / 여러 테이블 JOIN하기: 중복 행과 집계 오류 확인

여러 테이블을 JOIN할 때는 결합 후 한 행의 단위가 무엇인지 확인한 뒤 집계해야 합니다. 주문과 상세를 연결하면 주문 정보가 상세 수만큼 반복되므로, 주문 배송비 같은 값은 주문별로 한 행이 되도록 먼저 집계하거나 별도 기준에서 계산해야 합니다.

고객·주문·상세·상품의 행 단위 구분하기

고객 이름, 주문 날짜, 상품 이름, 수량을 한 화면에 보이려면 네 테이블을 연결합니다. 마지막 결과 한 행은 주문 한 건이 아니라 주문의 상품 상세 하나입니다. 이 단위 차이를 놓치면 건수뿐 아니라 배송비·할인처럼 주문에 한 번만 적용되는 금액도 중복 합산됩니다. 기준 행 유지 여부는 LEFT JOIN으로 연결되지 않은 행도 조회하기에서 정하고, 같은 행 단위 검사를 여러 보고서에 적용하는 과정은 조회 종합 실습: 월별 매출·고객별 구매·미판매 상품에서 교차 검증합니다.

단가와 배송비가 있는 주문 데이터 준비하기

학습용 MariaDB 서버의 shopcourse에 course_user로 접속합니다. 명령줄 클라이언트와 UTF-8 SQL 파일 실행 방법대로 코드를 UTF-8 파일에 저장하고 SOURCE C:/mariadb-lab/lesson.sql;처럼 실제 파일 경로로 실행할 수 있습니다. SQL 문은 운영체제 터미널이 아닌 MariaDB 연결 안에서 실행합니다.

먼저 SELECT VERSION(), DATABASE();와 SHOW TABLES;로 대상 서버·DB·이름 중복을 확인합니다. 아래 c28_customers, c28_products, c28_orders, c28_items는 이 글의 독립된 연습용 이름입니다. 이미 존재하면 정의와 행을 확인하고, 보관할 자료가 없는 이 글의 실습 테이블임이 확실할 때만 마지막 절의 정리 순서로 지운 뒤 다시 만듭니다. 기존 강좌의 동명 없는 테이블이나 운영 데이터는 삭제하지 않습니다.

USE shopcourse;
CREATE TABLE c28_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c28_order_customer FOREIGN KEY (customer_id)
  REFERENCES c28_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c28_item_order FOREIGN KEY (order_id)
  REFERENCES c28_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c28_item_product FOREIGN KEY (product_id)
  REFERENCES c28_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c28_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c28_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c28_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c28_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

paid 주문은 101·102·103번 세 건입니다. 상세는 각각 2·1·2개이므로 paid 상세 행은 다섯 개이고 주문마다 배송비는 1000입니다. 이 작은 수를 기준으로 JOIN 뒤 값이 몇 번 반복되는지 확인하겠습니다.

각 단계의 키를 명시해 네 테이블 연결하기

SELECT o.order_id,c.customer_name,p.product_name,i.quantity,i.unit_price,
 i.quantity*i.unit_price AS line_total
FROM c28_orders o JOIN c28_customers c ON c.customer_id=o.customer_id
JOIN c28_items i ON i.order_id=o.order_id
JOIN c28_products p ON p.product_id=i.product_id
WHERE o.status='paid' ORDER BY o.order_id,i.product_id;

101번의 상품 금액은 7000+4500=11500, 102번은 8000, 103번은 3500+16000=19500입니다. 전체 상품 금액은 39000입니다. 상품 이름은 products에서 읽되 금액에는 주문 상세의 unit_price를 사용합니다. 현재 상품 가격을 수정해도 과거 주문 금액의 의미가 바뀌지 않아야 하기 때문입니다.

늘어난 행에서 주문 금액을 중복 합산하는 오류

SELECT COUNT(*) AS joined_rows,COUNT(DISTINCT o.order_id) AS orders_count,
 SUM(o.shipping_fee) AS duplicated_shipping,SUM(i.quantity*i.unit_price) AS sales
FROM c28_orders o JOIN c28_items i ON i.order_id=o.order_id
WHERE o.status='paid';

결과는 joined_rows=5, orders_count=3, duplicated_shipping=5000.00, sales=39000.00입니다. 배송비 정답은 주문 세 건의 3000.00이므로 2000.00이 중복되었습니다. COUNT(DISTINCT order_id)는 주문 건수를 고치는 데 적합하지만 다른 금액의 중복까지 함께 고쳐 주지는 않습니다.

SUM(DISTINCT shipping_fee)를 쓰면 같은 1000이라는 값이 하나로 모여 1000이 됩니다. 값이 같은 서로 다른 주문을 구분하지 못하기 때문에 주문별 한 번 합산과 같지 않습니다. 잘못된 합계에 DISTINCT를 임시로 붙이는 방법을 일반적인 해결책으로 사용하지 않습니다.

상세를 주문별로 요약한 뒤 다시 연결하기

SELECT COUNT(*) AS orders_count,SUM(o.shipping_fee) AS shipping_total,
 SUM(d.goods_total) AS goods_total
FROM c28_orders o
JOIN (SELECT order_id,SUM(quantity*unit_price) AS goods_total
 FROM c28_items GROUP BY order_id) d ON d.order_id=o.order_id
WHERE o.status='paid';

안쪽 조회는 주문당 goods_total 한 행을 만듭니다. 이를 주문과 연결하면 결과도 주문당 한 행이 되어 orders_count=3, shipping_total=3000.00, goods_total=39000.00입니다. 새로운 상세 행이 늘어도 배송비를 상세 개수만큼 합산하지 않습니다.

주문에 상세가 없는 상태도 보여 줘야 한다면 LEFT JOIN과 COALESCE 등을 업무 정의에 맞게 사용해야 합니다. 이 예제는 상세가 있는 paid 주문을 대상으로 합니다. 모든 집계를 한 거대한 JOIN에 넣기보다 각 측정값의 기준 단위에 맞게 요약하는 것이 결과를 검증하기 쉽습니다. JOIN 구문과 연결 방식을 확인하세요.

행 증가를 단계별로 확인하고 정리하기

테이블을 하나씩 붙이며 기본키와 행 수를 확인합니다. 한 단계에서 기대보다 많이 늘어나면 연결 열의 고유성과 빠진 키를 살펴보세요. 상세 두 테이블을 주문에 각각 붙이면 상세 간 곱이 생길 수도 있으므로 각 상세를 먼저 요약할지 검토합니다. SELECT 결과를 작은 주문별 계산과 대조한 뒤 실제 보고서에 적용합니다.

반복하기 전 현재 DB와 실제 정의·행을 확인하고 필요한 자료를 백업합니다. 보관할 데이터가 없는 이 글의 전용 실습 객체만 아래 순서로 정리합니다. 외래키가 있으면 자식부터 지웁니다. DROP TABLE은 구조와 데이터를 모두 없애며 일반적인 ROLLBACK으로 복원되지 않으므로 취소 가능한 DML 실습과 분리합니다.

DROP TABLE shopcourse.c28_items;
DROP TABLE shopcourse.c28_orders;
DROP TABLE shopcourse.c28_products;
DROP TABLE shopcourse.c28_customers;