SQL JOIN 활용하기

 

QueryDSL을 공부하면서, 데이터베이스에서 테이블을 어떻게 조인할지 설계하고 다룰 줄 아는 능력이 정말 중요하다는 걸 느꼈다.

 

그래서 이번 포스팅에서는 SQL JOIN에 대해 정리하고 그 다음 시간엔 QueryDSL 사용까지 해보려 한다.

 

 

 

 

 

JOIN이란?

 

SQL에서 데이터를 다룰 때 단일 테이블만으로 원하는 정보를 얻는 경우는 드물다.

 

대부분의 실무 데이터베이스는 테이블이 서로 연결되어 있어서 필요한 정보를 얻기 위해선 여러 테이블을 조합해서 조회해야 한다.

 

이 때 핵심이 되는 개념이 JOIN이다.

 

테이블 간 관계를 이해하고 상황에 맞는 JOIN을 활용할 수 있어야 복잡한 데이터도 효과적으로 처리가 가능하다.

 

JOIN은 두 개 이상의 테이블을 공통된 키로 연결하여 하나의 결과 집합을 만들어주는 기능을 수행한다.

 

우선 JOIN 예제를 위한 User 테이블과 Order 테이블을 정의해보자.

 

• User 테이블 (회원): user_id (PK), name
• Order 테이블 (주문): order_id (PK), user_id (FK, User 참조), item (주문 상품명)

 

Users

| user_id | name    |
|---------|---------|
| 1       | Alice   |
| 2       | Bob     |
| 3       | Charlie |


Orders

| order_id | user_id | item |
|----------|---------|------|
| 101      | 1       | X    |
| 102      | 1       | Y    |
| 103      | 4       | Z    | ← users에 없는 user_id

 

이렇게 간단한 데이터를 기반으로 해서 여러 JOIN별 결과를 볼 것이다.

 

 

 

 

 

INNER JOIN

 

 

INNER JOIN은 두 테이블에서 모두 매칭되는 행만 결합하는 조인이다.

 

교집합에 해당하는 레코드만 조회되며 일반적으로 그냥 JOIN이라고 하면 INNER JOIN을 의미할 만큼 가장 많이 사용되는 조인이다.

 

두 테이블을 FROM <테이블A> INNER JOIN <테이블B> ON <조인 조건>  형태로 작성한다.

 

ON 절에는 두 테이블을 연결하는 키 조건을 명시한다.

 

INNER JOIN은 ON 조건을 만족하는 데이터만 결과에 포함시키기 때문에 양쪽 테이블에 모두 존재하는 연결된 레코드들만 조회된다.

 

-- user_id 일치하는 경우만
SELECT U.user_id, U.name, O.order_id, O.item
FROM User U 
INNER JOIN Order O 
  ON U.user_id = O.user_id;

 

이 쿼리를 실행하면 User와 Order에서 user_id가 같은 경우의 데이터만 결합된다.

 

user_id (U)	name	order_id (O)	item
1	Alice	101	X
1	Alice	102	Y

 

Alice(User 1)는 주문 2건(101, 102)이 있으므로 두 행이 조회된다.

 

 

 

 

 

LEFT OUTER JOIN

 

 

LEFT OUTER JOIN은 왼쪽 테이블의 모든 행을 기준으로 오른쪽 테이블과 결합한다. 

 

왼쪽 테이블에 대응하는 오른쪽 테이블의 레코드가 없더라도 왼쪽 테이블의 모든 레코드가 결과에 포함된다.

 

매칭되지 않는 경우 오른쪽 테이블의 컬럼들은 NULL로 채워진다.

 

FROM <왼쪽 테이블> LEFT [OUTER] JOIN <오른쪽 테이블> ON <조인 조건> 형태로 작성한다.

 

SQL에선 OUTER은 선택 사항이므로 LEFT JOIN이라고만 작성해도 같다.

 

-- 모든 User와 일치 주문
SELECT U.user_id, U.name, O.order_id, O.item
FROM User U 
LEFT OUTER JOIN Order O 
  ON U.user_id = O.user_id;

 

이 쿼리 결과는 User 테이블의 모든 회원을 포함하며 주문이 없는 회원의 경우 주문 부분이 NULL로 표시된다.

 

