[MySQL] JOIN

Bpius·2023년 11월 11일

Full Outer Join INNER JOIN JOIN LEFT JOIN RIGHT JOIN mysql self join

MySQL

목록 보기

8/15

아래 2개의 가상 셀럽 table로 실습을 진행해보자.

celeb table

mysql> desc celeb;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| ID        | int         | NO   | PRI | NULL    | auto_increment |
| NAME      | varchar(32) | NO   |     |         |                |
| BIRTHDAY  | date        | YES  |     | NULL    |                |
| AGE       | int         | YES  |     | NULL    |                |
| SEX       | char(1)     | YES  |     | NULL    |                |
| JOB_TITLE | varchar(32) | YES  |     | NULL    |                |
| AGENCY    | varchar(32) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> select * from celeb;
+----+-----------+------------+------+------+-------------------------+--------------------------+
| ID | NAME      | BIRTHDAY   | AGE  | SEX  | JOB_TITLE               | AGENCY                   |
+----+-----------+------------+------+------+-------------------------+--------------------------+
|  1 | 아이유    | 1993-05-16 |   29 | F    | 가수, 텔런트            | EDAM엔터테이먼트         |
|  2 | 이미주    | 1994-09-23 |   28 | F    | 가수                    | 울림엔터테이먼트         |
|  3 | 송강      | 1994-04-23 |   28 | M    | 텔런트                  | 나무엑터스               |
|  4 | 강동원    | 1981-01-18 |   41 | M    | 영화배우, 텔런트        | YG엔터테이먼트           |
|  5 | 유재석    | 1972-08-14 |   50 | M    | MC, 개그맨              | 안테나                   |
|  6 | 차승원    | 1970-06-07 |   48 | M    | 영화배우, 모델          | YG엔터테이먼트           |
|  7 | 이수현    | 1999-05-04 |   23 | F    | 가수                    | YG엔터테이먼트           |
+----+-----------+------------+------+------+-------------------------+--------------------------+
7 rows in set (0.00 sec)

show table

mysql> desc snl_show;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| ID             | int         | NO   | PRI | NULL    | auto_increment |
| SEASON         | int         | NO   |     | NULL    |                |
| EPISODE        | int         | NO   |     | NULL    |                |
| BROADCAST_DATE | date        | YES  |     | NULL    |                |
| HOST           | varchar(32) | NO   |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> select * from snl_show;
+----+--------+---------+----------------+-----------+
| ID | SEASON | EPISODE | BROADCAST_DATE | HOST      |
+----+--------+---------+----------------+-----------+
|  1 |      8 |       7 | 2020-09-05     | 강동원    |
|  2 |      8 |       8 | 2020-09-12     | 유재석    |
|  3 |      8 |       9 | 2020-09-19     | 차승원    |
|  4 |      8 |      10 | 2020-09-26     | 이수현    |
|  5 |      9 |       1 | 2021-09-04     | 이병헌    |
|  6 |      9 |       2 | 2021-09-11     | 하지원    |
|  7 |      9 |       3 | 2021-09-18     | 제시      |
|  8 |      9 |       4 | 2021-09-25     | 조정석    |
|  9 |      9 |       5 | 2021-10-02     | 조여정    |
| 10 |      9 |       6 | 2021-10-09     | 옥주현    |
+----+--------+---------+----------------+-----------+
10 rows in set (0.01 sec)

JOIN

두 개 이상의 table을 결합할 때 사용된다.
쿼리를 작성할 시, column부분에서 닷('.')을 사용한다. table.column으로 지정하여 table에 해당하는 column인 것을 명시해주어야 한다.

INNER JOIN

문법

select column1, column2, ...
from tableA
inner join tableB # join 방법
on tableA.column = tableB.column # 합칠 때 기준 컬럼
where condition; # 조건

ex) snl_show에 호스트로 출연한 celeb을 기준으로, celeb table과 snl_show table을 inner join하여 각 teble의 id와 이름 조회

mysql> select celeb.id, celeb.name, snl_show.id, snl_show.host
    -> from celeb
    -> inner join snl_show
    -> on celeb.name = snl_show.host; # join 기준
+----+-----------+----+-----------+
| id | name      | id | host      |
+----+-----------+----+-----------+
|  4 | 강동원    |  1 | 강동원    |
|  5 | 유재석    |  2 | 유재석    |
|  6 | 차승원    |  3 | 차승원    |
|  7 | 이수현    |  4 | 이수현    |
+----+-----------+----+-----------+
4 rows in set (0.00 sec)

LEFT JOIN

문법

select column1, column2, ...
from tableA
left join tableB # join 방법
on tableA.column = tableB.column # 합칠 때 기준 컬럼
where condition; # 조건

ex) snl_show에 호스트로 출연한 celeb을 기준으로, celeb table과 snl_show table을 left join하여 각 teble의 id와 이름 조회
아이유, 이미주, 송강은 snl_show에 출연하지 않았기에 NaN값으로 조회가 되었다.

mysql> select celeb.id, celeb.name, snl_show.id, snl_show.host
    -> from celeb
    -> left join snl_show
    -> on celeb.name = snl_show.host;
+----+-----------+------+-----------+
| id | name      | id   | host      |
+----+-----------+------+-----------+
|  1 | 아이유    | NULL | NULL      |
|  2 | 이미주    | NULL | NULL      |
|  3 | 송강      | NULL | NULL      |
|  4 | 강동원    |    1 | 강동원    |
|  5 | 유재석    |    2 | 유재석    |
|  6 | 차승원    |    3 | 차승원    |
|  7 | 이수현    |    4 | 이수현    |
+----+-----------+------+-----------+
7 rows in set (0.00 sec)

