[SQL] JOIN

정의

JOIN 은 두 개 이상의 테이블을 공통 컬럼 (보통 키) 으로 결합해 한 결과셋으로 만드는 연산. SQL 의 관계 모델 핵심.

종류는 5 가지: INNER, LEFT, RIGHT, FULL OUTER, CROSS. 같은 테이블끼리 결합하는 SELF JOIN 은 별도 종류가 아니라 같은 테이블을 두 번 참조하는 패턴.

pandas 의 Pandas merge 가 완전 대응되는 개념. 시각화는 동일하게 사용한다.

시각화

4 가지 메인 JOIN 타입

예제 데이터

-- users 테이블
id |  name
----+---------
1 | Alice
2 | Bob
3 | Charlie
​
-- orders 테이블
user_id | amount
---------+--------
     1 |    100
     1 |    200
     2 |    150
     4 |     50

-- 이 두 테이블로 4 가지 join 차이를 본다

INNER JOIN

양쪽 모두 매칭되는 행만.

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

 id | name  | amount
----+-------+--------
1 | Alice |    100
1 | Alice |    200
2 | Bob   |    150

Alice (2 주문), Bob (1 주문) 만. Charlie 는 주문 없음, orders.user_id=4 는 users 에 없음.

INNER 키워드는 생략 가능, JOIN 만 쓰면 INNER JOIN. 명시 권장.

LEFT JOIN

왼쪽 테이블의 모든 행 + 오른쪽 매칭. 없으면 오른쪽 컬럼은 NULL.

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

 id |  name   | amount
----+---------+--------
1 | Alice   |    100
1 | Alice   |    200
2 | Bob     |    150
3 | Charlie |   NULL

Charlie 도 나타난다, amount 는 NULL. “users 전부 + 그들의 주문” 같은 질문에 적합.

LEFT JOIN + IS NULL = 안티 조인

“오른쪽에 없는 왼쪽 행” 찾기.

-- 주문이 한 번도 없는 사용자
SELECT u.id, u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL;

 id |  name
----+---------
3 | Charlie

NOT EXISTS / NOT IN 으로 같은 결과를 낼 수 있지만, 옵티마이저가 가장 효율적인 plan 을 짜기에 유리한 건 dialect/통계에 따라 다르다.

RIGHT JOIN

LEFT JOIN 의 좌우 반전.

SELECT u.id, u.name, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

orders 의 user_id=4 행도 나타난다 (사용자 없음, NULL).

실무에선 RIGHT JOIN 보다 FROM 의 테이블 순서를 바꾸고 LEFT JOIN 쓰는 게 가독성 좋다.

FULL OUTER JOIN

양쪽 모두 보존, 매칭 안 되면 반대편이 NULL.

SELECT u.id, u.name, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- MySQL
SELECT u.id, u.name, o.amount
FROM users u LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.id, u.name, o.amount
FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

CROSS JOIN

모든 조합 (데카르트곱). 행 수 = m × n.

SELECT u.name, c.color
FROM users u
CROSS JOIN colors c;

ON 절이 없다. 보통 의도된 사용은 작은 룩업 테이블 확장, 카탈로그 매트릭스 생성.

암시적 cross join (구식):

SELECT u.name, c.color FROM users u, colors c;     -- 같은 결과

WHERE 절을 깜빡하면 의도치 않은 cross join 이 된다, 명시적 CROSS JOIN 이 안전.

SELF JOIN

같은 테이블을 두 번 참조. 보통 부모-자식 관계, 동일 도메인 비교.

-- 매니저-부하 관계
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 같은 city 의 다른 사람
SELECT a.name, b.name AS roommate
FROM users a
JOIN users b
  ON a.city = b.city
 AND a.id   < b.id;     -- 중복 쌍 제거 (id 순으로만)

테이블 별칭이 필수.

ON vs USING vs NATURAL JOIN

ON

가장 일반적, 가장 명시적.

JOIN orders o ON u.id = o.user_id
JOIN orders o ON u.id = o.user_id AND o.status = 'active'

USING (양쪽 컬럼 이름 같을 때)

JOIN orders USING (user_id)
-- ON 절의 짧은 버전, 결과 컬럼이 한 번만 나옴

NATURAL JOIN

같은 이름의 모든 컬럼을 자동으로 매칭. 위험하다.

SELECT * FROM users NATURAL JOIN orders;
-- created_at, id 같은 우연히 겹친 컬럼까지 join 키가 됨

스키마가 바뀌면 의미가 조용히 바뀐다. 거의 항상 명시적 ON 권장.

여러 테이블 JOIN

SELECT u.name, o.id, p.name AS product
FROM users u
JOIN orders   o ON o.user_id    = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p  ON p.id         = oi.product_id
WHERE u.city = 'Seoul';

옵티마이저가 join 순서를 선택. 통계 가 정확하면 효율적, 아니면 nested-loop 폭발.

JOIN 알고리즘 (간단)

옵티마이저가 선택하는 물리 알고리즘 3 가지.

대용량 hash join 은 External Merge Sort 와 비슷하게 메모리 부족 시 디스크로 spill 한다 (Grace Hash Join). PG/MySQL EXPLAIN ANALYZE 로 확인.

pandas 와 SQL JOIN

Pandas merge 와 1:1 대응.

pandas 와 SQL 모두 동일한 set-theory 위에서 동작, 위 시각화는 pandas 와 공유한다.

함정

1. 다대다 폭증

users 와 orders 모두 같은 키가 중복돼 있으면 결과 행 수가 곱셈으로 증가.

-- users.id 가 1, 1
-- orders.user_id 가 1, 1, 1
-- → 2 × 3 = 6 행 (의도 아닐 수도)

GROUP BY 또는 서브쿼리로 한쪽 unique 화.

2. NULL 키리는 매칭 안 됨

ON u.col = o.col    -- 양쪽 NULL 이어도 매칭 X
ON u.col IS NOT DISTINCT FROM o.col     -- PostgreSQL, NULL=NULL 매칭

3. ON 과 WHERE 의 차이 (특히 OUTER JOIN)

-- LEFT JOIN 에서 ON 조건은 결합 단계, WHERE 는 결합 후
SELECT *
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid';
-- Charlie 도 살아 있음 (왼쪽 보존)

SELECT *
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid';
-- Charlie 가 사라짐 (WHERE 가 NULL 행 제거, INNER JOIN 처럼 동작)

OUTER JOIN 에서 우측 컬럼 조건은 ON 에 두는 게 의도와 맞을 때가 많다.

4. SELECT * 의 컬럼 중복

SELECT * FROM users JOIN orders ON users.id = orders.user_id;
-- id 컬럼이 양쪽에서 나옴, 어떤 게 어떤 건지 모호

명시적으로 users.id, orders.id AS order_id.

관련 위키

• SQL
• SQL DML
• SQL GROUP BY
• SQL Data Types
• PostgreSQL
• MySQL
• Pandas merge
• Pandas join
• Pandas concat
• External Merge Sort