MySQL, JOIN문

JOIN문을 사용하고는 있지만 제대로 알고 사용하고 있나 싶어서 다시한번 정리

* MYSQL에는 FULL JOIN은 없음 union을 통해서 구현 가능함

Tables

member

member_id	member_name	team_id
1	m1	0
2	m2	1
3	m3	1
4	m4	2

team

team_id	team_name
1	team1
2	team2
3	team3

 

SELECT JOIN

INNERT JOIN (JOIN)

SELECT * FROM `member` m
INNER JOIN `team` t ON t.team_id = m.team_id;

member_id	member_name	team_id	team_id	team_name
2	m2	1	1	team1
3	m3	1	1	team1
4	m4	2	2	team2

 

LEFT OUTER JOIN (LEFT JOIN)

SELECT * FROM `member` m
LEFT OUTER JOIN `team` t ON t.team_id = m.team_id;

member_id	member_name	team_id	team_id	team_name
2	m2	1	1	team1
3	m3	1	1	team1
4	m4	2	2	team2
1	m1	0	<null>	<null>

 

LEFT OUTER JOIN (LEFT JOIN) B IS NULL

SELECT * FROM `member` m
LEFT OUTER JOIN `team` t ON t.team_id = m.team_id
WHERE t.team_id IS NULL;

member_id	member_name	team_id	team_id	team_name
1	m1	0	<null>	<null>

 

RIGHT OUTER JOIN (RIGHT JOIN)

SELECT * FROM `member` m
RIGHT OUTER JOIN `team` t ON t.team_id = m.team_id;

member_id	member_name	team_id	team_id	team_name
2	m2	1	1	team1
3	m3	1	1	team1
4	m4	2	2	team2
<null>	<null>	<null>	3	team3

 

RIGHT OUTER JOIN (RIGHT JOIN) A IS NULL

SELECT * FROM `member` m
RIGHT OUTER JOIN `team` t ON t.team_id = m.team_id
WHERE m.team_id IS NULL;

member_id	member_name	team_id	team_id	team_name
<null>	<null>	<null>	3	team3

 

FULL JOIN -- mysql에는 full join이 없으므로 union으로 구현

SELECT * FROM `member` m
LEFT OUTER JOIN `team` t ON t.team_id = m.team_id
UNION
SELECT * FROM `member` m
RIGHT OUTER JOIN `team` t ON t.team_id = m.team_id;

member_id	member_name	team_id	team_id	team_name
2	m2	1	1	team1
3	m3	1	1	team1
4	m4	2	2	team2
1	m1	0	<null>	<null>
<null>	<null>	<null>	3	team3

 

FULL JOIN A IS NULL, B IS NULL -- mysql에는 full join이 없으므로 union으로 구현

SELECT * FROM `member` m
LEFT OUTER JOIN `team` t ON t.team_id = m.team_id
WHERE t.team_id IS NULL
UNION
SELECT * FROM `member` m
RIGHT OUTER JOIN `team` t ON t.team_id = m.team_id
where m.member_id IS NULL;

member_id	member_name	team_id	team_id	team_name
1	m1	0	<null>	<null>
<null>	<null>	<null>	3	team3

 

참고: 

https://yoo-hyeok.tistory.com/98