[Mysql] JOIN

(해당 포스트는 Inflearn - [백문이불여일타] 데이터 분석을 위한 중급 SQL 강의록입니다.)

JOIN

테이블간의 관계성을 기반으로 정규화하는 RDB에서
흩어져 있는 정보들이 속한 두 개 이상의 테이블을 서로 결합할 때(이어 붙일 때) 사용하는 방법

select * from [table1], [table2]

table1과 table2를 가지고 만들 수 있는 모든 경우의 수를 다 출력함
카티시안 곱 (곱집합)

Inner Join

select * from [Table1] 
inner join [Table2] [Table1.same_column] = [Table2.same_column]

이 때 same_column이란 실제 각 테이블에서의 이름은 다르지만
내용이 같은(==value가 같은) 컬럼을 의미.
실제 각 테이블내에서 쓰이는 이름으로 작성해야 한다.

실제 ex.

select * from users 
inner join orders 
on users.Id = orders.user_Id

inner join은 양쪽테이블에 모두 정보가 있는 경우만을 출력한다.
user 테이블에는 id=1로 정보가 있으나 id=1인 user가 만일 한 번도 실제 상품을 주문한 적이 없다면 order 테이블에는 user_id=1인 주문 레코드가 없을 것이다.
이런 경우에 user와 order를 inner join하면 user_id=1인 레코드는 보이지 않는다.
(강의를 보면서도 궁금해서 실제로 테이블을 만들어 실습해보았고 실습한 결과는 아래 따로 적어두었다.)

Inner join vs. Outer join

<Inner, Left, Right, Full outer join의 길잡이 - Visualizer>
https://sql-joins.leopard.in.ua

결국 join에서 inner join(mysql 기준 보통 그냥 join으로 쓴다)을 제외한
left join과 right join은 모두 outer join이다.
실제 업무에서는 outer join 중 left join을 주로 사용하게 된다.

user table

order table

order table의 각 레코드는 user_id를 FK로 물고 있고,
user 1, 2, 3중 3번 user는 어떠한 주문도 하지 않은 상황이다.
이 때 내가 user tabler과 order table을 한 눈에 보고 싶은 상황이라면 선택지는 세 가지이다.

1) Inner Join 사용 (mysql 기준 그냥 join이라고만 써도 됨)

select * 
from Users as a
inner join Orders as b
on a.id = b.user_id

result set

Inner join을 검색하면 교집합이라는 말을 자주 보게 되는데,
처음에는 두 테이블의 교집합 부분만(==즉 on절에 들어가는 컬럼만) query한다는 것으로 오해했었다.
Inner join은 각각의 table에서 교집합이 되는 부분을 on절에 이용하여
table A에도 있고, table B에도 있는 부분을 보여준다.
즉 한 테이블에서라도 null값을 가지는 경우는 결과값에서 보이지 않게 된다.

2) Left Join 사용

select * 
from Users as a
left join Orders as b
on a.id = b.user_id

result set

Left join은 쿼리문에서 말그대로 좌측에 위치한 테이블을 기준으로 잡아 쿼리하는 것이다. 이 경우 쿼리 내 좌측에 위치한 Users 테이블이 기준이 되기 때문에 만일 Order를 단 한건도 하지 않은 User라고 해도 result set에 포함된다. 위 사진에서 3번 id를 가지는 user는 order를 하지 않은 상황이기 때문에 id, item, price, payment_type, user_id(모두 Orders 테이블의 컬럼들)에는 Null값이 들어간채로 result set에 등장하고 있다.

3) Right Join 사용

select * 
from Users as a
right join Orders as b
on a.id = b.user_id

result set

Right join은 쿼리문에서 말그대로 우측에 위치한 테이블을 기준으로 잡아 쿼리하는 것이다. 이 경우에는 쿼리 내 우측에 위치한 Orders 테이블이 기준이 되고, 이번에는 반대로 Users가 없는, 즉 user_id가 없는 경우의 Order일지라도 result set에 포함된다. 하지만 지금 이 경우에서는 user_id가 없는 order가 존재하지 않는다. (물론 실제 상황에서는 비회원 주문이라는 경우를 포함한다면 존재할 수 있다고 생각한다.) 따라서 right join의 결과는 맨 위의 inner join과 정확히 일치하게 된다.

4) Full Outer Join 사용

select * from Users 
left join Orders on Users.Id = Orders.user_id
union
select * from Users
right join Orders on Users.Id = Orders.user_id;

result set

아마도 Oracle에서는 full outer join을 지원하는 모양이지만, mysql에서는 full outer join이 지원되지 않기에 Union을 이용해야한다. left join과 right join을 합집합으로 만들면(합집합이므로 inner join 부분으로 겹치는 부분은 자연히 distinct 처리) 이 때에는 Full outer join의 효과를 낸다.

그리고 위에서도 이야기했듯, 현재 상황에서 right join이 inner join과 같은 효과를 내고 있기 때문에 역시 full outer join도 left join + inner join이 되어 결국 left join과 같은 result set을 반환했다.