SQL_Basic_07_Day 08. 테이블 합치기

DAY 08 테이블 합치기

---

1. 테이블 합치기

---

같은 의미를 가지는 컬럼의 값을 기준으로

테이블을 합칠때 사용하는 키워드

JOIN

 

JOIN의 종류

 

2. 기준으로 테이블 합치기

---

INNER JOIN

두 테이블모두에있는값만합치기

※ 기본 JOIN으로 JOIN만 적을시, 자동으로 INNER JOIN으로 인식된다.

 

 

INNER JOIN 문법

 

SELECT [컬럼이름]
FROM [테이블A 이름]
INNER JOIN [테이블B 이름]
ON [테이블A 이름].[컬럼A이름] = [테이블B 이름].[컬럼B이름]
WHERE 조건식;

 

INNER JOIN 예시

 

SELECT *
FROMmypokemon
INNER JOIN ability
ON mypokemon.number = ability.number;

 

 

3. 한쪽을 기준으로 테이블 합치기

---

LEFT JOIN
왼쪽테이블에 있는 값만 합치기

RIGHT JOIN
오른쪽테이블에 있는 값만 합치기

 

 

 

LEFT JOIN 문법

 

SELECT [컬럼이름]
FROM [테이블A 이름]
LEFT JOIN [테이블B 이름]
ON [테이블A 이름].[컬럼A이름] = [테이블B 이름].[컬럼B이름]
WHERE조건식;

 

RIGHT JOIN 문법

 

SELECT [컬럼이름]
FROM [테이블A 이름]
RIGHT JOIN [테이블B 이름]
ON [테이블A 이름].[컬럼A이름] = [테이블B 이름].[컬럼B이름]
WHERE 조건식;

 

 

LEFT JOIN 예시

 

SELECT *
FROMmypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number;

 

 

RIGHT JOIN 예시

 

SELECT *
FROMmypokemon
RIGHT JOIN ability
ON mypokemon.number = ability.number;

 

 

 

4. 다양한 방식으로 테이블 합치기

---

OUTER JOIN

두테이블에 있는 모든 값 합치기

※ OUTER JOIN은 MySQL 키워드에 없음

 

 OUTER JOIN 문법

 

SELECT [컬럼이름]
FROM[테이블A 이름]
LEFT JOIN [테이블B 이름]
ON [테이블A 이름].[컬럼A이름] = [테이블B 이름].[컬럼B이름]
UNION
SELECT [컬럼이름]
FROM[테이블A 이름]
RIGHT JOIN [테이블B 이름]
ON [테이블A 이름].[컬럼A이름] = [테이블B 이름].[컬럼B이름] ;

 

 

OUTER JOIN 예시

 

SELECT *
FROMmypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
UNION
SELECT *
FROMmypokemon
RIGHT JOIN ability
ON mypokemon.number = ability.number;

 

 

 

 

 

CROSS JOIN

두 테이블에 있는 모든값을 각각 합치기

 

CROSS JOIN 문법

 

SELECT [컬럼이름]
FROM[테이블A 이름]
CROSS JOIN [테이블B 이름]
WHERE 조건식;

 

 

CROSS JOIN 예시

 

SELECT *
FROMmypokemon
CROSS JOIN ability;

 

 

 

SELF JOIN

같은 테이블에 있는값 합치기

 

 

SELF JOIN 문법

 

SELECT [컬럼이름]
FROM [테이블A이름] AS t1
INNER JOIN [테이블A이름] AS t2
ON t1.[컬럼A 이름] = t2.[컬럼A 이름]
WHERE 조건식;

 

SELF JOIN 예시

 

SELECT *
FROMmypokemon AS t1
INNER JOIN mypokemon AS t2
ON t1.number = t2.number;

 

 

 

5. [실습] 다양한 방식으로 테이블을 합쳐보자 (JOIN)

---

TIP!같은 이름을 가지는 컬럼이 있다면 SELECT 해올 때,
어떤 테이블에서 합쳐진 컬럼을 가져올 것 인지 명시해줘야 한다.

 

INNER JOIN 예시결과

 

 

SELECT number (X) ERROR!
SELECT mypokemon.number또는 ability.number (O)

 

[포켓몬 쿼리 입력]

 

DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
	   number  INT,
       name	VARCHAR(20),
       type	VARCHAR(10)
);
INSERT INTO mypokemon (number, name, type)
VALUES (10, 'caterpie', 'bug'),
	   (25, 'pikachu', 'electric'),
       (26, 'raichu', 'electric'),
       (133, 'eevee', 'normal'),
       (152, 'chikoirita', 'grass');
