JOIN (조인) - MySQL

JOIN - 여러 테이블의 데이터를 결합할 때 사용

 

- 왜 사용하는 가?

• 대부분의 실제 데이터는 여러 테이블에 나뉘어 저장된다.
• 예를 들어 "users" 테이블엔 고객 정보, "orders" 테이블엔 주문 정보가 있다.
• 이 2가지 테이블을 연결해서 "누가 어떤 주문을 했는지" 알아내기 위해 JOIN 이 사용된다.

 

- JOIN 의 종류

 

 

- 예시

아래와 같은 테이블이 있다고 가정하고 각 JOIN 방법별로 예시를 작성해 보자.

-- users
+---------+--------+
| user_id | name   |
+---------+--------+
| 1       | Alice  |
| 2       | Bob    |
| 3       | Carol  |
+---------+--------+

-- orders
+----------+---------+--------+
| order_id | user_id | amount |
+----------+---------+--------+
| 101      | 1       | 100.00 |
| 102      | 1       | 150.00 |
| 103      | 2       | 200.00 |
| 104	   | 4       | 250.00 |
+----------+---------+--------+

 

 

1. INNER JOIN - 양쪽 테이블에 모두 있는 경우만 JOIN

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o
ON u.user_id, o.user_id

 

출력 결과 :

 

name	amount
Alice	100.00
Alice	150.00
Bob	200.00

 

 

 

2. LEFT JOIN - 왼쪽 테이블은 전부 출력하고, 오른쪽 값이 없다면 그 오른쪽 값은 NULL

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id

 

출력 결과 :

name	amount
Alice	100.00
Alice	150.00
Bob	200.00
Carol	NULL

 

 

 

3. FULL OUTER JOIN - 양쪽 테이블의 모든 데이터를 출력하고 없는 값은 NULL

단, 앞에서도 말했듯이 MySQL 에서는 UNION을 사용해야함.

SELECt u.user_id, u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o On u.user_id = o.user_id

UNION # 테이블 결합

SELECT u.user_id, u.name, o.order_id, o.amount
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id

 

출력 결과 :

user_id	name	order_id	amount
1	Alice	101	100.00
1	Alice	102	150.00
2	Bob	103	200.00
3	Carol	NULL	NULL
4	NULL	104	250.00

 

 

- SELF JOIN 

JOIN 에는 앞에서 살펴본 기능 이외에도 SELF JOIN 이라는 기능이 있다. SELF JOIN은 자기 자신과 JOIN 하는 것을 의미한다. 즉, 같은 테이블을 두 번 사용해서 서로 연결 관계를 표현하는 것이다.

 

예를 들어, "어떤 직원의 상사는 누구인지", "어떤 사람이 누구랑 친구인지" 와 같이 테이블 자기 자신과의 관계를 표현할 때 사용한다.

 

아래와 같은 테이블이 있다고 생각해보자. 이 테이블에서 직원과 그들의 상사 이름을 함께 보려고 한다면 어떻게 해야할까?

-- employees
+-----------+----------+------------+
| emp_id    | name     | manager_id |
+-----------+----------+------------+
| 1         | Alice    | NULL       |
| 2         | Bob      | 1          |
| 3         | Carol    | 1          |
| 4         | David    | 2          |
+-----------+----------+------------+

 

• Alice: CEO (상사 없음)
• Bob, Carol: Alice의 부하
• David: Bob의 부하

 

SELECT
	e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id

 

출력 결과 :

employee	manager
Alice	NULL
Bob	Alice
Carol	Alice
David	Bob

 

 

---

 

- 실전 문제

 

이제 GPT를 활용해서 실전 문제를 만들고, 채점과 피드백을 요청했다.

 

문제에 활용할 테이블

-- users 테이블
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO users (user_id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Carol'),
(4, 'David');

-- orders 테이블
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10, 2)
);

INSERT INTO orders (order_id, user_id, amount) VALUES
(101, 1, 100.00),
(102, 1, 150.00),
(103, 2, 200.00),
(104, 4, 300.00);

