관계 설계와 조인 이해하기

테이블을 나누는 이유

관계형 데이터베이스의 핵심은 데이터를 한 테이블에 모두 넣는 것이 아닙니다. 데이터를 알맞은 단위로 나누고, 필요할 때 정확히 연결하는 것입니다.

예를 들어 쇼핑몰 데이터를 하나의 테이블에 모두 저장한다고 생각해 봅시다.

주문번호 | 회원이름 | 회원이메일 | 상품명 | 상품가격 | 주문수량

처음에는 단순해 보입니다. 하지만 같은 회원이 여러 번 주문하면 회원 이름과 이메일이 계속 반복됩니다. 상품 가격이 바뀌면 예전 주문의 가격까지 같이 바뀌어야 하는지 고민해야 합니다. 한 주문에 상품이 여러 개 들어가면 같은 주문번호가 여러 줄 반복됩니다.

이런 문제를 줄이기 위해 데이터를 나눕니다.

• 회원 정보는 members 테이블에 저장합니다.

• 상품 정보는 products 테이블에 저장합니다.

• 주문 정보는 orders 테이블에 저장합니다.

• 주문에 담긴 상품 정보는 order_items 테이블에 저장합니다.

테이블을 나누면 중복이 줄고, 데이터의 의미가 분명해집니다. 대신 나뉜 데이터를 다시 연결하는 기술이 필요합니다. 그 역할을 하는 것이 관계와 조인입니다.

관계의 세 가지 형태

테이블 사이의 관계는 크게 세 가지로 나눌 수 있습니다.

일대일 관계

일대일 관계는 한 행이 다른 테이블의 한 행과만 연결되는 관계입니다.

예를 들어 회원 기본 정보와 회원 상세 정보를 나눈다고 합시다.

• members: 이름, 이메일, 가입일

• member_profiles: 전화번호, 주소, 추가 소개

한 회원은 하나의 상세 정보만 가집니다. 하나의 상세 정보도 한 회원에게만 속합니다. 이런 관계가 일대일입니다.

일대일 관계는 자주 함께 조회하지 않는 정보를 분리할 때 사용합니다. 예를 들어 모든 회원 목록을 볼 때 주소까지 항상 필요하지 않다면, 주소를 별도 테이블로 나눌 수 있습니다.

일대다 관계

일대다 관계는 한 행이 다른 테이블의 여러 행과 연결되는 관계입니다.

회원과 주문의 관계가 대표적입니다.

• 한 회원은 여러 주문을 할 수 있습니다.

• 하나의 주문은 한 회원에게 속합니다.

이때 members는 일 쪽이고, orders는 다 쪽입니다. 외래키는 보통 다 쪽 테이블에 둡니다. 따라서 orders 테이블에 member_id 열을 만들어 어떤 회원의 주문인지 저장합니다.

members.id  ──<  orders.member_id

──< 표시는 한 회원이 여러 주문과 연결될 수 있다는 뜻으로 이해하면 됩니다.

다대다 관계

다대다 관계는 양쪽 모두 여러 행과 연결될 수 있는 관계입니다.

주문과 상품의 관계를 봅시다.

• 하나의 주문에는 여러 상품이 담길 수 있습니다.

• 하나의 상품은 여러 주문에 포함될 수 있습니다.

이 관계는 orders 테이블과 products 테이블만으로는 깔끔하게 표현하기 어렵습니다. 그래서 중간 테이블을 둡니다. 이 중간 테이블을 연결 테이블 또는 교차 테이블이라고 부릅니다.

여기서는 order_items 테이블을 사용합니다.

orders.id  ──<  order_items.order_id
products.id  ──<  order_items.product_id

order_items에는 주문 번호, 상품 번호, 수량, 주문 당시 가격을 저장합니다. 이렇게 하면 한 주문에 여러 상품을 담을 수 있고, 한 상품이 여러 주문에 포함되는 것도 표현할 수 있습니다.

실습용 테이블 만들기

이제 회원, 상품, 주문 테이블을 만들어 보겠습니다. 앞 장에서 배운 기본키와 데이터 타입을 실제 관계 설계에 연결해 보겠습니다.