RIGHT JOIN

문법

select column1, column2, ...
from tableA
right join tableB # join 방법
on tableA.column = tableB.column # 합칠 때 기준 컬럼
where condition; # 조건

ex) snl_show에 호스트로 출연한 celeb을 기준으로, celeb table과 snl_show table을 right join하여 각 teble의 id와 이름 조회
이병헌, 하지원, 제시, 조정석, 조여정, 옥주현은 celeb table에 없는 데이터이기에 NaN값으로 조회가 되었다.

mysql> select celeb.id, celeb.name, snl_show.id, snl_show.host
    -> from celeb
    -> right join snl_show
    -> on celeb.name = snl_show.host;
+------+-----------+----+-----------+
| id   | name      | id | host      |
+------+-----------+----+-----------+
|    4 | 강동원    |  1 | 강동원    |
|    5 | 유재석    |  2 | 유재석    |
|    6 | 차승원    |  3 | 차승원    |
|    7 | 이수현    |  4 | 이수현    |
| NULL | NULL      |  5 | 이병헌    |
| NULL | NULL      |  6 | 하지원    |
| NULL | NULL      |  7 | 제시      |
| NULL | NULL      |  8 | 조정석    |
| NULL | NULL      |  9 | 조여정    |
| NULL | NULL      | 10 | 옥주현    |
+------+-----------+----+-----------+
10 rows in set (0.00 sec)

FULL OUTER JOIN

문법

select column1, column2, ...
from tableA
right join tableB # join 방법
on tableA.column = tableB.column # 합칠 때 기준 컬럼
where condition; # 조건

ex) snl_show에 호스트로 출연한 celeb을 기준으로, celeb table과 snl_show table을 full outer join하여 각 teble의 id와 이름 조회
에러 발생

# 지원하지 않는 형식
mysql> select celeb.id, celeb.name, snl_show.id, snl_show.host
    -> from celeb
    -> full outer join snl_show
    -> on celeb.name = snl_show.host;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join snl_show
on celeb.name = snl_show.host' at line 3

MySQL은 full outer join을 지원하지 않기에 다른 쿼리로 같은 결과를 만들어보자.
left join과 right join을 union하여 만들 수 있다.

문법

select column1, column2, ...
from tableA
left join tableB # join 방법
on tableA.column = tableB.column # 합칠 때 기준 컬럼
union # 중복은 제외
select column1, column2, ...
from tableA
right join tableB # join 방법
on tableA.column = tableB.column # 합칠 때 기준 컬럼
where condition;

ex) 다시 snl_show에 호스트로 출연한 celeb을 기준으로, celeb table과 snl_show table을 full outer join하여 각 teble의 id와 이름 조회
위 3행은 celeb에만 있는 데이터이기에 snl_show는 NaN값이고, 아래 6행은 snl_show에만 있는 데이터이기에 celeb은 NaN값이 조회가 되었다.

mysql> select celeb.id, celeb.name, snl_show.id, snl_show.host
    -> from celeb
    -> left join snl_show
    -> on celeb.name = snl_show.host
    -> union
    -> select celeb.id, celeb.name, snl_show.id, snl_show.host
    -> from celeb
    -> right join snl_show
    -> on celeb.name = snl_show.host;
+------+-----------+------+-----------+
| id   | name      | id   | host      |
+------+-----------+------+-----------+
|    1 | 아이유    | NULL | NULL      |
|    2 | 이미주    | NULL | NULL      |
|    3 | 송강      | NULL | NULL      |
|    4 | 강동원    |    1 | 강동원    |
|    5 | 유재석    |    2 | 유재석    |
|    6 | 차승원    |    3 | 차승원    |
|    7 | 이수현    |    4 | 이수현    |
| NULL | NULL      |    5 | 이병헌    |
| NULL | NULL      |    6 | 하지원    |
| NULL | NULL      |    7 | 제시      |
| NULL | NULL      |    8 | 조정석    |
| NULL | NULL      |    9 | 조여정    |
| NULL | NULL      |   10 | 옥주현    |
+------+-----------+------+-----------+
13 rows in set (0.00 sec)

SELF JOIN

inner join과 유사하고 table 간의 공통된 데이터를 조회하며 가장 자주 쓰이는 join 방법이다.

문법

select column1, column2, ...
from tableA, tableB, ... # 여러 개의 table
where condition; # where에 join 기준을 정의

ex) snl_show에 호스트로 출연한 celeb을 기준으로, celeb table과 snl_show table을 self join하여 각 teble의 id와 이름 조회

mysql> select celeb.id, celeb.name, snl_show.id, snl_show.host
    -> from celeb, snl_show
    -> where celeb.name = snl_show.host;
+----+-----------+----+-----------+
| id | name      | id | host      |
+----+-----------+----+-----------+
|  4 | 강동원    |  1 | 강동원    |
|  5 | 유재석    |  2 | 유재석    |
|  6 | 차승원    |  3 | 차승원    |
|  7 | 이수현    |  4 | 이수현    |
+----+-----------+----+-----------+
4 rows in set (0.00 sec)

ex) celeb table의 셀럽 중, snl_show에 host로 출연했고 소속사가 안테나인 셀럽의 이름과 직업을 조회

mysql> select celeb.name, celeb.job_title
    -> from celeb, snl_show
    -> where celeb.name = snl_show.host and celeb.agency = '안테나';
+-----------+---------------+
| name      | job_title     |
+-----------+---------------+
| 유재석    | MC, 개그맨    |
+-----------+---------------+
1 row in set (0.00 sec)

Bpius

데이터 굽는 타자기

이전 포스트

[MySQL] UNION

다음 포스트