SQL 기초 - JOIN

Jungmin·2022년 11월 4일

SQL

목록 보기

6/17

실습 환경 설정 (테이블 추가)

mysql> use zerobase;
Database changed
mysql> CREATE TABLE SNL_SHOW
    -> (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> SEASON INT NOT NULL,
    -> EPISODE INT NOT NULL,
    -> BROADCAST_DATE DATE,
    -> HOST VARCHAR(32) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

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)

✨NOT NULL : NULL값 허용X
✨AUTO_INCREMENT : 입력하지 않는 경우 자동으로 증가되는 값 가짐.

데이터 추가 후 확인

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.00 sec)

⏹️ JOIN

: 두 개 이상의 테이블을 결합하는 것

INNER JOIN

: 두 개의 테이블에서 공통된 요소들을 통해 결합하는 조인방식
SELECT COL1, COL2,... FROM 테이블명1 INNER JOIN 테이블명2 ON 테이블명1.COLUMN=테이블명2.COLUMN WHERE 조건 ;

SNL_SHOW에 호스트로 출연한 연예인을 기준으로 CELEB테이블과 SNL_SHOW 테이블을 INNER JOIN

mysql> SELECT CELEB.ID,CELEB.NAME,SNL_SHOW.ID,SNL_SHOW.HOST FROM CELEB
    -> INNER JOIN SNL_SHOW
    -> ON CELEB.NAME = SNL_SHOW.HOST;
+----+--------+----+--------+
| ID | NAME   | ID | HOST   |
+----+--------+----+--------+
|  4 | 강동원 |  1 | 강동원 |
|  5 | 유재석 |  2 | 유재석 |
|  6 | 차승원 |  3 | 차승원 |
|  7 | 이수현 |  4 | 이수현 |
+----+--------+----+--------+
4 rows in set (0.01 sec)

LEFT JOIN

: 두개의 테이블에서 공통영역을 포함해 왼쪽 테이블의 다른 데이터를 포함하는 조인방식
SELECT COL1, COL2,... FROM 테이블명A LEFT JOIN 테이블명B ON 테이블명A.COLUMN=테이블명B.COLUMN WHERE 조건 ;

위의 INNER JOIN 예제 문제를 LEFT JOIN 방식으로 검색하면 공통된 부분 외에 왼쪽(CELEB) 나머지 데이터도 확인된다.

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 COL1, COL2,... FROM 테이블명A RIGHT JOIN 테이블명B ON 테이블명A.COLUMN=테이블명B.COLUMN WHERE 조건 ;

SNL_SHOW 출연한 호스트를 기준으로 CELEB과 SNL_SHOW 테이블 RIGHT JOIN

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 COL1, COL2,... FROM 테이블명A FULL OUTER JOIN 테이블명B ON 테이블명A.COLUMN=테이블명B.COLUMN WHERE 조건 ;
❗Mysql에서는 지원하지 않으므로 다음과 같은 쿼리로 같은 결과를 만들 수 있다.
SELECT COL1, COL2,.. FROM 테이블A LEFT JOIN 테이블B ON 테이블A.COLUMN = 테이블B.COLUMN UNION SELECT COL1, COL2,.. FROM 테이블A RIGHT JOIN 테이블B ON 테이블A.COLUMN = 테이블B.COLUMN WHERE 조건;

SNL_SHOW 출연한 호스트를 기준으로 CELEB과 SNL_SHOW 테이블 FULL OUTER JOIN

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과 같이 공통된 요소들을 가져오는 조인방식 (테이블 2개이상 가능)
SELECT COL1,COL2 FROM 테이블A,테이블B,...WHERE 조건;

SNL_SHOW 출연한 연예인 기준으로 CELEB테이블과 SNL_SHOW 테이블 SELF JOIN

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)

CELEB테이블 연예인 중 SNL_SHOW 호스트로 출연했고, 소속사가 안테나인 사람의 이름과 직업 검색

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)

CELEB테이블 연예인 중 SNL_SHOW에 출연했고, 영화배우는 아니면서 YG소속이거나 40세 이상이면서 YG소속이 아닌 연예인의 이름, 나이, 직업, 소속사, 시즌, 에피소드 정보 검색

mysql> SELECT CELEB.NAME,CELEB.AGE,CELEB.JOB_TITLE,CELEB.AGENCY,
    ->          SNL_SHOW.SEASON,SNL_SHOW.EPISODE
    -> FROM CELEB,SNL_SHOW
    -> WHERE CELEB.NAME = SNL_SHOW.HOST
    -> AND((NOT JOB_TITLE LIKE '%영화배우%' AND AGENCY ='YG엔터테이먼트')
    -> OR (AGE >=40 AND AGENCY != 'YG엔터테이먼트'));