이미 같은 이름의 테이블이 있다면 실습용으로 새 데이터베이스를 만들거나, 아래 DROP TABLE 문을 실행해 초기화할 수 있습니다. 외래키가 있는 테이블은 자식 테이블부터 삭제해야 합니다.

USE mysql_book;

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS member_profiles;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS members;

먼저 회원 테이블을 만듭니다.

CREATE TABLE members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

회원 상세 정보 테이블입니다. member_profiles.member_id는 기본키이면서 외래키입니다. 이렇게 하면 한 회원당 상세 정보가 하나만 만들어집니다.

CREATE TABLE member_profiles (
    member_id INT PRIMARY KEY,
    phone VARCHAR(20),
    address VARCHAR(200),
    FOREIGN KEY (member_id) REFERENCES members(id)
);

상품 테이블입니다.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price INT NOT NULL,
    stock INT NOT NULL DEFAULT 0
);

주문 테이블입니다. member_id는 어떤 회원의 주문인지 가리킵니다.

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    member_id INT NOT NULL,
    ordered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'ORDERED',
    FOREIGN KEY (member_id) REFERENCES members(id)
);

마지막으로 주문 상품 테이블입니다. 하나의 주문에 어떤 상품이 몇 개 들어갔는지 저장합니다.

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price INT NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

order_items.price는 상품의 현재 가격이 아니라 주문 당시 가격입니다. 상품 가격은 나중에 바뀔 수 있습니다. 하지만 과거 주문 금액은 바뀌면 안 됩니다. 그래서 주문 상세에는 당시 가격을 따로 저장하는 경우가 많습니다.

외래키로 잘못된 연결 막기

외래키는 다른 테이블의 기본키를 참조하는 열입니다. 외래키 제약 조건을 걸면 존재하지 않는 데이터를 참조하는 실수를 막을 수 있습니다.

예를 들어 orders.member_id는 members.id를 참조합니다. 따라서 존재하지 않는 회원 번호로 주문을 만들 수 없습니다.

INSERT INTO orders (member_id, status)
VALUES (999, 'ORDERED');

members 테이블에 id = 999인 회원이 없다면 이 문장은 실패합니다. 이것이 외래키의 중요한 역할입니다.

외래키는 데이터를 자동으로 조인해 주지는 않습니다. 외래키는 잘못된 참조를 막는 장치입니다. 실제로 여러 테이블의 데이터를 함께 읽으려면 JOIN을 사용해야 합니다.

외래키를 사용할 때는 다음을 기억하세요.

• 참조하는 열과 참조되는 열의 데이터 타입은 같아야 합니다.

• 부모 테이블의 행이 먼저 있어야 자식 테이블의 행을 만들 수 있습니다.

• 자식 테이블에서 참조 중인 부모 행은 마음대로 삭제하기 어렵습니다.

• ON DELETE CASCADE를 사용하면 부모 행 삭제 시 자식 행도 함께 삭제됩니다.

위 예제에서 order_items.order_id에는 ON DELETE CASCADE를 붙였습니다. 주문이 삭제되면 해당 주문의 상품 목록도 함께 삭제되도록 한 것입니다. 하지만 회원을 삭제할 때 주문까지 자동 삭제되도록 설정하지는 않았습니다. 주문 기록은 중요한 데이터일 수 있기 때문입니다.

자동 삭제 옵션은 편리하지만 위험할 수 있습니다. 실제 서비스에서는 어떤 데이터를 함께 삭제해도 되는지 신중하게 결정해야 합니다.

예제 데이터 입력하기

조인을 연습할 수 있도록 데이터를 넣어 보겠습니다.

INSERT INTO members (name, email) VALUES
('민준', '[email protected]'),
('서연', '[email protected]'),
('지우', '[email protected]');

INSERT INTO member_profiles (member_id, phone, address) VALUES
(1, '010-1111-1111', '서울시 마포구'),
(2, '010-2222-2222', '부산시 해운대구');

INSERT INTO products (name, price, stock) VALUES
('무선 마우스', 25000, 100),
('기계식 키보드', 89000, 50),
('USB 허브', 18000, 80);

INSERT INTO orders (member_id, status) VALUES
(1, 'PAID'),
(1, 'ORDERED'),
(2, 'PAID');

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 25000),
(1, 3, 2, 18000),
(2, 2, 1, 89000),
(3, 1, 2, 25000);

