Join 에 관해서 정리해 보겠습니다. #SQL

오늘은 관계형 데이터베이스의 핵심인 Join에 대해서 정리해 보도록 하겠습니다.

 

1. Join

중복정보를 제거하기 위해, 정규화가 된 테이블들이 있을 때,

조각나있는 여러 테이블의 정보를 한번에 합쳐서 Query해야 하는 경우가 발생하는데요.

이 때 여러 테이블들을 합쳐서 필요한 정보를 찾아내는 방법이 JOIN 인데요.

 

2. Join의 종류

Join의 종류는 테이블을 어떻게 합치느냐에 따라서 달라지는데요.

이를 이해하기 위해서 벤다이어그램을 이용하면 좋습니다.

이에 따라서 크게 LEFT JOIN, RIGHT JOIN, INNER JOIN, FULL OUTER JOIN으로 나눠볼수 있을 텐데요.

하나씩 알아보도록 하겠습니다.

 

2-1. LEFT JOIN

아래와 같이 A테이블에서 필요한 일부 정보를 B에서 가져온다고 할 때,

아래와 같이 JOIN할 수 있는데요.

대부분 이 LEFT JOIN을 많이 사용하게 되겠지요.

 

 

SQL문은 아래와 같은데요.

LEFT JOIN을 사용할 수 있습니다.

이 때, A.key와 B.key공통된 경우에 사용한다고 아래와 같이 써 주는 것 이지요.

SELECT * FROM TableA A 
LEFT JOIN TableB B 
ON A.key = B.key

 

 

한가지 경우가 더 있을 수 있는데요.

A와 B에 공통으로 존재하는 데이터는 가져오지 않을 때 입니다.

이를 벤다이어그램으로 표현하면 아래와 같은데요.

 

 

SQL문으로 보면 아래와 같습니다.

A테이블에 B테이블을 LEFT JOIN하되,

A.key와 B.key가 NULL일 때,

사용합니다.

SELECT * FROM TableA A 
LEFT JOIN TableB B 
ON A.key = B.key WHERE B.key IS NULL

 

2-2. RIGHT JOIN

LEFT JOIN이 있다면 당연히 RIGHT JOIN도 있겠지요.

사실 어느 테이블을 기준으로 잡고 합치느냐는 것 이므로,

LEFT JOIN을 기준으로 한 것을 RIGHT JOIN으로 표현할 수도 있는 것이겠지요.

다만, LEFT JOIN을 더 많이 사용하기는 합니다.

 

 

SQL문도 보도록 하겠습니다.

SELECT * FROM TableA A 
RIGHT JOIN TableB B 
ON A.key = B.key

 

2-3. INNER JOIN

만약 A테이블과 B테이블의 공통하는 정보만 가지고 오려면 어떻게 해야 할까요?

아래 벤다이어그램과 같이 표현할 수 있을 텐데요.

 

 

SQL문으로 정리하면 다음과 같습니다.

 

SELECT * FROM TableA A 
INNER JOIN TableB B 
ON A.key = B.key

 

 

2-4. FULL OUTER JOIN

FULL이라는 이름이 들어간 것처럼 두테이블의 모든 데이터를 합친 것 인데요.

벤다이어 그램을 보면서 이해해 보도록 하겠습니다.

 

 

이것을 SQL문으로 써 보면 다음과 같습니다.

 

SELECT * FROM TableA A 
FULL OUTER JOIN TableB B 
ON A.key = B.key

 

3. 실제 SQL문 작성

3-1. LEFT JOIN

실제로 테이블을 만들고 LEFT JOIN에 대해서, SQL문을 작성해 보도록 하겠습니다.

다음의 세개의 표가 있다고 가정하겠습니다.

book과 author 그리고  category에 대한 테이블을 만들고,

한번에 이를 표현할 때 어떻게 Query해야하는지 생각해 보겠습니다.

 

book 테이블

id	title	description	author_id
1	제목1	설명1	1
2	제목2	설명2	2
3	제목3	설명3	NULL

 

 

author 테이블

id	name	category_id
1	박군	1
2	송군	2

 

category 테이블

id	name
1	소설
2	수필

 

먼저 book테이블에 LEFT JOIN을 이용해서 author에 대한 정보를 볼 수 있도록 해 보겠습니다.

LEFT JOIN이라는 명령어를 이용해서 오른쪽에 author테이블을 놓는다고 선언해 주고요.

그리고 book테이블의 author_id와 author의 id같이 같으므로 이것을 기준으로

하나의 테이블을 만드는 것 입니다.

참고로 author에 해당하는 id가 NULL인 경우, 우측에 JOIN된 테이블의 컬럼 값들은 NULL값이 나옵니다.

book.author_id도 NULL이고 author.id도 NULL이므로 NULL==NULL아니냐고 할수도 있지만,

SQL에서 NULL==NULL에서는 false 입니다.

SELECT * FROM book
LEFT JOIN author 
ON book.author_id = author.id 

 

 

이 Join문에 세번째인 Category테이블을 합쳐보겠습니다.

LEFT JOIN을 이용해서 위에서 하나로 합쳐진 두개의 테이블의 데이터에,