-- friendships 테이블
CREATE TABLE friendships (
  user_id INT,
  friend_id INT
);

INSERT INTO friendships (user_id, friend_id) VALUES
(1, 2),
(1, 3),
(2, 4);

 

🔸 문제 1.

사용자와 그들의 주문 내역을 모두 출력하라.
주문이 없는 사용자도 포함되도록 한다.

• 사용 테이블: users, orders
• 출력 컬럼: user_id, name, order_id, amount

SELECT
	u.user_id,
    u.name,
    o.order_id,
    o.amount
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id

 

🔸 문제 2.

사용자별로 지금까지 결제한 총 금액을 구하라.
주문이 없는 사용자는 0으로 표시한다.

• 사용 테이블: users, orders
• 출력 컬럼: user_id, name, total_amount

SELECT
	u.user_id,
    u.name,
    COALESCE(SUM(o.amount), 0) AS total_amount
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id
GROUP BY u.user_id

 

🔸 문제 3.

사용자와 친구 이름을 모두 출력하라.
friendships 테이블은 친구 관계만 나타낸다.

• 사용 테이블: users, friendships
• 출력 컬럼: user_name, friend_name

WITH friend AS (
  SELECT
    u.user_id,
    u.name,
    f.friend_id
  FROM users u
  LEFT JOIN friendships f
    ON u.user_id = f.user_id
)

SELECT
	f1.name as user_name,
    f2.name as friend_name
FROM friend f1
JOIN friend f2
ON f1.friend_id = f2.user_id

 

🔸 문제 4.

주문을 한 번도 하지 않은 사용자만 출력하라.

• 사용 테이블: users, orders
• 출력 컬럼: user_id, name

SELECT
	u.user_id,
    u.name
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id
GROUP BY u.user_id
HAVING COUNT(order_id) = 0

 

🔸 문제 5.

사용자의 친구의 친구를 출력하라.

• 자기 자신이 결과에 나오면 안 된다.
• 이미 친구인 관계도 결과에 포함되면 안 된다.
• 사용 테이블: users, friendships
• 출력 컬럼: user_id, friend_of_friend_id

WITH friend AS (
  SELECT
  	u.user_id,
  	u.name,
  	f.friend_id
  FROM users u
  LEFT JOIN friendships f
  ON u.user_id = f.user_id
  )
 
SELECT
	f1.user_id,
    f3.user_id
FROM friend f1
JOIN friend f2
ON f1.friend_id = f2.user_id
JOIN friend f3
ON f2.friend_id = f3.user_id

 

 

 

📊  GPT의 총평

1번	✅	정확함
2번	✅	완벽함
3번	✅	구조 깔끔
4번	✅	논리 완벽
5번	⚠️	자기자신/직접친구 제외 조건 누락됨

 

5번 문제에서 조건이 누락된 것들이 있었다. 확인해서 코드를 수정했다. 수정한 코드는 다음과 같다.

WITH friend AS (
  SELECT
  	u.user_id,
  	u.name,
  	f.friend_id
  FROM users u
  LEFT JOIN friendships f
  ON u.user_id = f.user_id
  ),

# 1 -> 2 가 친구이고, 2 -> 1 이 친구라는 데이터 포함되어 있는 경우 제거
friend_of_friend AS (
  SELECT
	f1.user_id,
    f2.friend_id as friend_of_friend
  FROM friend f1
  JOIN friend f2
  ON f1.friend_id = f2.user_id
  WHERE f1.user_id != f2.friend_id
  )

# 1 -> 2 가 친구이고, 2 -> 4 가 친구일 때 1 -> 4 는 친구의 친구 관계
# 단, 기존에 1 -> 4 가 친구라는 데이터가 포함되어 있는 경우 제거
SELECT *
FROM friend_of_friend ff
LEFT JOIN friend f
ON ff.friend_of_friend = f.friend_id AND ff.user_id = f.user_id
WHERE f.user_id IS NULL