현재 데이터에서는 민준이 주문을 두 번 했고, 서연이 주문을 한 번 했습니다. 지우는 아직 주문이 없습니다. 이 차이가 INNER JOIN과 LEFT JOIN을 이해하는 좋은 예제가 됩니다.

INNER JOIN으로 양쪽에 모두 있는 데이터 조회하기

INNER JOIN은 양쪽 테이블에 모두 연결되는 데이터만 조회합니다.

회원과 주문을 함께 조회해 보겠습니다.

SELECT
    m.id AS member_id,
    m.name,
    o.id AS order_id,
    o.status,
    o.ordered_at
FROM members AS m
INNER JOIN orders AS o
    ON m.id = o.member_id
ORDER BY m.id, o.id;

핵심은 ON m.id = o.member_id입니다. 이 조건이 회원 테이블과 주문 테이블을 연결합니다.

결과에는 주문이 있는 회원만 나옵니다. 민준과 서연은 주문이 있으므로 조회됩니다. 지우는 회원 테이블에는 있지만 주문 테이블에 연결된 행이 없으므로 결과에서 제외됩니다.

INNER JOIN은 다음 질문에 어울립니다.

• 주문한 회원 목록을 보고 싶다.

• 실제 주문이 있는 데이터만 보고 싶다.

• 상품이 포함된 주문 상세만 보고 싶다.

조인할 때는 테이블 별칭을 자주 사용합니다.

FROM members AS m

여기서 m은 members의 짧은 이름입니다. SQL이 길어질수록 별칭을 쓰면 읽기 쉬워집니다.

여러 테이블 조인하기

조인은 두 테이블에만 사용하는 것이 아닙니다. 필요한 만큼 여러 테이블을 연결할 수 있습니다.

주문 번호, 회원 이름, 상품명, 수량, 주문 당시 가격을 함께 조회해 보겠습니다.

SELECT
    o.id AS order_id,
    m.name AS member_name,
    p.name AS product_name,
    oi.quantity,
    oi.price,
    oi.quantity * oi.price AS line_total
FROM orders AS o
INNER JOIN members AS m
    ON o.member_id = m.id
INNER JOIN order_items AS oi
    ON o.id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.id
ORDER BY o.id, p.name;

이 쿼리는 네 개의 테이블을 연결합니다.

• orders와 members를 연결해 주문한 회원을 찾습니다.

• orders와 order_items를 연결해 주문에 담긴 상품 목록을 찾습니다.

• order_items와 products를 연결해 상품 이름을 가져옵니다.

조인은 복잡해 보이지만 원리는 단순합니다. 한 번에 모든 테이블을 이해하려 하지 말고, 두 테이블씩 연결 조건을 확인하면 됩니다.

orders.member_id      = members.id
order_items.order_id  = orders.id
order_items.product_id = products.id

조인을 작성할 때 가장 중요한 것은 연결 기준입니다. ON 조건이 틀리면 결과도 틀립니다.

LEFT JOIN으로 누락된 관계까지 조회하기

LEFT JOIN은 왼쪽 테이블의 데이터를 모두 보여 줍니다. 오른쪽 테이블에 연결되는 데이터가 있으면 함께 보여 주고, 없으면 NULL로 표시합니다.

이번에는 모든 회원과 주문 정보를 조회해 보겠습니다.

SELECT
    m.id AS member_id,
    m.name,
    o.id AS order_id,
    o.status
FROM members AS m
LEFT JOIN orders AS o
    ON m.id = o.member_id
ORDER BY m.id, o.id;

이 결과에는 지우도 나옵니다. 지우는 아직 주문이 없으므로 order_id와 status가 NULL로 표시됩니다.

여기서 NULL은 값이 0이라는 뜻이 아닙니다. 연결된 주문 행이 없다는 뜻입니다.

LEFT JOIN은 다음 질문에 어울립니다.

• 주문이 없는 회원도 포함해서 전체 회원을 보고 싶다.

• 상품이 한 번도 주문되지 않았는지 확인하고 싶다.

• 누락된 관계를 찾고 싶다.

