본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
OUTER JOIN 주의사항
OUTER JOIN에서 OUTER로 조인되는 테이블의 칼럼에 대한 조건은 모두 ON절에 명시해야 한다. 조건을 ON 절에 명시하지 않고 다음 예제와 같이 OUTER 테이블의 칼럼이 WHERE 절에 명시하면 옵티마이저가 INNER JOIN과 같은 방법으로 처리한다.
SELECT * FROM employees e
LEFT JOIN dept_manager mgr ON mgr.emp_no=e.emp_no
WHERE mgr.dept_no='d001';
위의 쿼리는 ON 절에 조인 조건은 명시했지만 OUTER로 조인되는 테이블인 dept_manager의 dept_no='d001' 조건을 WHERE 절에 명시한 것은 잘못된 방법이다. WHERE 절의 조건 때문에 옵티마이저가 LEFT JOIN을 INNER JOIN으로 변환해 버린다.
SELECT * FROM employees e
INNER JOIN dept_manager mgr ON mgr.emp_no=e.emp_no
WHERE mgr.dept_no='d001';
위의 쿼리를 정상적인 OUTER 조인이 되게 만들려면 다음과 같이 "mgr.dept_no='d001'" 조건을 LEFT JOIN의 ON 절로 옮겨야 한다.
SELECT * FROM employees e
LEFT JOIN dept_manager mgr ON mgr.emp_no=e.emp_no AND mgr.dept_no='d001';
OUTER JOIN과 COUNT(*)
페이징 처리를 위해 조건에 일치하는 레코드의 건수를 가져오는 쿼리에서 OUTER JOIN과 COUNT(*)를 자주 함께 사용되곤 한다. 주로 테이블의 레코드를 가져오는 쿼리에서 SELECT 절의 내용만 COUNT(*)로 바꿔서 일치하는 레코드 건수를 조회하는 쿼리를 만들기 때문에 불필요하게 OUTER JOIN으로 연결되는 테이블이 자주 있다. 만약 건수를 조회하는 쿼리에서 OUTER JOIN으로 조인된 테이블이 다음의 2가지 조건을 만족한다면 해당 테이블은 불필요하게 조인에 포함된 것이다. 조인에서 불필요한 테이블을 제거하면 같은 결과를 더 빠르게 가져올 수 있다.
- 드라이빙 테이블과 드리븐(OUTER 조인되는) 테이블의 관계가 1:1 또는 M:1인 경우
- 드리븐(OUTER 조인되는) 테이블에 조인 조건 이외의 별도 조건이 없는 경우
두가지 조건 중 첫번째는 OUTER JOIN으로 연결되는 테이블에 의해 레코드 건수가 더 늘어나지 않아야 한다는 것을 의미한다. 두 번째 조건은 OUTER JOIN으로 연결된느 테이블에 의해 레코드 건수가 더 줄어들지 않아야 한다는 것을 의미한다.
SELECT COUNT(*)
FROM dept_emp de LEFT JOIN employees e ON e.emp_no=de.emp_no
WHERE de.dept_no='d001';
위 쿼리의 조인에서 드라이빙 테이블(dept_emp)과 드리븐 테이블(employees)은 M:1 관계로 데이터가 저장되며, 드리븐 테이블에 대한 조건은 ON 절의 조인 조건밖에 없다. 결국 이 쿼리는 아래의 쿼리와 같이 조인되지 않고 dept_emp 테이블의 레코드 건수를 세는 것과 같은 결과를 가져온다. 하지만 성능측면에서는 조인을 하지 않는 쿼리가 훨씬 빠르다.
SELECT COUNT(*)
FROM dept_emp de
WHERE de.dept_no='d001';
OUTER JOIN을 이용한 ANTI JOIN
두 개의 테이블에서 한쪽 테이블에서는 있지만 다른 테이블에 없는 레코드를 검색할 때 ANTI JOIN을 사용한다. 다음 예제와 같이 tab_test2 테이블에는 있지만 tab_test1 에는 존재하지 않는 레코드를 조회하는 쿼리를 생각해 보자.
CREATE TABLE tab_test1(id INT, PRIMARY KEY(id));
CREATE TABLE tab_test2(id INT);
INSERT INTO tab_test1 VALUES (1), (2), (3), (4);
INSERT INTO tab_test2 VALUES (1), (2);
일반적으로 사용한는 방법이 NOT IN을 사용하거나 NOT EXIST를 사용한느 것이다.
SELECT t1.id FROM tab_test1 t1
WHERE t1.id NOT IN (SELECT t2.id FROM tab_test2 t2);
SELECT t1.id FROM tab_test1 t1
WHERE NOT EXISTS
(SELECT 1 FROM tab_test2 t2 WHERE t2.id=t1.id);
IN(subquery)나 NOT IN(subquery) 도 상당히 비효율적을 작동한다. 조회하는 레코드 건수가 적다면 NOT EXISTS로 처리해도 별 문제가 없다. 하지만 처리해야 할 레코드 건수가 많다면 OUTER JOIN을 이용한 ANTI JOIN을 사용하는 방법이 좋은 해결책일 것이다.
SELECT t1.id
FROM tab_test1 t1
LEFT JOIN tab_test2 t2 ON t1.id=t2.id
WHERE t2.id IS NULL;
위 쿼리는 OUTER로 조인되었지만 WHERE 절에 t2.id IS NULL 조건이 명시되었다. WHERE 조건으로 필터링하면 tab_test2 의 id 칼럼이 NULL인 것만 가져오고 나머지는 버린다. 즉, tab_test1 테이블에만 존재하는 id 값만 가져오는 것이다. 이러한 형태의 조인을 ANTI JOIN이라고 표현한다.
하지만 모든 NOT IN(subquery) 형태의 쿼리를 OUTER JOIN을 이용한 ANTI JOIn으로 변환할 수 있는 것은 아니다. OUTER 로 조인되는 테이블 때문에 로코드의 건수가 더 늘어나지 않을 때만 이렇게 변환할 수 있따. 또한 예제에서와 같이 드라이빙 테이블과 드리븐 테이블의 관계가 1:1 이거나 M:1일 때만 사용할 수 있다. 만약 1:M 이라면 NOT EXIST를 OUTER JOIN으로 변경하면서 레코드 건수가 더 늘어나기 때문이다.
