SELECT column1, column2, ... FROM tableA
UNION \ UNION ALL
SELECT column1, column2, ... FROM tableB;
select * from test1
UNION ALL
select * from test2;
-> 중복값 상관없이 그대로 합쳐서 보여줌
select * from test1
UNION
select * from test2;
-> 중복값을 제거하여 하나씩만 보여줌
select name, sex, agency from celeb where sex='F'
UNION ALL
select name, sex, agency from celeb where agency='YG엔터테인먼트';
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', '강동원');
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, '2021-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', '옥주현');
두 개 이상의 테이블을 결합하는 것
INNER JOIN : 교집합(공통된 부분)
SELECT column1, column2, ...
FROM tableA
INNER JOIN tableB
ON tableA.column = tableB.column # 결합기준
WHERE condition;
select celeb.id, celeb.name, snl_show.id, snl_show.host
from celeb
INNER JOIN snl_show
ON celeb.name = snl_show.host;
SELECT column1, column2, ...
FROM tableA # left table
LEFT JOIN tableB # right table
ON tableA.column = tableB.column # 결합기준
WHERE condition;
select celeb.id, celeb.name, snl_show.id, snl_show.host
from celeb
LEFT JOIN snl_show
ON celeb.name = snl_show.host;
-> celeb 인물들은 모두 가져옴 + 공통
SELECT column1, column2, ...
FROM tableA # left table
RIGHT JOIN tableB # right table
ON tableA.column = tableB.column # 결합기준
WHERE condition;
select celeb.id, celeb.name, snl_show.id, snl_show.host
from celeb
RIGHT JOIN snl_show
ON celeb.name = snl_show.host;
-> snl 출연진은 모두 가져옴 + 공통
SELECT column1, column2, ...
FROM tableA
FULL OUTER JOIN tableB
ON tableA.column = tableB.column # 결합기준
WHERE condition;
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을 지원하지 않음!
따라서 다음의 쿼리로 같은 결과를 만들 수 있음
SELECT column1, column2, ...
FROM tableA
LEFT JOIN tableB ON tableA.column = tableB.column
UNION # 중복 제거하고 합침
SELECT column1, column2, ...
FROM tableA
RIGHT JOIN tableB ON tableA.column = tableB.column
WHERE condition;
-> 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;
SELECT column1, column2, ...
FROM tableA, tableB, ...
WHERE condition;
select celeb.id, celeb.name, snl_show.id, snl_show.host
from celeb, snl_show
where celeb.name = snl_show.host;
-> 교집합만 결합(INNER JOIN)
-> select하는 칼럼은 조건 상관없이 어느것이나 괜찮
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');
SELECT CONCAT('string1', 'string2', ...);
select CONCAT('이름 : ', name) from celeb;
SELECT column AS alias
FROM tablename;
-> 칼럼명 별칭 생성
SELECT column1, column2, ...
FROM tablename AS alias;
-> 테이블명 별칭 생성
select name as '이름' from celeb;
select name as '이름', agency as '소속사' from celeb;
select s.season, s.episode, c.name, c.job_title
from celeb AS c, snl_show AS s # 별칭을 사용하면 더 간편
where c.name = s.host;
select CONCAT(name, ' : ', job_title) AS profile from celeb;
select CONCAT(s.season, '-', s.episode, '(', s.broadcast_date, ')') AS '방송정보',
CONCAT(c.name, '(', c.job_title, ')') AS '출연자정보'
FROM celeb AS c, snl_show AS s
where c.name = s.host;
SELECT DISTINCT column1, column2, ...
from tablename;
select DISTINCT agency from celeb;
select DISTINCT sex, job_title from celeb where job_title like '%가수%';
select column1, column2, ...
from tablename
where condition
LIMIT number;
select * from celeb LIMIT 3;
select * from celeb order by age LIMIT 4;
-> 나이가 가장 적은 연예인 4명 검색
<제로베이스 데이터 취업 스쿨>