[MySQL] 테이블 조인 (1)

Mysql 조인 방식은 네스트드 루프로 알려진 중첩된 루프와 같은 형태이다.

조인의 종류

INNER JOIN과 OUTER JOIN으로 구별된다.

OUTER JOIN은 다시 LEFT OUTER JOIN(LEFT JOIN)과 RIGHT OUTER JOIN(RIGHT JOIN), FULL OUTER JOIN으로 구분할 수 있다.

그리고 NATURAL JOIN과 CROSS JOIN(FULL JOIN, CARTESIAN JOIN)으로도 구분할 수 있다.

조인의 처리에서 어느 테이블을 먼저 읽을지를 결정하는 것은 상당히 중요하다.

작업량 자체가 달라지기 때문이다.

INNER JOIN은 어느 테이블을 먼저 읽어도 달라지지 않으므로 옵티마이저가 조인의 순서를 조절해서 다양한 방법으로 최적화를 수행할 수 있다.

OUTER JOIN은 반드시 OUTER가 되는 테이블을 먼저 읽어야 하기 때문에 조인 순서를 옵티마이저가 선택할 수 없다.

주도적 역활을 하는 테이블을 드라이빙 테이블, 조인에서 끌려가는 역할을 하는 것은 드리븐 테이블 이라고도 한다.

INNER JOIN

네스티드-루프방식은 아래와 같다.
결국 안쪽 테이블에 의해 최종적으로 선택될 레코드가 결정된다. 이런 방식을 INNER JOIN이라고 한다.
조건이 안맞아 포함되지 않는 테이블은 결과에 나오지 않는다.

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 JOIN
INNER JOIN과 다른점은 TABLE2 조건에 만족하지 않은 레코드가 없는 경우에는 TABLE2의 칼럼을 모두 NULL로 채운다.

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);        //여기다. null
		}
	}
}

결과적으로 INNER JOIN에서는 일치하는 레코드를 찾지 못했을때는 TABLE1의 결과를 모두 버리지만
OUTER JOIN에서는 TABLE1의 결과를 버리지 않고 그대로 결과에 포함한다

INNER 테이블이 조인의 결과에 전혀 영향을 미치지 않고 OUTER 테이블의 내용에 따라 조인의 결과가 결정되는 것이 OUTER JOIN의
특징이다.

OUTER테이블의 레코드가 INNER테이블에 일치하는 레코드가 없다고 버려지지 않는다.
(없으면 NULL로 나온다)

OUTER JOIN은 조인의 결과를 결정하는 아우터 테이블이 왼쪽이냐 오른쪽이냐에 따라 LEFT, RIGHT가 결정된다.



Mysql에서는 FULL OUTER JOIN을 지원하지 않는다.


LEFT OUTER JOIN에서 주의해야할 것

1. Mysql의 실행계획은 INNER JOIN을 사용했는지 OUTER JOIN을 사용했는지를 알려주지 않으므로 OUTER JOIN을 의도한 쿼리가 INNER JOIN으로 실행되지는 않는지 주의해야한다.

2. OUTER JOIN에서 레코드가 없을 수도 있는 쪽을 테이블에 대한 조건은 반드시 LEFT JOIN의 ON절에 명시해야한다.
안그러면 옵티마이저가 내부적으로 INNER JOIN으로 처리할 수도 있다.
LEFT OUTER JOIN의 ON 절에 명시되는 조건은 조인되는 레코드가 있을 때만 적용된다. 
하지만 WHERE 절대 명시되는 조건은 OUTER JOIN이나 INNER JOIN에 관계없이 조인된 결과에 대해 모두 적용된다.
그래서 OUTER JOIN으로 연결되는 테이블이 있는 쿼리에서는 가능하다면 모든 조건을 ON절에 명시하는 습관을 들이는 것이 좋다.

아래를 한번보자.

SELECT * FROM a A
LEFT JOIN s S ON s.test = a.test
WHERE s.test2 > 1000;

위 쿼리는 LEFT JOIN절과 WHERE절은 서로 충돌되는 방식으로 사용되었다.
OUTER JOIN으로 연결되는 테이블의 칼럼에 대한 조건이 ON 절에 명시되지 않고 WHERE 절에 명시되었기 때문이다.
그래서 Mysql 서버는 이 쿼리를 최적화 단계에서 다음과 같은 쿼리로 변경한 후 실행한다.
SELECT * FROM a A
INNER JOIN s S ON s.test = a.test
WHERE s.test2 > 1000;

수정한다면,
SELECT * FROM a A
LEFT JOIN s S ON s.test = a.test AND s.test2 > 1000;

또는
SELECT * FROM a A 
INNER JOIN s S ON s.test = a.test
WHERE s.test2 > 1000;