[스터디12] 03. 테이블 관계와 JOIN - PostgreSQL

목차

 

3장. 테이블 관계와 JOIN

3.1 관계 훑어보기

데이터베이스에서 테이블 간의 관계는 크게 세 가지로 분류된다.

• 일대다 관계 (One-to-Many): 한 테이블의 한 레코드가 다른 테이블의 여러 레코드와 연결
• 일대일 관계 (One-to-One): 한 테이블의 한 레코드가 다른 테이블의 정확히 한 레코드와 연결
• 다대다 관계 (Many-to-Many): 한 테이블의 여러 레코드가 다른 테이블의 여러 레코드와 연결

관계 구현 방법은 다음과 같다.

• 일대다와 일대일은 외래키로 직접 구현한다.
• 다대다는 중간 테이블(연결 테이블)을 통해 구현한다.

3.2 일대다 관계

• 부모 테이블의 한 레코드가 자식 테이블의 여러 레코드를 가질 수 있다.
• 예: 저자(authors) 한 명이 여러 도서(books)를 집필한다.

SELECT a.id, a.familyname, COUNT(b.id) AS book_count
FROM authors a
LEFT JOIN books b ON a.id = b.authorid
GROUP BY a.id;

▲ 일대다 관계 (저자→도서)

3.2.1 JOIN 카운트하기

관계를 분석할 때는 다음과 같은 카운트를 확인한다.

-- 자식 중 부모가 존재하는 경우
SELECT count(*) FROM books WHERE authorid IS NOT NULL;

-- 부모 없는 자식
SELECT count(*) FROM books WHERE authorid IS NULL;

-- 자식 없는 부모
SELECT count(*) FROM authors
WHERE id NOT IN (SELECT authorid FROM books WHERE authorid IS NOT NULL);

▲ 자식 중 부모가 존재하는 경우

▲ 부모 없는 자식

▲ 자식 없는 부모

3.2.2 NOT IN의 특이한 점

NOT IN 절에서 NULL 값 처리에 주의해야 한다.

• NULL이 포함된 리스트와 NOT IN을 사용하면 결과가 비어버린다.
• 해결책: NOT EXISTS 또는 IS DISTINCT FROM 사용한다.

-- 문제가 있는 쿼리
SELECT * FROM customers WHERE state NOT IN ('VIC','QLD', NULL);

-- 올바른 쿼리
SELECT * FROM customers WHERE state NOT IN ('VIC','QLD');

▲ 문제가 있는 쿼리

▲ 올바른 쿼리

3.2.3 뷰(View) 만들기

자주 사용하는 JOIN 결과를 뷰로 저장하면 편리하다.

CREATE VIEW bookdetails AS
SELECT
  b.id,
  b.title,
  b.published,
  b.price,
  a.givenname,
  a.familyname
FROM books b
LEFT JOIN authors a ON b.authorid = a.id;

▲ 뷰 만들기

3.3 일대일 관계

• 한 테이블의 레코드가 다른 테이블의 정확히 한 레코드와 매칭된다.
• 주로 테이블 분할이나 선택적 속성 관리에 사용된다.

3.3.1 일대불확실 관계 (Optional 1:0..1)

모든 고객이 VIP는 아니지만, VIP는 반드시 고객이어야 하는 경우

-- 모든 고객과 VIP 정보 조회
SELECT c.*, v.*
FROM customers c
LEFT JOIN vip v ON c.id = v.id;

-- VIP 고객만 조회
SELECT c.*
FROM customers c
JOIN vip v ON c.id = v.id;

▲ 모든 고객과 VIP 정보 조회

▲ VIP 고객만 조회

3.4 여러 값

3.4.1 다대다 관계

중간 테이블(연결 테이블)을 통해 구현한다.

• 예: books ↔ bookgenres ↔ genres

SELECT b.id, b.title, g.genre
FROM bookgenres bg
JOIN books b ON bg.bookid = b.id
JOIN genres g ON bg.genreid = g.id;

▲ 다대다 관계

3.4.3 여러 값 요약하기

string_agg() 함수를 사용해 다대다 데이터를 한 줄로 요약할 수 있다.

WITH cte AS (
  SELECT b.id, b.title, g.genre
  FROM bookgenres bg
  JOIN books b ON bg.bookid = b.id
  JOIN genres g ON bg.genreid = g.id
)
SELECT
  id, title,
  string_agg(genre, ', ') AS genres
FROM cte
GROUP BY id, title;

▲ 여러 값 요약하기

3.4.4 JOIN 결합하기