user_id (U)	name	order_id (O)	item
1	Alice	101	X
1	Alice	102	Y
2	Bob	(NULL)	(NULL)
3	Charlie	(NULL)	(NULL)

 

Alice(1)는 주문 2건이 있어 두 행으로 나타나고, Bob(2)과 Charlie(3)는 주문이 없어 Order 쪽 값이 NULL로 표시된다.

 

LEFT JOIN은 이처럼 왼쪽 테이블의 모든 행을 빠짐없이 보여주고 대응되는 주문이 없는 경우 NULL로 채운다.

 

주문 내역이 없는 회원을 조회할 때 LEFT JOIN을 활용할 수 있다.

 

 

 

 

 

RIGHT OUTER JOIN 

 

RIGHT OUTER JOIN은 LEFT JOIN과 반대로 오른쪽 테이블을 기준으로 모든 행을 결합한다.

 

JOIN 조건에 맞는 왼쪽 테이블의 데이터가 없더라도 오른쪽 테이블의 모든 행이 결과에 포함되며 왼쪽 값이 없으면 NULL로 표시된다.

 

FROM <왼쪽 테이블> RIGHT [OUTER] JOIN <오른쪽 테이블> ON <조건> 형태로 작성한다.

 

-- 모든 Order와 매칭되는 User
SELECT U.user_id, U.name, O.order_id, O.item
FROM User U 
RIGHT OUTER JOIN Order O 
  ON U.user_id = O.user_id;

 

Order 테이블의 모든 주문이 출력되게 된다. User 정보가 없는 주문 103도 포함되고, 해당 행에선 사용자 컬럼이 NULL이 된다.

 

user_id (U)	name	order_id (O)	item
1	Alice	101	X
1	Alice	102	Y
(NULL)	(NULL)	103	Z

 

 

RIGTH JOIN의 결과는 LEFT JOIN에서 테이블 위치만 바꾼 것과 같다.

 

 

 

FULL OUTER JOIN

 

FULL OUTER JOIN은 두 테이블의 모든 행을 모두 포함하여 조인하는 방식이다.

 

왼쪽이든 오른쪽이든 어느 한쪽에만 존재하는 레코드까지도 전부 결과에 나타난다.

 

짝이 없는 부분은 NULL로 채워진다.

 

말 그대로 FULL -> 왼쪽 + 오른쪽의 합집합을 보여준다.

 

MySQL에선 FULL OUTER JOIN을 직접 지원하지 않으므로 LEFT JOIN 결과와 RIGHT JOIN 결과를 UNION으로 합치는 방식으로 우회한다. 

 

여기선 FULL OUTER JOIN을 지원하는 PostgreSQL을 사용하겠다.

 

SELECT U.user_id, U.name, O.order_id, O.item
FROM User U
FULL OUTER JOIN Order O
  ON U.user_id = O.user_id;

 

모든 User와 모든 Order가 빠짐없이 출력된다.

 

user_id	name	order_id	item
1	Alice	101	X
1	Alice	102	Y
2	Bob	(NULL)	(NULL)
3	Charlie	(NULL)	(NULL)
(NULL)	(NULL)	103	Z

 

 

FULL OUTER JOIN은 양쪽 테이블 중 어느 한쪽에만 있는 데이터도 함께 보고 싶을 때 사용하는 조인이다.

 

그런데 우리가 만든 예제처럼 Order의 user_id가 항상 User 테이블에 있어야 한다면, 사실상 고립된(Order만 있는) 데이터가 생기지 않기 때문에 FULL JOIN이 의미가 없다.

그래서 user_id=4인 주문처럼 일부러 연결되지 않는 데이터를 추가해서 예제를 구성했다.

현실에서는 이런 ‘연결 안 되는 데이터’가 양쪽 테이블에 모두 존재할 수 있는 경우, 그걸 다 보려고 FULL JOIN을 사용하는 것이다.

 

 

 

CROSS JOIN

 

CROSS JOIN은 두 테이블 간의 모든 가능한 조합을 생성하는 조인이다.

 

아무 조건 없이 각 테이블의 행을 서로 곱하여 결과를 만들기 때문에 카티션 곱이라고도 한다.

 

