mysql - JOIN(INNER, LEFT, RIGHT, OUTER, CROSS, SELF)

mysql의 꽃이라고 할 수 있는 JOIN에 대해 알아보자 사실 개념 자체는 어렵지 않고 쉬운편이나 생소에서 익숙해지는데 시간이 좀 걸리는 문법이다. 코테에서는 다양한 조건과 함께 나오고 JOIN이 많아질수록(많으면 좋지 않은 쿼리긴 하지만) 그만큼 햇갈리기 때문에 기본기를 확실히 해둘 필요가 있다.

 

조인의 종류는 기본적으로 다음과 같다.

예제테이블

-- 조인 연습용 테이블 생성
-- info Table
CREATE TABLE pokemon.SPokemon (
	number INT NOT NULL,
	name VARCHAR(1048) NOT NULL,
	type VARCHAR(1024) NOT NULL,
	PRIMARY KEY(number)
);

-- ability Table
DROP TABLE ability;
CREATE TABLE pokemon.ability (
	number INT NOT NULL,
	height FLOAT,
	weight FLOAT,
	attack INT,
	defense INT,
	speed INT,
	PRIMARY KEY(number)
);
-- Insert data
INSERT INTO SPokemon (number, name, type) 
	VALUES (10, 'caterpie', 'bug');
INSERT INTO SPokemon (number, name, type) 
	VALUES (25, 'pikachu', 'electric');
INSERT INTO SPokemon (number, name, type) 
	VALUES (26, 'raichu', 'electric');
INSERT INTO SPokemon (number, name, type) 
	VALUES (133, 'eevee', 'normal');
INSERT INTO SPokemon (number, name, type) 
	VALUES (152, 'chikorita', 'grass');

INSERT INTO pokemon.ability (`number`,height,weight,attack,defense,speed) VALUES
	 (10,0.3,2.9,30,35,45),
	 (25,0.4,6.0,55,40,90),
	 (125,1.1,30.0,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.0,40,15,60),
	 (470,1.0,25.5,110,130,95);
		
SELECT * FROM pokemon.ability;

SELECT * from SPokemon;

SPokemon Table / ability Table

 1. INNER JOIN

SELECT *
FROM SPokemon
INNER JOIN ability
ON SPokemon.number = ability.number;

 

2. LEFT, RIGHT JOIN

SELECT *
FROM SPokemon
LEFT JOIN ability
ON SPokemon.number = ability.number
WHERE type = 'electric';

SELECT *
FROM SPokemon
RIGHT JOIN ability
ON SPokemon.number = ability.number;

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

 

3. OUTER JOIN

OUTER JOIN은 mysql에서 지원하지 않기 때문에 left와 right를 각각 조인해준 후 UNION하는 방법을 사용한다.

SELECT *
FROM SPokemon 
LEFT JOIN ability
ON SPokemon.number = ability.number
UNION 
SELECT *
FROM SPokemon
RIGHT JOIN ability
ON SPokemon.number = ability.number

4. CROSS JOIN

모든 경우의 수를 다 조인 하는 것으로 로우가 굉장히 많아진다. 잘 사용하지는 않는다.

SELECT *
FROM SPokemon
CROSS JOIN ability;

5.  SELF JOIN

자기 자신과 INNER JOIN하는 경우가 SELF JOIN이다.

SELECT *
FROM SPokemon AS t1
INNER JOIN SPokemon AS t2
ON t1.number = t2.number;