SQL JOIN 예제 - INNER, LEFT, RIGHT, FULL, CROSS, SELF JOIN

SQL에서 두번째로 많이 쓰이는 JOIN에 대해 알아본다.

[MEMBER]와 [ORDER] 라고 하는 두가지 가상의 테이블이 있다고 하자. 두 테이블은 이렇게 생겼다.

 

SELECT * FROM [MEMBER]
SELECT * FROM [ORDER]

 

JOIN(결합)이란, 2개 이상의 다른 테이블을 결합시키는 것을 의미하며 join을 하려면 반드시 공통열(key)이 있어야 한다.

이번 예제에서 알아볼 [MEMBER]는 회원정보, [ORDER]는 주문내역을 담은 테이블이며, 두 개의 공통열(key)는 mem_no(회원 ID) 이다.

→ 그러므로 mem_no를 기준으로 조인시키면 되겠지?

 

✔Table of Contents

 

1. INNER JOIN

INNER JOIN은 교집합을 의미하며, 이를 사용하는 경우 두 테이블의 공통값이 매칭되는 데이터만 조회된다.

[MEMBER]와 [ORDER] 2개의 테이블 모두에 존재하는 mem_no의 데이터만 뽑힌다.

공통열인 mem_no를 ON으로 연결해주면 조인 완료!

--1. INNER JOIN
SELECT *
FROM [MEMBER] A 
INNER JOIN [ORDER] B 
ON A.mem_no = B.mem_no

 

이렇게 두 테이블 모두에 있는 mem_no에 대한 데이터 2,951개만 조회되었다.

 

2. OUTER JOIN 

OUTER JOIN은 합집합을 의미하는데, 즉 다시 말해서 두 테이블 간의 공통값으로 매칭되는 값뿐만 아니라 매칭되지 않는 데이터까지 조회된다.

OUTER JOIN에는 LEFT JOIN, RIGHT JOIN, FULL JOIN이 있으며, 그림으로 설명해보면 다음과 같다.

위 그림처럼, LEFT JOIN은 좌측의 테이블(A)을 기준으로 B를 결합하는 것이다. 공통열이 A에는 있지만 B엔 없는 경우에도 NULL로 결합되게 된다.

마찬가지로 RIGHT JOIN은 우측의 테이블(B)을 기준으로 A를 결합하는 것이다. 공통열이 B에는 있지만 A엔 없는 경우에도 NULL로 결합된다.

 

FULL JOIN은 A와 B의 모든 부분이 결합되는 것이다. 공통되는 부분이 없어도 모든 데이터를 조회시킨다고 보면 된다.

실무에서 가장 많이 쓰이는 것은 LEFT JOIN이다. 가장 중요한 테이블을 A로 깔아 두고 LEFT JOIN시켜 필요한 테이블들 B, C, D ,... 등등을 조인하는 경우가 많다. 

말로 하면 헷갈리니 바로 예제로 알아보자.

 

2-1. LEFT JOIN 

| [MEMBER] 테이블을 기준으로 LEFT JOIN하여라.

-- [MEMBER] 기준으로 LEFT JOIN
SELECT *
FROM [MEMBER] A
LEFT JOIN [ORDER] B
ON A.mem_no = B.mem_no

 

중요한 점은 'A' 테이블을 기준으로 LEFT JOIN한다면, A 테이블은 FROM 절에 쓰고 LEFT JOIN 절에는 B를 써야 한다.

결과는 위처럼 2,955개의 행이 나오게 된다. 

[MEMBER] 테이블에 있는 데이터는 모조리 save하고, [ORDER] 테이블엔 없는 데이터일지라도 이 값을 NULL로 갖다붙인다!

→ 즉, [ORDER] 테이블엔 mem_no가 100001번부터~ 100004번은 없는데도 NULL로 채워졌다. 이 회원번호들은 가입은 해놓고 주문은 안 한 애들이다.

 

2-2. RIGHT JOIN

| [ORDER] 테이블을 기준으로 RIGHT JOIN하여라.

-- [ORDER] 기준으로 RIGHT JOIN 
SELECT *
FROM [MEMBER] A
RIGHT JOIN [ORDER] B
ON A.mem_no = B.mem_no

 

이 경우엔 반대로 [ORDER] 테이블의 모든 데이터는 save하고, [MEMBER]엔 없는 데이터일지라도 NULL로 채워지게 된다.

지금 결과를 보면 NULL이 안보이지만, 만일 NULL이 있다면 주문은 해놓고 가입은 안 한, 비회원의 데이터라고 볼 수 있다. (하지만 이 데이터는 비회원 주문 기능이 없는 걸로 가정했기에 NULL이 나오지 않았다)

그래서 2,951개의 행이 조회되었다.

 

2-3. FULL JOIN

