[MySQL] JOIN (INNER, OUTER[LEFT, RIGHT]) 정리

 

[해당 포스트는 개인적으로 공부를 하고 차후에 참고용으로 하고자 작성한 것입니다.
따라서 잘못된 부분이나 부족한 부분이 있을 수 있기에 참고하시기 바랍니다.]

 

두 개 이상의 Table을 조합하여 결과를 출력해야 할 때 사용되는 것이 JOIN이다.

JOIN은 크게 INNER, OUTER JOIN으로 구분되며, OUTER JOIN에선 LEFT OUTER JOIN과 RIGHT OUTER JOIN으로 구분된다.

 

OUTER JOIN에선 LEFT, RIGHT 말고도 추가로 FULL OUTER JOIN도 존재한다. 다만 MySQL에선 FULL OUTER JOIN이 지원이 되지 않기에 INNER JOIN과 OUTER JOIN을 활용해서 만들어야 한다.

JOIN의 처리에서 어느 테이블을 먼저 읽을지를 결정하는 것은 상당히 중요하다. 처리할 작업량에 따라 속도가 달라지기 때문이다. INNER JOIN은 어느 테이블을 먼저 읽어도 결과가 달라지지 않기에 MySQL Optimizer가 JOIN의 순서를 조절해서 다양한 방법으로 최적화를 수행할 수 있다. 그러나 OUTER JOIN은 반드시 OUTER가 되는 테이블을 먼저 읽어야 하기 때문에 JOIN 순서를 Optimizer가 선택할 수 없다.

 

이번 예제는 단순히 JOIN이 어떻게 돌아가는지에 대해서만 알아보기 위해 간단한 예제로 실습해보자.

Table은 Client와 Food 테이블이 존재한다. Client는 사용자 정보가 저장되며 흥미를 가지고 있는 요리의 Key를 가지고 있다. Food는 각 Key별 음식 이름이랑 칼로리가 나와있다.

 

Client Table

 

Food Table

 

create database ldhTest;

use ldhTest;

-- Client 테이블 생성
CREATE TABLE client
(
	id INT NOT NULL primary key auto_increment,
    name VARCHAR(40) NOT NULL,
    age INT NOT NULL,
    interest INT
);

-- Food 테이블 생성
CREATE TABLE food
(
	food_id INT NOT NULL primary key auto_increment,
    food_name VARCHAR(40) NOT NULL,
    food_kcal INT
);

-- Client 테이블에 값 추가
INSERT INTO client (name, age, interest) VALUES ('DHL', 27, '1');
INSERT INTO client (name, age, interest) VALUES ('EHL', 29, '3');
INSERT INTO client (name, age, interest) VALUES ('FHL', 27, '4');
INSERT INTO client (name, age, interest) VALUES ('GHL', 30, '2');
INSERT INTO client (name, age, interest) VALUES ('HHL', 24, '6');
INSERT INTO client (name, age, interest) VALUES ('IHL', 20, '2');
INSERT INTO client (name, age, interest) VALUES ('JHL', 26, '3');
INSERT INTO client (name, age, interest) VALUES ('KHL', 24, NULL);
INSERT INTO client (name, age, interest) VALUES ('LHL', 23, NULL);
INSERT INTO client (name, age, interest) VALUES ('MHL', 21, NULL);

-- Food 테이블에 값 추가
INSERT INTO food (food_name, food_kcal) VALUES ('튀김', 300);
INSERT INTO food (food_name, food_kcal) VALUES ('삼겹살', 200);
INSERT INTO food (food_name, food_kcal) VALUES ('빠다코코낫', 150);
INSERT INTO food (food_name, food_kcal) VALUES ('목살', 150);
INSERT INTO food (food_name, food_kcal) VALUES ('돈까스', 250);
INSERT INTO food (food_name, food_kcal) VALUES ('어묵튀김', 200);
INSERT INTO food (food_name, food_kcal) VALUES ('양파튀김', 150);

 