-- User와 Order의 교차 조인
SELECT U.user_id, U.name, O.order_id, O.item
FROM User U 
CROSS JOIN Order O;

 

CROSS JOIN엔 ON 절이나 조건이 없으며 결과 행의 개수는 A테이블 행 수 x B테이블 행 수가 된다.

 

예시 데이터 기준 User 3명 x Order 3건 = 총 9개의 조합이 결과로 출력된다.

 

| user_id (U)  | name    | order_id (O)  | item |
| ------------ | ------- | ------------- | ---- |
| 1            | Alice   | 101           | X    |
| 1            | Alice   | 102           | Y    |
| 1            | Alice   | 103           | Z    |
| 2            | Bob     | 101           | X    |
| 2            | Bob     | 102           | Y    |
| 2            | Bob     | 103           | Z    |
| 3            | Charlie | 101           | X    |
| 3            | Charlie | 102           | Y    |
| 3            | Charlie | 103           | Z    |

 

 

 

 

 

 

THETA JOIN

 

 

CROSS JOIN은 조건 없이 모든 가능한 조합을 만들어내는 조인이지만 실제 실무에선 이런 모든 조합 중 원하는 조건에 맞는 행만 필터링해서 사용하는 경우가 많다.

 

이때 세타 조인(theta join)을 사용한다.

 

세타 조인은 CROSS JOIN처럼 두 테이블을 조합한 후 WHERE 절을 사용해서 특정 조건으로 필터링하는 조인 방식이다.

 

CROSS JOIN + WHERE 절 = 세타 조인이라고 이해하면 쉽다.

 

SELECT U.user_id, U.name, O.order_id, O.item
FROM User U, Order O
WHERE U.user_id = O.user_id;

 

이 쿼리는 CROSS JOIN처럼 FROM User, Order로 조합을 만든 뒤 WHERE U.user_id = O.user_id 조건을 걸어 user_id가 같은 데이터만 필터링한다.

 

결과적으로 INNER JOIN과 동일한 결과를 얻게 된다.

 

user_id (U)	name	order_id (O)	item
1	Alice	101	X
1	Alice	102	Y

 

※ user_id가 같은 경우만 추출되므로, 주문 103(Z)이나 주문이 없는 Bob/Charlie는 빠짐.

 

세타 조인은 단순한 등호 뿐 아니라 >, <, !=, BETWEEN, LIKE 등 모든 비교 연산자를 사용할 수 있어서 유연하다.

 

 

 

 

INNER JOIN과 CROSS JOIN과 Theta Join 비교

JOIN 방식	조인 조건 위치	특징
INNER JOIN	ON 절	양쪽 테이블에 모두 있는 데이터만
CROSS JOIN	없음	모든 조합 생성 (카티션 곱)
Theta Join	WHERE 절	조건으로 원하는 조합만 필터링

 

 

 

 

 

 

SELF JOIN

 

SELF JOIN은 하나의 테이블을 자기 자신과 조인하는 것이다.

 

동일한 테이블을 두 번 참조하여 서로 다른 별칭을 주고 조인하면 마치 두 개의 테이블인 것처럼 활용할 수 있다.

 

예를 들어 User 테이블에 추천인 정보가 있는 경우를 생각해보자.

 

User 테이블에 referrer_id 컬럼이 있어서 다른 사용자 ID를 가리킨다면 User 테이블을 자체 조인하여 사용자와 추천인 이름을 함께 조회할 수 있다.

 

SELECT U.name    AS user_name, 
       R.name    AS referrer_name
FROM User U 
LEFT JOIN User R 
  ON U.referrer_id = R.user_id;

 

이 쿼리는 User 테이블을 U와 R로 두 번 불러와서 U.referrer_id와 R.user_id를 매칭했다.

 

결과적으로 U는 본인, R은 추천인 역할을 하기 때문에 각 사용자의 추천인 이름을 얻을 수 있다.

 

SELF JOIN을 사용할 땐 테이블별로 다른 별칭을 지정해야 한다는 것을 유념하자!

 

빨리 더 익숙해져서 맘대로 테이블 짜고 백엔드 설계하고 자유자재로 JPA와 QueryDSL을 다룰 수 있으면 좋겠다..