-- [MEMBER] & [ORDER] FULL JOIN : 합집합(UNION)
SELECT *
FROM [MEMBER] A
FULL JOIN [ORDER] B
ON A.mem_no = B.mem_no

 

이번엔 FULL JOIN을 해보자. 이 경우 [MEMBER]와 [ORDER] 테이블의 모든 부분(즉 겹치는 부분과 겹치지 않는 부분 전부)을 결합한 것이다.

비회원 주문 기능을 막아놓은 데이터이므로 LEFT JOIN과 같은 결과인 2,955행이 조회되었다.

 

3. OTHER JOIN

자주 쓰이진 않지만 알아두면 좋은 JOIN에 대해서도 알아보자. 바로 CROSS JOIN과 SELFT JOIN이다.

 

CROSS JOIN은 두 테이블 A,B가 있을 때 이를 1개 행씩 결합하는 조인이다.

즉 다시 말해서 테이블 A의 행 1개가 테이블 B의 모든 행에 하나씩 결합된다.

예를 들어 A엔 행이 1개, B엔 행이 5개 있으면 → 1*5 = 5번 조인

A에 행이 2개, B엔 행이 3개 있으면 → 2*3 = 6번 조인된다.

 

SELF JOIN은 자기 자신의 테이블에 대해서 1개 행씩 결합하는 조인이다.

다시 말해서 테이블 A의 행 1개가 테이블 A의 모든 행에 하나씩 결합되는 것이다.

이 경우 테이블 A의 특정 행만 하나를 뽑고, 얘에 대해 A의 모든 행에 매칭시킨다.

이를테면 특정 행 1개, A의 행 100개가 있으면 → 1*100 = 100번 조인되는 식이다.

예제로 알아보자!

 

3-1. CROSS JOIN

| Q. [MEMBER] 테이블의 mem_no = '1000001'인 것과, [ORDER]의 모든 행과 JOIN 하여라.

SELECT *
FROM [MEMBER] A
CROSS JOIN [ORDER] B
WHERE A.mem_no = '1000001'

결과를 보면, [MEMBER] 테이블이 좌측, [ORDER] 테이블이 우측에 결합되었다.

[MEMBER] 테이블은 mem_no가 모두 '1000001'인데, [ORDER] 테이블의 mem_no는 다양하다. 

왜냐하면 좌측은 [MEMBER] 테이블에서 mem_no가 1000001인 것만, 우측은 [ORDER] 테이블 전체가 결합되었기 때문!

 

3-2. SELF JOIN

| Q. [MEMBER] 테이블의 mem_no = '1000001'인 것과, [MEMBER]의 모든 행과 JOIN하여라.

SELECT *
FROM [MEMBER] A, [MEMBER] B 
WHERE A.mem_no = '1000001'

SELF JOIN은 좀 특별한데, 다른 애들이 전부 '~~ JOIN' 이라는 이름이 있는 것과 달리 SELF JOIN은 FROM절에 자기 자신의 테이블 2개를 넣는다.

좌측은 [MEMBER] 테이블의 mem_no가 1000001인 것만, 우측은 [MEMBER] 테이블 전체가 결합되었다!

 

---

4. 문제로 복습하기 

문제와 함께 어떻게 쿼리를 짜야 하는지 스스로 해보고 정답을 맞춰보자! :)

 

| Q. [ORDER] 테이블을 기준으로 [MEMBER] 테이블을 LEFT JOIN해라.

-- 1. [ORDER] 기준으로 [MEMBER] 테이블 LEFT JOIN 
SELECT *
FROM [ORDER] A
LEFT JOIN [MEMBER] B
ON A.mem_no = B.mem_no

 

| Q. 위의 LEFT JOIN 결과를 이용해, [gender] 컬럼별로 [sales_amt] 컬럼의 합계를 구하여라. (SUM(sales_amt)의 이름은 tot_amt로 할것)

-- 2. CALCULATE SUM OF [sales_amt] PER [gender]
SELECT B.gender AS gender, SUM(A.sales_amt) AS tot_amt
FROM [ORDER] A
LEFT JOIN [MEMBER] B
ON A.mem_no = B.mem_no
GROUP BY B.gender

 

| Q. 1번의 LEFT JOIN 결과를 이용해, [gender]와 [addr] 컬럼별로 [sales_amt] 컬럼의 합계를 구하여라. (SUM(sales_amt)의 이름은 tot_amt로 할것)

-- 3. CALCULATE SUM OF [sales_amt] PER [gender, addr]
SELECT B.gender AS gender, B.addr AS addr, SUM(sales_amt) AS tot_amt
FROM [ORDER] A
LEFT JOIN [MEMBER] B
ON A.mem_no = B.mem_no
GROUP BY B.gender, B.addr