주문이 없는 회원만 찾고 싶다면 다음처럼 작성합니다.

SELECT
    m.id,
    m.name,
    m.email
FROM members AS m
LEFT JOIN orders AS o
    ON m.id = o.member_id
WHERE o.id IS NULL;

LEFT JOIN으로 전체 회원을 가져온 뒤, 주문 쪽의 id가 NULL인 행만 고르는 방식입니다.

LEFT JOIN에서 WHERE 조건 주의하기

LEFT JOIN을 사용할 때 자주 하는 실수가 있습니다. 오른쪽 테이블 조건을 WHERE에 잘못 쓰는 것입니다.

다음 쿼리를 보겠습니다.

SELECT
    m.name,
    o.id AS order_id,
    o.status
FROM members AS m
LEFT JOIN orders AS o
    ON m.id = o.member_id
WHERE o.status = 'PAID';

이 쿼리는 결제 완료 주문만 조회합니다. 그런데 주문이 없는 회원은 결과에서 사라집니다. 왜냐하면 주문이 없는 회원의 o.status는 NULL이고, NULL = 'PAID' 조건을 만족하지 않기 때문입니다.

모든 회원을 유지하면서 결제 완료 주문만 붙이고 싶다면 조건을 ON 절에 넣습니다.

SELECT
    m.name,
    o.id AS paid_order_id,
    o.status
FROM members AS m
LEFT JOIN orders AS o
    ON m.id = o.member_id
   AND o.status = 'PAID'
ORDER BY m.id;

이렇게 하면 모든 회원이 결과에 남습니다. 결제 완료 주문이 있는 회원은 주문 정보가 표시되고, 없는 회원은 주문 쪽 열이 NULL로 표시됩니다.

정리하면 다음과 같습니다.

• 오른쪽 테이블을 필터링하면서 왼쪽 데이터를 유지하려면 조건을 ON에 둡니다.

• 조인 결과 전체를 필터링하려면 조건을 WHERE에 둡니다.

이 차이는 실무에서 매우 중요합니다.

조인과 집계를 함께 사용하기

앞 장에서 배운 GROUP BY와 조인을 함께 사용하면 더 유용한 결과를 만들 수 있습니다.

회원별 주문 수를 구해 보겠습니다.

SELECT
    m.id,
    m.name,
    COUNT(o.id) AS order_count
FROM members AS m
LEFT JOIN orders AS o
    ON m.id = o.member_id
GROUP BY m.id, m.name
ORDER BY m.id;

여기서는 LEFT JOIN을 사용했습니다. 주문이 없는 회원도 결과에 포함하기 위해서입니다.

또 하나 중요한 점은 COUNT(*)가 아니라 COUNT(o.id)를 사용했다는 것입니다. LEFT JOIN에서는 주문이 없는 회원도 한 행으로 나타납니다. 이때 COUNT(*)를 쓰면 주문이 없는 회원도 1로 계산될 수 있습니다. 반면 COUNT(o.id)는 o.id가 NULL인 경우 세지 않습니다.

즉, 관계가 없음을 정확히 0으로 표현하려면 조인 방식과 집계 대상을 함께 생각해야 합니다.

관계 설계의 기본 순서

테이블 관계를 설계할 때는 다음 순서로 생각하면 좋습니다.

첫째, 저장할 대상을 찾습니다. 회원, 주문, 상품처럼 독립적으로 관리할 대상을 테이블 후보로 봅니다.

둘째, 각 테이블의 기본키를 정합니다. 보통 id 같은 숫자 기본키를 사용합니다.

셋째, 문장으로 관계를 표현합니다.

• 회원은 주문을 한다.

• 주문은 회원에게 속한다.

• 주문은 여러 상품을 포함한다.

• 상품은 여러 주문에 포함될 수 있다.

넷째, 관계의 종류를 판단합니다.

• 회원과 주문은 일대다입니다.

• 주문과 상품은 다대다입니다.

• 회원과 회원 상세 정보는 일대일입니다.

다섯째, 외래키 위치를 정합니다.

• 일대다에서는 다 쪽 테이블에 외래키를 둡니다.

• 다대다에서는 연결 테이블을 만들고, 양쪽 기본키를 외래키로 둡니다.