CREATE TABLE ability (
	   number INT,
       height FLOAT,
       weight FLOAT,
       attack INT,
       defense INT,
       speed int
);
INSERT INTO ability (number, height, weight, attack, defense, speed)
VALUES (10, 0.3, 2.9, 30, 35, 45),
	   (25, 0.4, 6, 55, 40, 90),
       (125, 1.1, 30, 83, 57, 105),
	   (133, 0.3, 6.5, 55, 50, 55),
       (137, 0.8, 36.5, 60, 70, 40),
	   (152, 0.9, 6.4, 49, 65, 45),
       (153, 1.2, 15.8, 62, 80, 60),
       (172, 0.3, 2, 40, 15, 60),
       (470, 1, 25.5, 110, 130, 95);

 

 

MISSION (1)
포켓몬 테이블과 능력치 테이블을 합쳐서 포켓몬 이름, 공격력, 방어력을 한번에 가져와 주세요.
이 때, 포켓몬 테이블에 있는 모든 포켓몬의 데이터를 가져와 주세요. 만약, 포켓몬의 능력치 데이터를 구할 수 없다면, NULL을 가져와도 좋습니다.

 

SELECT name, attack , defense
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number;

 

 

MISSION (2)
포켓몬 테이블과 능력치 테이블을 합쳐서 포켓몬 번호와 이름을 한번에 가져와 주세요.
이 때, 능력치 테이블에 있는 모든 포켓몬의 데이터를 가져와 주세요. 만약, 포켓몬의 이름 데이터를 구할 수 없다면, NULL을 가져와도 좋습니다.
이름이 같은 콜럼이 2개가 되면 ability.number 어떤 number 콜럼 인지 확인해야 한다.  

 

SELECT ability.number , name
FROM mypokemon
RIGHT JOIN ability
ON mypokemon.number = ability.number ;

 

 

6. [실습] 테이블을 합쳐 원하는 값을 가져오자

---

[포켓몬 입력 쿼리]

 

DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number INT,
name VARCHAR(20),
type VARCHAR(10)
);
INSERT INTO mypokemon (number, name, type)
VALUES (10, 'caterpie', 'bug'),
(25, 'pikachu', 'electric'),
(26, 'raichu', 'electric'),
(133, 'eevee', 'normal'),
(152, 'chikoirita', 'grass');
CREATE TABLE ability (
number INT,
height FLOAT,
weight FLOAT,
attack INT,
defense INT,
speed int
);
INSERT INTO ability (number, height, weight, attack, defense, speed)
VALUES (10, 0.3, 2.9, 30, 35, 45),
(25, 0.4, 6, 55, 40, 90),
(26, 0.8, 30, 90, 55, 110),
(133, 0.3, 6.5, 55, 50, 55),
(137, 0.8, 36.5, 60, 70, 40),
(152, 0.9, 6.4, 49, 65, 45),
(153, 1.2, 15.8, 62, 80, 60),
(172, 0.3, 2, 40, 15, 60),
(470, 1, 25.5, 110, 130, 95);

 

 

MISSION (1)
내 포켓몬의 타입별 키의 평균을 가져와주세요.

 

SELECT type, AVG(height)
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
GROUP BY type;

 

 

 

MISSION (2)
내포켓몬의 타입별 몸무게의 평균을 가져와주세요.

 

SELECT type, AVG(weight)
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
GROUP BY type;

 

 

 

MISSION (3)
내포켓몬의 타입별 키의 평균과 몸무게의 평균을 함께 가져와주세요.

 

SELECT type, AVG(height) ,AVG(weight)
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
GROUP BY type;

 

 

 

MISSION (4)
번호가 100이상인 내포켓몬들의 번호, 이름, 공격력, 방어력을 가져와주세요.

 

SELECT mypokemon.number, name, attack, defense
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
WHERE mypokemon.number >= 100;

 

 

 

 

MISSION (5)
공격력과 방어력의 합이 큰 순서대로 내 포켓몬들의 이름을 나열해주세요.

 

SELECT name
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
ORDER BY attack + defense DESC;

 

 

 

MISSION (6)
속도가 가장 빠른 내포켓몬의 이름을 가져와주세요

 

SELECT name
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number 
ORDER BY speed DESC
limit 1;