Join 개요
inner join : 두 테이블 중 교집합만 결합해서 보여줌
left join : 두 테이블 중 왼쪽 테이블만 보여줌(오른쪽 테이블에선 공통된 것만 출력함)
right join : 두 테이블 중 오른쪽 테이블만 보여줌(왼쪽 테이블에선 공통된 것만 출력함)
full outer join : 두 테이블 중 합집합만 결합해서 보여줌
self join :가장 많이 사용함, 문법은 다르나 inner join과 같은 결과를 가져옴
셀프조인 예시
SELECT celeb.name, celeb.job_title
FROM celeb, snl_show
WHERE celeb.name = snl_show.host AND celeb.agency='안테나';
select celeb.name
from celeb, snl_show
where age >= 40 AND NOT agency = 'YG엔터테이먼트';
=> from절에 , snl_show테이블이 추가되서 유재석이 10번 출력됨실습환경 만들기
- 제로베이스 데이터베이스에 접속해서 celeb 테이블 확인
use zerobase;
select * from celeb;- 새로운 snl_show 테이블 만들기
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
);
DESC snl_show;- 추가한 테이블에서 데이터(행) 추가
#ID,시즌,에피소드, 방송날짜, 출연진
INSERT INTO snl_show VALUES (1, 8, 7, '2020-09-05', '강동원');
INSERT INTO snl_show VALUES (2, 8, 8, '2020-09-12', '유재석');
INSERT INTO snl_show VALUES (3, 8, 9, '2020-09-19', '차승원');
INSERT INTO snl_show VALUES (4, 8, 10, '2020-09-26', '이수현');
INSERT INTO snl_show VALUES (5, 9, 1, '2020-09-04', '이병헌');
INSERT INTO snl_show VALUES (6, 9, 2, '2021-09-11', '하지원');
INSERT INTO snl_show VALUES (7, 9, 3, '2021-09-18', '제시');
INSERT INTO snl_show VALUES (8, 9, 4, '2021-09-25', '조정석');
INSERT INTO snl_show VALUES (9, 9, 5, '2021-10-02', '조여정');
INSERT INTO snl_show VALUES (10, 9, 6, '2021-10-09', '옥주현');
select * from snl_show;
- JOIN 기본 (p11~12): 두 개 이상의 테이블을 결합하는 것
두개의테이블에서공통된요소들을통해결합하는조인방식
select * from celeb;
select * from snl_show;1. INNER JOIN
ㅁ3. inner join (p11~)
1. JOIN 기본 (p11~12): 두 개 이상의 테이블을 결합하는 것
두개의테이블에서공통된요소들을통해결합하는조인방식
- INNER JOIN (p14~16): 두테이블의 공통된 부분만 가져옴
INNER JOIN 문법
SELECT column1, column2, ...
FROM tableA
INNER JOIN tableB # 여기까지해도 조인안됨
ON tableA.column = tableB.column # 기준제시: 여기서 기준을 주어야함
WHERE condition;
ON을 안써주고 끝내면 생기는 오류
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 '' at line 3
SELECT celeb., snl_show.,
FROM celeb
INNER JOIN snl_show
ON celeb. = snl_show.
WHERE ;
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;
=> 해설
SELECT문
- 두 테이블의 컬럼의 이름이 같은 경우, 반드시 어느 테이블에 있는 컬럼인지 명시해줘야 함 ex> celeb.id, snl_show.id
- 한 테이블에만 있는 컬럼의 경우, 테이블 명시 안해줘도 됨 , 시스템이 알아서 인지, 인간의 편의를 위해 써주기
ex> celeb.name을 그냥 name이라고 명시해줘도 됨
ON(기준점): celeb테이블의 이름과 snl_show의 호스트가 같은 경우에 가져옴
celeb snl_show
id | name | id | host |
+----+--------+----+--------+
| 4 | 강동원 | 1 | 강동원 |
| 5 | 유재석 | 2 | 유재석 |
| 6 | 차승원 | 3 | 차승원 |
| 7 | 이수현 | 4 | 이수현 |
=> 공통된 영역의 데이터만 가져왔음
SELECT *
FROM celeb
INNER JOIN snl_show
ON celeb.name = snl_show.host; 2. LEFT JOIN
-
LEFT JOIN: 왼쪽 테이블의 모든 데이터를 포함
두개의 테이블에서 공통영역을 포함해 왼쪽 테이블의 다른 데이터를 포함하는 조인방식 -
LEFT JOIN 문법
-
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;
celeb snl_show
id | name | id | host |
+----+--------+------+--------+
| 1 | 아이유 | NULL | NULL |
| 2 | 이미주 | NULL | NULL |
| 3 | 송강 | NULL | NULL |
| 4 | 강동원 | 1 | 강동원 |
| 5 | 유재석 | 2 | 유재석 |
| 6 | 차승원 | 3 | 차승원 |
| 7 | 이수현 | 4 | 이수현 |
=>celeb(left 테이블)을 기준으로 가져옴3. RIGHT JOIN
-
RIGHT JOIN: 오른쪽 테이블의 모든 데이터를 포함
두개의 테이블에서 공통영역을 포함해 오른쪽 테이블의 다른 데이터를 포함하는 조인방식
s -
RIGHT JOIN 문법
-
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;
celeb snl_show
| 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 | 옥주현 |
+------+--------+----+--------+
=>snl_show(right 테이블)을 기준으로 가져옴4. FULL OUTER JOIN
1.FULL OUTER JOIN: 양쪽 테이블의 모든 데이터를 포함하는 조인방식
두개의 테이블에서 공통영역을 포함하여 양쪽 테이블의 다른 영역을 모두 포함하는 조인방식
-
FULL OUTER JOIN 문법 - SQL
-
FULL OUTER JOIN 예제 - SQL
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;
=> MySQL에서는 full outer join을 지원하지 않음
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-
FULL OUTER JOIN 문법 - MySQL
MySQL 에서는FULL JOIN 을지원하지않으므로다음의쿼리로같은결과를만들수있다. -
FULL OUTER JOIN 예제 - MySQL
snl_show 에호스트로출연한 celeb 을기준으로
celeb 테이블과 snl_show 테이블을 FULL OUTER JOIN
- UNION 사용하여 합집합(full outer join) 표기
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;
celeb snl_show
+------+--------+------+--------+
| 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 | 옥주현 |
+------+--------+------+--------+5. SELF JOIN
가장 많이 사용함
문법은 다르나 inner join과 같은 결과를 가져옴
(조인하는 테이블간에 공통된 데이터를 가져옴)
SELF JOIN 문법
SELECT column1, column2, ...
FROM tableA, tableB, ...
WHERE condition;
SELF JOIN 예제 - 1 (p33~34)
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
| id | name | id | host |
+----+--------+----+--------+
| 4 | 강동원 | 1 | 강동원 |
| 5 | 유재석 | 2 | 유재석 |
| 6 | 차승원 | 3 | 차승원 |
| 7 | 이수현 | 4 | 이수현 |SELF JOIN 예제 - 2 (p35~38) STEP3
celeb 테이블의 연예인 중
snl_show 에 host 로출연했고 소속사가 안테나인 사람의 이름과 직업을검색
SELECT celeb.name, celeb.job_title
FROM celeb, snl_show
WHERE celeb.name = snl_show.host AND celeb.agency='안테나';
=> where절에 기준을 명시(다른 조인의 on 역할)
celeb
| name | job_title |
+--------+------------+
| 유재석 | MC, 개그맨 |
SELF JOIN 예제 - 3 (p36~49) STEP 8.
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 age >= 40 AND NOT agency = '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 NOT agency = 'YG엔터테이먼트'));
=> <틀렸음> 1~3.
| name | age | job_title | agency | season | episode |
+--------+------+------------+----------------+--------+---------+
| 유재석 | 50 | MC, 개그맨 | 안테나 | 8 | 7 |
| 유재석 | 50 | MC, 개그맨 | 안테나 | 8 | 8 |
| 유재석 | 50 | MC, 개그맨 | 안테나 | 8 | 9 |
| 이수현 | 23 | 가수 | YG엔터테이먼트 | 8 | 10 |
| 유재석 | 50 | MC, 개그맨 | 안테나 | 8 | 10 |
| 유재석 | 50 | MC, 개그맨 | 안테나 | 9 | 1 |
| 유재석 | 50 | MC, 개그맨 | 안테나 | 9 | 2 |
| 유재석 | 50 | MC, 개그맨 | 안테나 | 9 | 3 |
| 유재석 | 50 | MC, 개그맨 | 안테나 | 9 | 4 |
| 유재석 | 50 | MC, 개그맨 | 안테나 | 9 | 5 |
| 유재석 | 50 | MC, 개그맨 | 안테나 | 9 | 6 |
1. where절 host AND뒤 문장전체괄호 명시해야함 AND((~~, 트'));
=> '괄호 없으면 [snl출연자이면서 영화배우X, yg 소속 이거나]
(★snl출연자가 아니어도 모든 테이블 중) 나이가 40이상이면서 yg아닌 사람출력함 : 유재석이 10번 출력되었음(모든 시즌, 모든에피소드에 유재석이 출력됨)
=> p46~47 오류 동일: where문부터~ 'YG엔터테이먼트'까지 먼저 실행되고
select celeb.name, celeb.age, celeb.job_title, celeb.agency, snl_show.season, snl_show.episode
from celeb, snl_show
where age >= 40 AND NOT agency = 'YG엔터테이먼트';
이코드 = or뒤 코드도 출력됨, 기준이 엉망★이 되서 위와 같이 출력
(유재석이 시즌 8에 에피소드 8에만 출현했음에도)
select celeb.name
from celeb, snl_show
where age >= 40 AND NOT agency = 'YG엔터테이먼트';
=> from절에 , snl_show테이블이 추가되서 유재석이 10번 출력됨
2. 'YG 엔터테이먼트'에 띄어쓰기 있으면 안됨
3. ※NOT agency = 'YG 엔터테이먼트'와 agency != 'YG 엔터테이먼트'는 둘 중
아무거나 써도 OK
p48~49
(정답)
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 |
p46~47 조건 에러
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엔터테이먼트';SELF JOIN 예제 - 4 (p50~53)
CASE 1. 칼럼명앞에테이블명을명시
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;
=> 이너조인처럼 셀프조인은 공통된 영역만 가져옴CASE 2. 칼럼명만명시(에러 - 동일한칼럼명인경우)
SELECT id, season, episode, name, job_title
FROM celeb, snl_show
WHERE name = host;
=> ERROR 1052 (23000): Column 'id' in field list is ambiguousCASE 3. 동일한칼럼명이존재하는경우에만테이블명을암시
SELECT snl_show.id, season, episode, name, job_title
FROM celeb, snl_show
WHERE name = host;
=> snl_show.id만 명시해줘도 시스템상 문제는 없음, 그러나 가독성을 위해 모든 테이블을 명시해주는게 좋음.SELF JOIN 예제 - 5 (p54~62), STEP6
snl_show 에출연한 celeb 중, 에피소드 7, 9, 10 중에출연했거나
소속사가 YG로시작하고 뒤에 6글자로 끝나는사람 중 작년 9월 15일이후에 출연했던사람을검색
p62
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';
=> 언더바 6개
| name | season | episode | broadcast_date | agency |
+--------+--------+---------+----------------+----------------+
| 차승원 | 8 | 9 | 2020-09-19 | YG엔터테이먼트 |
| 이수현 | 8 | 10 | 2020-09-26 | YG엔터테이먼트 |
문제 2개 (p65~66)
- snl_show 에출연한 celeb 테이블의연예인중, 영화배우나 텔런트가 아닌 연예인의 아이디, 이름, 직업, 시즌, 에피소드정보를 검색하세요.
select celeb.name, celeb.job_title, snl_show.season, snl_show.episode
from celeb, snl_show
where celeb.name = snl_show.host
AND (NOT job_title LIKE '%영화배우%' and NOT job_title LIKE '%텔런트%');
=> AND NOT (job_title LIKE '%영화배우%' OR job_title LIKE '%텔런트%'); 이렇게 명시해줘도 같은 결과
(A 또는 B가 아니다 = A도 B도 아니다, not(AorB)=not A and not B #드모르간법칙)
| name | job_title | season | episode |
+--------+------------+--------+---------+
| 유재석 | MC, 개그맨 | 8 | 8 |
| 이수현 | 가수 | 8 | 10 |
- snl_show 에출연한 celeb 중, 작년(2020년) 9월 15일이후에출연했거나
소속사이름이 ‘엔터테이먼트’ 로끝나지않으면서영화배우나개그맨이아닌연예인의 celeb 아이디, 이름, 직업, 소속사를검색하세요.
select celeb.id, name, job_title, agency, snl_show.BROADCAST_DATE
from celeb, snl_show
where celeb.name = snl_show.host
AND ((BROADCAST_DATE > 20200915 or NOT agency like '%엔터테이먼트')
and not (job_title like '%영화배우%' or job_title like '%개그맨%'));
=> 틀림: BROA부터~'%엔터테이먼트'까지 괄호를 안해주면 915 or NO부분의 OR 때문에 방송날짜가 2020년9월 15일 이후만 충족하는 차승원도 출력되어버림
(celeb네임에 있고 snl쇼에 출연한 조건은 충족된 상태에서)
이런 이유로 괄호를 자주 잘 사용해서 명시해주는 습관이 틀리지 않아 좋음.
(※이번 경우 and뒤 문장전체괄호는 안해줘도 무방했음: 뒤의 문장이 and, and로 묶였기 때문, 뒤의 문장이 or로 묶이면 결과 엉망으로 출력됨, 이때는 전체 괄호필수★,
아래 ※참조코드#1,2 참고하기)
| id | name | job_title | agency |
+----+--------+----------------+----------------+
| 6 | 차승원 | 영화배우, 모델 | YG엔터테이먼트 |
| 7 | 이수현 | 가수 | YG엔터테이먼트 |
=> 정답출력
| id | name | job_title | agency | BROADCAST_DATE |
+----+--------+-----------+----------------+----------------+
| 7 | 이수현 | 가수 | YG엔터테이먼트 | 2020-09-26 |
----------------------------------
※참조코드★
#1. 엉망 출력
select celeb.id, name, job_title, agency, snl_show.BROADCAST_DATE
from celeb, snl_show
where celeb.name = snl_show.host
AND (BROADCAST_DATE > 20200915 or NOT agency like '%엔터테이먼트')
or not (job_title like '%영화배우%' or job_title like '%개그맨%');
#2. 바른 출력
select celeb.id, name, job_title, agency, snl_show.BROADCAST_DATE
from celeb, snl_show
where celeb.name = snl_show.host
AND ((BROADCAST_DATE > 20200915 or NOT agency like '%엔터테이먼트')
or not (job_title like '%영화배우%' or job_title like '%개그맨%'));