• 일대일에서는 한쪽 테이블의 기본키를 외래키로 함께 쓰는 방식이 자주 사용됩니다.

여섯째, 실제 조회 쿼리를 생각합니다. 설계가 좋아도 자주 필요한 조회가 지나치게 복잡하다면 다시 검토해야 합니다.

좋은 설계는 저장과 조회를 함께 고려합니다.

자주 하는 실수

모든 정보를 한 테이블에 넣기

처음에는 편해 보이지만 중복이 빠르게 늘어납니다. 회원 이메일, 상품명, 상품 가격이 주문마다 반복되면 수정하기 어렵고 오류가 생기기 쉽습니다.

다대다 관계를 쉼표로 저장하기

예를 들어 주문 테이블에 상품 번호를 이렇게 저장하는 방식은 피해야 합니다.

product_ids = '1,2,3'

이 방식은 검색, 집계, 외래키 설정이 모두 어려워집니다. 다대다 관계는 연결 테이블로 풀어야 합니다.

JOIN 조건을 빠뜨리기

다음과 같은 쿼리는 위험합니다.

SELECT *
FROM members AS m
INNER JOIN orders AS o;

ON 조건이 없으면 회원과 주문이 잘못 조합된 결과가 나올 수 있습니다. 조인할 때는 항상 어떤 열과 어떤 열을 연결하는지 확인해야 합니다.

같은 이름의 열을 구분하지 않기

여러 테이블에 id, name, created_at 같은 열이 함께 있을 수 있습니다. 조인에서는 어느 테이블의 열인지 명확히 적는 습관이 좋습니다.

SELECT
    members.id,
    members.name,
    orders.id
FROM members
INNER JOIN orders
    ON members.id = orders.member_id;

실무에서는 별칭을 사용해 더 간결하게 씁니다.

SELECT
    m.id AS member_id,
    m.name AS member_name,
    o.id AS order_id
FROM members AS m
INNER JOIN orders AS o
    ON m.id = o.member_id;

외래키 타입을 다르게 만들기

members.id가 INT라면 orders.member_id도 INT여야 합니다. 타입이 다르면 외래키를 만들 수 없거나, 조인 성능과 안정성에 문제가 생길 수 있습니다.

이번 장 정리

관계형 데이터베이스는 테이블을 잘 나누고 다시 연결하는 방식으로 데이터를 관리합니다.

일대일 관계는 한 행이 다른 한 행과 연결됩니다. 일대다 관계는 한 행이 여러 행과 연결됩니다. 다대다 관계는 중간 연결 테이블로 풀어야 합니다.

외래키는 잘못된 참조를 막는 제약 조건입니다. 존재하지 않는 회원의 주문을 만들지 못하게 하고, 관계의 무결성을 지켜 줍니다.

INNER JOIN은 양쪽 테이블에 모두 연결된 데이터만 조회합니다. 주문한 회원처럼 실제 관계가 있는 데이터만 보고 싶을 때 사용합니다.

LEFT JOIN은 왼쪽 테이블의 데이터를 모두 유지합니다. 주문이 없는 회원처럼 누락된 관계를 찾거나 포함해야 할 때 사용합니다. 이때 오른쪽 테이블의 값이 없으면 NULL로 표시됩니다.

관계형 데이터베이스의 핵심은 테이블을 잘 나누는 것에서 끝나지 않습니다. 필요한 순간 정확히 연결해 원하는 결과를 읽어 내는 능력이 중요합니다.

연습 문제

다음 쿼리를 직접 작성해 보세요.

1. 모든 상품과 해당 상품이 주문된 내역을 LEFT JOIN으로 조회해 보세요. 한 번도 주문되지 않은 상품도 결과에 포함해야 합니다.

2. 회원별 총 주문 금액을 구해 보세요. orders, order_items, members를 조인해야 합니다.

3. 주문 번호별 총 상품 수량을 구해 보세요.

4. 주문이 없는 회원만 조회해 보세요.

5. 존재하지 않는 product_id로 order_items에 데이터를 입력해 보고 어떤 오류가 나는지 확인해 보세요.

이 연습을 통해 관계, 외래키, 조인이 서로 어떻게 연결되는지 감각을 익힐 수 있습니다.