[SQL] SQL JOIN

1. 환경 셋업

1-1. DB생성 & 데이터 추가

참고: https://pauls-grit.tistory.com/36

[SQL] SQL 논리 연산자: AND, OR, NOT, BETWEEN, IN, LIKE

 

1-2. 새로운 테이블 & 데이터 추가

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
);

INSERT INTO snl_show 
VALUES (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', '옥주현');

 

1-3. 2개의 예제 테이블 확인

SELECT * FROM celeb;

SELECT * FROM snl_show;

 

2. INNER JOIN

두개의 테이블에서 공통된 요소들을 통해 결합하는 조인방식

 

snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 INNER JOIN

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb
INNER JOIN snl_show
ON celeb.name = snl_show.host;

 

3. LEFT JOIN

두개의 테이블에서 공통영역을 포함해 왼쪽 테이블의 다른 데이터를 포함하는 조인방식

 

snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 LEFT JOIN

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb
LEFT JOIN snl_show
ON celeb.name = snl_show.host;

 

4. RIGHT JOIN

두개의 테이블에서 공통영역을 포함해 오른쪽 테이블의 다른 데이터를 포함하는 조인방식

 

snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 RIGHT JOIN

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb
RIGHT JOIN snl_show
ON celeb.name = snl_show.host;

 

5. FULL OUTER JOIN

두개의 테이블에서 공통영역을 포함하여 양쪽 테이블의 다른영역을 모두 포함하는 조인방식

 

snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 FULL OUTER JOIN

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: MySQL에서는 FULL OUTER JOIN을 지원하지 않음
• FULL OUTER JOIN은 LEFT JOIN과 RIGHT JOIN을 UNION하는 것과 동일한 결과

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;

 

6. SELF JOIN

snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 SELF JOIN

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb, snl_show
WHERE celeb.name = snl_show.host;

 

celeb 테이블의 연예인 중, snl_show 에 host 로 출연했고, 영화배우는 아니면서 YG 엔터테이먼트 소속이거나 40세 이
상이면서 YG 엔터테이먼트 소속이 아닌 연예인의 이름과 나이, 직업, 소속사, 시즌, 에피소드 정보를 검색

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엔터테이먼트'));

 

snl_show 시즌 8 에 출연한 celeb 중, 에피소드 7, 9, 10 중에 출연했거나 소속사가 YG로 시작하고 뒤에 6글자로 끝나
는 사람 중 2020년 9월 15일 이후에 출연했던 사람을 검색

SELECT name, season, episode, broadcast_date, agency
FROM celeb, snl_show
WHERE name = host
AND (episode IN (7, 9, 10) OR agency like 'YG______')
AND broadcast_date > '2020-09-15';