+--------+------+-----------+----------------+--------+---------+
| NAME   | AGE  | JOB_TITLE | AGENCY         | SEASON | EPISODE |
+--------+------+-----------+----------------+--------+---------+
| 유재석 |   50 | MC,개그맨 | 안테나         |      8 |       8 |
| 이수현 |   23 | 가수      | YG엔터테이먼트 |      8 |      10 |
+--------+------+-----------+----------------+--------+---------+
2 rows in set (0.00 sec)

SNL_SHOW 출연 연예인의 SNL_SHOW ID, 시즌, 에피소드, 이름, 직업 정보 검색

mysql> SELECT SNL_SHOW.ID,SNL_SHOW.SEASON,SNL_SHOW.EPISODE,
    ->          CELEB.NAME,CELEB.JOB_TITLE
    -> FROM CELEB, SNL_SHOW
    -> WHERE CELEB.NAME = SNL_SHOW.HOST;
+----+--------+---------+--------+-----------------+
| ID | SEASON | EPISODE | NAME   | JOB_TITLE       |
+----+--------+---------+--------+-----------------+
|  1 |      8 |       7 | 강동원 | 영화배우,탤런트 |
|  2 |      8 |       8 | 유재석 | MC,개그맨       |
|  3 |      8 |       9 | 차승원 | 영화배우,모델   |
|  4 |      8 |      10 | 이수현 | 가수            |
+----+--------+---------+--------+-----------------+
4 rows in set (0.00 sec)

❗ 컬럼명만 넣어도 되긴 하지만 두 테이블 사이에 동일명의 컬럼이 있을 경우 에러 발생.

⭕ 중복 컬럼명 없이 쿼리 보내는 경우

mysql> SELECT SEASON, EPISODE, NAME, JOB_TITLE
 -> FROM CELEB,SNL_SHOW
 -> WHERE NAME=HOST;
	+--------+---------+--------+-----------------+
	| SEASON | EPISODE | NAME   | JOB_TITLE       |
	+--------+---------+--------+-----------------+
	|      8 |       7 | 강동원 | 영화배우,탤런트 |
	|      8 |       8 | 유재석 | MC,개그맨       |
	|      8 |       9 | 차승원 | 영화배우,모델   |
	|      8 |      10 | 이수현 | 가수            |
	+--------+---------+--------+-----------------+
	4 rows in set (0.00 sec)




❌ 두 테이블 간 중복된 컬럼명 포함하는 경우 ('ID'컬럼은 두 테이블에 존재)

	mysql> SELECT ID,SEASON,EPISODE,NAME,JOB_TITLE
 -> FROM CELEB,SNL_SHOW
 -> WHERE NAME=HOST;
	ERROR 1052 (23000): Column 'ID' in field list is ambiguous

SNL_SHOW 출연 연예인 중, 에피소드 7,9,10 중 출연했거나 소속사가 YG로 시작하고 뒤에 6글자로 끝나는 사람 중 작년 9/15일 이후 출연했던 데이터 검색

mysql> SELECT NAME, AGENCY, EPISODE, BROADCAST_DATE, HOST
    -> FROM CELEB,SNL_SHOW
    -> WHERE NAME = HOST
    -> AND (EPISODE IN (7,9,10) OR AGENCY LIKE 'YG______') AND BROADCAST_DATE >  '2020-09-15';
+--------+----------------+---------+----------------+--------+
| NAME   | AGENCY         | EPISODE | BROADCAST_DATE | HOST   |
+--------+----------------+---------+----------------+--------+
| 차승원 | YG엔터테이먼트 |       9 | 2020-09-19     | 차승원 |
| 이수현 | YG엔터테이먼트 |      10 | 2020-09-26     | 이수현 |
+--------+----------------+---------+----------------+--------+
2 rows in set (0.00 sec)

SNL_SHOW 출연 연예인 중, 배우나 탤런트가 아닌 연예인의 아이디, 이름, 직업, 시즌, 에피소드 정보 검색

mysql> SELECT CELEB.ID, NAME, JOB_TITLE, SEASON, EPISODE
    -> FROM CELEB, SNL_SHOW
    -> WHERE NAME = HOST
    -> AND NOT (JOB_TITLE LIKE '%영화배우%' OR JOB_TITLE LIKE '%탤런트%');
+----+--------+-----------+--------+---------+
| ID | NAME   | JOB_TITLE | SEASON | EPISODE |
+----+--------+-----------+--------+---------+
|  5 | 유재석 | MC,개그맨 |      8 |       8 |
|  7 | 이수현 | 가수      |      8 |      10 |
+----+--------+-----------+--------+---------+
2 rows in set (0.00 sec)

Jungmin

데이터분석 스터디노트🧐✍️

이전 포스트

SQL 기초 - UNION

다음 포스트