복잡한 관계는 여러 JOIN을 조합해 해결한다.

SELECT
  b.id, b.title, g.genre, a.familyname
FROM authors a
RIGHT JOIN books b ON a.id = b.authorid
LEFT JOIN bookgenres bg ON b.id = bg.bookid
JOIN genres g ON bg.genreid = g.id;

▲ JOIN 결합하기

3.4.5 흔하게 생기는 다대다 관계

공동 저자 구조(한 책에 여러 저자, 한 저자가 여러 책을 쓰는 경우)

CREATE TABLE authorship (
  bookid INT REFERENCES multibooks(id),
  authorid INT REFERENCES multiauthors(id),
  PRIMARY KEY(authorid, bookid)
);

3.5 또 다른 다대다 관계의 예

한 판매에 여러 도서, 한 도서가 여러 판매에 포함되는 구조

CREATE TABLE saleitems (
  saleid INT REFERENCES sales(id),
  bookid INT REFERENCES books(id),
  quantity INT
);

3.6 관련 테이블에 데이터 삽입하기

3.6.1 도서 및 저자 추가하기

외래키 제약조건을 고려해 부모 → 자식 순으로 삽입한다.

-- 1. 저자 추가
INSERT INTO authors (givenname, familyname)
VALUES ('John', 'Doe');

-- 2. 도서 추가 (currval로 방금 생성된 저자 ID 참조)
INSERT INTO books (authorid, title, published, price)
VALUES (currval('authors_id_seq'), 'New Book', 2024, 20.0);

▲ 저자 추가

▲ 도서 추가

3.6.2 새로운 판매 내역 추가하기

판매와 판매 항목을 순차적으로 추가한다.

-- 1. 판매 기록 추가
INSERT INTO sales (customerid, total, ordered, shipped)
VALUES (1, 50.0, now(), now());

-- 2. 판매 항목 추가
INSERT INTO saleitems (saleid, bookid, quantity, price)
VALUES (currval('sales_id_seq'), 1303, 1, 15.0);

▲ 판매 기록 추가

▲ 판매 항목 추가

3.7 Docker를 이용한 실습 환경 구성

3.7.1 PostgreSQL 컨테이너 실행

Windows PowerShell에서 다음 명령을 실행한다.

$workdir = (Get-Location).Path
docker run --name pg-books `
  -e POSTGRES_PASSWORD=postgres `
  -e POSTGRES_USER=postgres `
  -e POSTGRES_DB=books `
  -p 5432:5432 `
  -v "$workdir\sql:/sql" `
  -d postgres:16

3.7.2 샘플 데이터 로드

순서대로 SQL 스크립트를 실행한다.

# 1) 스키마/기본데이터
docker exec -e PAGER=cat -e LESS= -i pg-books `
  psql -U postgres -d books --pset=pager=off -v ON_ERROR_STOP=1 `
  -f /sql/books-pgsql-10-script.sql

# 2) towns
docker exec -e PAGER=cat -e LESS= -i pg-books `
  psql -U postgres -d books --pset=pager=off -v ON_ERROR_STOP=1 `
  -f /sql/books-pgsql-10-towns.sql

# 3) countries
docker exec -e PAGER=cat -e LESS= -i pg-books `
  psql -U postgres -d books --pset=pager=off -v ON_ERROR_STOP=1 `
  -f /sql/books-pgsql-10-countries.sql

# 4) exercises
docker exec -e PAGER=cat -e LESS= -i pg-books `
  psql -U postgres -d books --pset=pager=off -v ON_ERROR_STOP=1 `
  -f /sql/books-pgsql-10-exercises.sql

3.7.3 데이터 확인

데이터가 정상적으로 로드되었는지 확인한다. 확인 후 3.2부터 코드를 실습하면 된다.

# 고객 수 확인
docker exec -it pg-books psql -U postgres -d books -c "SELECT COUNT(*) FROM customers;"

# 도시 수 확인
docker exec -it pg-books psql -U postgres -d books -c "SELECT COUNT(*) FROM towns;"

3.8 요약

• 데이터베이스 관계는 일대다, 일대일, 다대다로 분류된다.
• 각 관계 유형에 따라 적절한 JOIN 방법을 선택해야 한다.
• NULL 값 처리와 외래키 제약조건을 고려해 데이터를 조작한다.
• 복잡한 관계는 뷰를 통해 단순화할 수 있다.

 

이 글은 『실무에서 SQL을 다루는 기술』 책을 학습한 내용을 정리한 것입니다.