본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
INNNER JOIN과 OUTER JOIN의 선택
INNER JOIN은 조인의 양쪽 테이블 모두 레코드가 존재하는 경우에만 레코드가 반환된다. 하지만 OUTER JOIN은 아우터 테이블에 존재하면 레코드가 반환된다. 쿼리나 테이블의 구조를 살펴보면 OUTER JOIN을 사용하지 않아도 될 것을 OUTER JOIN으로 사용할 때가 상당히 많다. 때로는 OUTER JOIN으로 실행하면 쿼리의 처리가 느려진다고 생각하고, 억지로 INNER JOIN으로 쿼리를 작성하려는 경우도 있다.
사실 OUTER JOIN과 INNER JOIN은 실제 가져와야 하는 레코드가 같다면 쿼리의 성능은 거의 차이가 없다. 다음 두쿼리를 비교하면 실제 비굘르 수행하는 건수나 최종적으로 가져오는 결과 건수가 동일하다.
SELECT SQL_NO_CACHE STRAIGHT_JOIN COUNT(*)
FROM dept_emp de
INNER JOIN employees e ON e.emp_no=de.emp_no;
SELECT SQL_NO_CACHE STRAIGHT_JOIN COUNT(*)
FROM dept_emp de
LEFT JOIN employees e ON e.emp_no=de.emp_no;
위 두 쿼리는 실제 실행 시간의 차이가 0.01초 정도밖에 나지 않는다.
INNER JOIN과 OUTER JOIN은 성능을 고려해 선택할 것이아니라 업무 요건에 따라 선택하는 것이 좋다. 테이블의 구조와 데이터의 특성을 분석해 어떤 것을 사용할지 결정해야 한다. 데이터의 정확한 구조나 특성을 모르고 OUTER JOIN을 사용한다면 얼마 지나지 않아 잘못된 결과가 화면에 나타날 것이다.
FULL OUTER JOIN 구현
MySQL에는 FULL OUTER JOIN을 제공하지 않는다. 하지만 두 개의 쿼리 결과를 UNION으로 결합하면 FULL OUTER JOIN의 효결과를 얻을 수 있다. 양쪽 테이블 모두를 OUTER로 연결해서 결과를 가져와야 하는 조인을 FULL OUTER JOIN 이라 하며 MySQL 에서는 UNION을 사용해 동일한 효과를 낼 수 있다.
SELECT e.yearmonth, e.event_name n.news_title FROM tab_event e
LEFT JOIN tab_news n ON n.yearmonth=e.yearmonth
UNION
SELECT n.yearmonth, e.event_name, n.news_title FROM tab_news n
LEFT JOIN tab_event e ON e.yearmonth=n.yearmonth
일반적으로 두 집합의 결과에서 중복 제거가 필요하기 때문에 UNION ALL을 사용하면 더 빠르게 처리도리 것이다.
SELECT e.yearmonth, e.event_name n.news_title FROM tab_event e
LEFT JOIN tab_news n ON n.yearmonth=e.yearmonth
UNION ALL
SELECT n.yearmonth, e.event_name, n.news_title FROM tab_news n
LEFT JOIN tab_event e ON e.yearmonth=n.yearmonth
UNION이나 UNION ALL은 내부적인 임시테이블을 사용해 결과가 버퍼링돼야 하고, 그로인해 쿼리가 늦게 처리된다. 버퍼링의 성능이 걱정되는 경우 뮤텍스(Mutex) 테이블을 사용하면 된다. 뮤텍스 테이블이란 copy_t라고도 많이 알려져 있는데, 단순히 n개 만큼 복제하는 역할으 하는 테이블이다.
일반적으로 뮤텍스 테이블은 복제하고자하는 수만큼 레코드를 갖게 되는데, 여기서는 숫자 칼럼 하나가 포함돼 있고 레코드는 단 2건인 뮤텍스 테이블을 이용하는 예제를 살펴보자.
- Mutex 테이블 생성
CREATE TABLE mutex (no INT NOT NULL, PRIMARY KEY(no));
INSERT INTO mutex VALUES (0), (1);
- Mutex 활용 실행쿼리
SELECT IFNULL(e.yearmonth, n.yearmonth) AS yearmonth, e.event_name, n.news_title
FROM mutex m
LEFT JOIN tab_event e ON m.NO=0
LEFT JOIN tab.news n ON m.NO=1 OR n.yearmonth=e.yearmonth
LEFT JOIN tab_event e2 ON m.NO=1 AND e2.yearmonth=n.yearmonth
WHERE n.yearmonth IS NULL OR e2.yearmonth IS NULL;
위의 쿼리에서 첫 번째와 두 번째 OUTER JOIN은 우선 필요한 기초 레코드를 만드는데 사용됐다. 세 번째 OUTER JOIN과 WHERE 조건절은 첫 번째와 두 번째 OUTER JOIN 결과로 만들어진 레코드 가운데 불필요한 데이터를 거러내는 데 사용됐다. 즉, tab_news 테이블과 tab_event 테이블에 존재하는 중복 제거를 위해 세번째 OUTER JOIN이 필요하다.
copy_t나 뮤텍스 테이블이라고 하는 레코드 복제용 테이블을 실제로 다른 용도로 많이 활요될 수 있으니 자료를 찾아보고 활용하면 도움이 될 것이다.
JOIN과 FOREIGN KEY
데이터베이스에 FOREIGN KEY가 생성돼 있어야만 조인을 사용할 수 있을까? 정답은 FOREIGN KEY와 조인은 아무런 연관이 없다. FOREIGN KEY를 생성하는 주 목적은 데이터의 무결성을 보장하기 위해서다. FOREIGN KEY와 연관된 무결성을 참조 무결성이라고 표현한다.
예를 들어, 부서 테이블과 사원 테이블이 있고, 사원 테이블에 이 사원이 소속된 부서 정보를 저장하는 칼럼이 있다. 이때 사원 테이블의 부서 코드는 반드시 부서 테이블에 존재하는 부서 정보만 사용해야 하는데, 이것이 바로 참조 무결성이다. 그런데 애플리케이션의 버그 등의 이유로 부서 테이블에는 존재하지 않는 부서 코드가 사원 테이블에 있을 수 있다. 이렇게 참조 무결성이 깨지는 문제를 DBMS 차원에서 막기 위해 FOREIGN KEY를 생성한다.
하지만 SQL로 테이블간의 조인을 수행하는 것은 전혀 무관한 칼럼을 조인 조건으로 사용해도 문법적으로는 문제가 되지 않는다. 데이터 모델링을 할 때는 각 테이블 간의 관계는 필수적으로 그려 넣어야한다. 하지만 그 데이터 모델을 데이터베이스에 생성할 때는 그 테이블 간의 관계는 FOREIGN KEY로 생성하지 않을 때가 더 많다. 하지만 테이블 간의 조인을 사용하기 위해 FOREIGN KEY가 필요한 것은 아니다.