다시 Category의 데이터를 붙여서 하나의 큰 테이블을 만들어 주기만 하면 됩니다.

이런식으로 정규화하면서 여러개의 테이블로 나누어진 정보를 하나의 테이블로 합쳐주는 것 이지요.

 

SELECT * FROM book
LEFT JOIN author 
ON book.author_id = author.id 
LEFT JOIN category
ON author.category_id = category.id

 

이렇게 JOIN된 테이블에는 컬럼수가 굉장히 많아지기 때문에,

사용하기를 원하는 컬럼만 보기위해서 컬럼을 아래와 같이 SQL문에 지정해 주면 좋겠지요.

합쳐진 테이블에서 다른 컬럼명을 사용해서 가져오려면 아래와 같이 AS 지시어를 사용해 주면 됩니다.

마지막에 WHERE 절을 붙여서 원하는 데이터의 ROW만 가져올 수도 있겠지요.

 

SELECT book.id, title, author_id, author.name, category.name AS category_name
FROM book
LEFT JOIN author 
ON book.author_id = author.id 
LEFT JOIN category
ON author.category_id = category.id
WHERE book.id = 8

 

WHERE절을 이용하면 LEFT JOIN된 값에 다양한 조건을 걸 수 있을 텐데요.

아래와 같이도 사용이 가능합니다.

author.id가 NULL인 값만 보이도록 하는 것 인데요.

author가 없는 JOIN된 테이블만 보도록 하는 것 이지요.

SELECT * FROM book
LEFT JOIN author 
ON book.author_id = author.id 
WHERE author.id is NULL

 

3-2. INNER JOIN

INNER JOIN에 대해서 위에서 벤다이어그램에서을 보았지만,

양쪽 테이블에서 존재하는 공통의 데이터에 대해서만 테이블을 합쳐서 사용하는 것 입니다.

 

book테이블에 author테이블을 LEFT JOIN을 했을경우,

author_id가 NULL이므로 기준이 되는 book 테이블의 우측에는 NULL값만 각 컬럼에 들어가서 합쳐지게 되는데요.

INNER JOIN에서는 NULL값이 있을경우는 ROW가 생성되지 않습니다.

공통된 데이터가 없기 때문인데요.

 

SQL문도 보도록 하겠습니다.

 

SELECT * FROM book
INNER JOIN author 
ON book.author_id = author.id 

 

 

3-3. FULL OUTER JOIN

FULL OUTER JOIN은 위에서 본 것처럼,

양쪽 테이블의 모든 데이터를 다 합쳐서 하나의 테이블을 만드는 것 인데요.

합쳐진 테이블은 결국 LEFT JOIN과 RIGHT JOIN을 합쳐놓은 것과 같다고도 할 수 있습니다.

 

author_id가 NULL일 경우는 어떻게 될까요?

 

SQL문을 보도록 하겠습니다.

SQL문만 보면, book테이블을 기준으로 양쪽 테이블이 합쳐질 것 같지만,

book테이블을 기준으로 한 ROW들과 author테이블을 기준으로 한 ROW들이 모두 합쳐지게 됩니다.

 

LEFT JOIN만 했다면, 3개의 ROW만 생성되었을 것 인데,

FULL OUTER JOIN의 경우는,

book테이블에 3개의 ROW가 있고, author테이블에는 2개가 있으므로,

총 5개의 ROW가 생성되게 되겠지요.

문제는 이 ROW들은 당연히도 중복된 데이터가 생기게 되는 것 입니다.

그래서인지 우리의 MYSQL은 FULL OUTER JOIN을 지원하지 않습니다.

 

SELECT * FROM book A 
FULL OUTER JOIN author B 
ON book.author_id = author.id

 

그렇지만, 중복된 데이터를 제거하기 위해서, 집합을 만드는 UNION 지시어를 이용해서 비슷한 결과값을 가져올 수는 있습니다.

 

(
 SELECT * FROM book A 
 LEFT JOIN author B 
 ON book.author_id = author.id
) UNION (
 SELECT * FROM book A 
 RIGHT JOIN author B 
 ON book.author_id = author.id
)

 

4. Join 참고 사이트

Google에 SQL Join Visualizer를 검색하면 많은 사이트가 나오는데요.

SQL문의 Join을 비쥬얼로 형상화 한 것을 볼 수 있어서, JOIN 된 테이블에 대해서 이해하는데 큰 도움이 됩니다.

 

먼저 아래 사이트가 있는데요.

>> joins.spathon.com/

 

아래와 같이, Users테이블과 Likes테이블을 이용해서 JOIN에 대한 결과를 알려줍니다.

 

 

 

아래의 사이트도 좋은데요.

>> sql-joins.leopard.in.ua/

 

예를 들면, A테이블에서 필요한 정보를 B테이블에서 가져와서 사용할 경우,

LEFT JOIN문을 이용해서 오른쪽과 같이 사용할 수 있다고 알려주는 것 이지요.

 

 

이상으로 JOIN에 대해서 정리해 보았는데요.

더 좋은 정보가 있으면 이 글을 통해서 업데이트 하도록 하겠습니다.