JOIN을 활용하여 각자 어떤 음식을 좋아하는지 한 눈에 알아보도록 하자.

 

INNER JOIN

출처: https://ko.wikipedia.org/wiki/%EA%B5%90%EC%A7%91%ED%95%A9

일반적인 JOIN을 INNER JOIN을 칭한다.

서로 공통된 부분만 가져와 출력킨다. 다이어그램으로 표시하면 교집합에 포함된다.

MySQL에서 INNER JOIN은 Nested-Loop 방식을 사용한다. Nested-Loop 방식은 중첩 된 For나 WHILE과 같은 반복 루프 문장을 실행하는 형태로 동작하는 것을 말한다. 특징이 있다하면 INNER Table의 조건에 맞지 않는 레코드는 모두 버려버린다. 즉, Outer Table의 결과를 모두 버린다.

 

FOR ( record1 IN TABLE1 ) { // OUTER
	FOR ( record2 IN TABLE2 ) { // INNER
		IF ( record1.join_column == record2.join_column ) {
			join_record_found(record1.*, record2.*);
		} ELSE {
			join_record_notfound();
 		}
 	}
}

 

외부 테이블 (Outer Table)에서 조건에 만족하는 레코드를 검색한 후, JOIN 조건에 일치하는 레코드를 INNER TABLE에서 JOIN 하는 방식이다. Outer Table에서 만족하는 레코드 개수가 적을수록 속도가 빨라지는 특징을 가진다.

 

 

SELECT 컬럼 FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.Key = 테이블2.Key;
SELECT 컬럼 FROM 테이블1 JOIN 테이블2 ON 테이블1.Key = 테이블2.Key;

 

INNER JOIN은 단순히 JOIN 키워드로만 해도 동작이 된다. ON 뒤에오 서로 공통적인 KEY를 기준으로 JOIN을 시켜준다.

 

select id, name, age, food_name, food_kcal from client inner join food on interest = food_id;
select id, name, age, food_name, food_kcal from client join food on interest = food_id;

 

OUTER JOIN

INNER JOIN에선 INNER Table에 일치하는 레코드가 있으면 가져오고, 일치하는게 없으면 버리는 걸 알 수 있다. 이와 다르게 OUTER JOIN에선 INNER Table에 일치하는 레코드가 없으면 모두 NULL로 채워서 가져온다.  즉, INNER JOIN에서는 일치하는 레코드를 찾지 못했을 때는 OUTER Table의 결과를 모두 버렸지만 OUTER JOIN에서는 OUTER Table의 결과를 버리지 않고 남겨준다.

이 또한 Outer Table에서 만족하는 레코드 개수가 적을수록 속도가 빨라지는 특징을 가진다.

 

FOR ( record1 IN TABLE1 ) { // OUTER
	FOR ( record2 IN TABLE2 ) { // INNER
		IF ( record1.join_column == record2.join_column ) {
			join_record_found(record1.*, record2.*);
		} ELSE {
			join_record_found(record1.*, NULL);
 		}
 	}
}

 

OUTER JOIN에선 조인의 결과를 결정하는 OUTER Table이 JOIN의 위치에 따라서 LEFT, RIGHT로 나눠진다.

 

1. LEFT OUTER JOIN

 

LEFT OUTER JOIN은 왼쪽 테이블을 기준으로 JOIN 조건이 일치하는 값들과 일치하지 않는 값을 출력한다. 일치하는 값이 없다면 NULL을 리턴하여 채워준다.

 

select id, name, age, food_name, food_kcal from client left outer join food on interest = food_id;

 

 

2. RIGHT OUTER JOIN

LEFT OUTER JOIN과 비슷하게 동작하지만 오른쪽 테이블을 기준으로 출력시킨다.

 

select id, name, age, food_name, food_kcal from client right outer join food on interest = food_id;

 

참고
사진: https://ko.wikipedia.org/wiki/%EA%B5%90%EC%A7%91%ED%95%A9
Nested Loop: https://12bme.tistory.com/165