[MySQL] 테이블 조인

MySQL은 다른 DBMS보다 조인을 처리하는 방식이 단순합니다. 현재 릴리즈된 MySQL의 모든 버전에서 조인 방식은 네스티드-루프로 알려진 중첩된 루프와 같은 형태만 지원합니다. 그리고 조인되는 각 테이블 간의 레코드를 어떻게 연결할지에 다라 여러 가지 종류의 조인으로 나뉩니다.

 

 

테이블 조인의 종류

---

조인의 종류는 크게 INNER JOIN과 OUTER JOIN으로 구분할 수 있고, OUTER JOIN은 다시 LEFT OUTER JOIN과 RIGHT OUTER JOIN 그리고 FULL OUTER JOIN으로 구분할 수 있습니다. 그리고 조인의 조건을 어떻게 명시하느냐에 따라 NATURAL JOIN과 CROSS JOIN(ULL JOIN, CARTESIAN JOIN)으로도 구분할 수 있습니다.

 

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

 

 

 

JOIN (INNER JOIN)

일반적으로 "조인"이라 함은 INNER JOIN을 지칭하는데, 별도로 아우터 조인과 구분할 때 "이너 조인(INNER JOIN)"이라고도 합니다. MySQL에서 조인은 네스티드-루프 방식만 지원합니다. 네스티드-루프란 일반적으로 프로그램을 작성할 때 두 개의 FOR나 WHILE과 같은 반복 루프 문장을 실행하는 형태로 조인이 처리되는 것을 의미합니다.

 

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();
        }
    }
}

위의 의사 코드에서 알 수 있듯이 조인은 2개의 반복 루프로 두 개의 테이블을 조건에 맞게 연결해주는 작업입니다.(이 의사 코드의 FOR 반복문이 풀 테이블 스캔을 의미하는 것은 아닙니다). 두 개의 FOR 문장에서 바깥쪽을 아우터(OUTER) 테이블이라고 하며, 안쪽을 이너(INNER) 테이블이라고 표현합니다. 또한 아우터 테이블은 이너 테이블보다 먼저 읽어야 하며, 조인에서 주도적인 역할을 한다고 해서 드라이빙(Driving) 테이블이라고도 합니다. 이너 테이블은 조인에서 끌려가는 역할을 한다고 해서 드리븐(Driven) 테이블이라고도 합니다.

 

중첩된 반복 루프에서 최종적으로 선택될 레코드가 안쪽 반복 루프 (INNER 테이블)에 의해 결정되는 경우를 INNER JOIN이라고 합니다. 즉, 두 개의 반복 루프를 실행하면서 TABLE2(INNER 테이블)에 "IF(record1.join_column == record2.join_column)" 조건을 만족하는 레코드만 조인의 결과로 가져옵니다.

 

 

OUTER 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_found(record1.*, NULL);
        }
    }
}

위 코드에서 TABLE2에 일치하는 레코드가 있으면 INNER 조인과 같은 결과를 만들어내지만, TABLE2(INNER 테이블)에 조건을 만족하는 레코드가 없는 경우에는 TABLE2의 칼럼을 모두 NULL로 채워서 가져옵니다. 즉, INNER JOIN에서는 일치하는 레코드를 찾지 못했을 때는 TABLE1의 결과를 모두 버리지만 OUTER JOIN에서는 TABLE1의 결과를 버리지 않고 그대로 결과에 포함합니다.

 

INNER 테이블이 조인의 결과에 전혀 영향을 미치지 않고, OUTER 테이블의 내용에 따라 조인의 결과가 결정되는 것이 OUTER JOIN의 특징입니다. 물론 OUTER 테이블과 INNER 테이블의 관계(대표적으로 1:M 관계일 때)에 의해 최종 결과 레코드 건수가 늘어날 수는 있지만, OUTER 테이블의 레코드가 INNER 테이블에 일치하는 레코드가 없다고 해서 버려지지는 않습니다. 

 

그리고 OUTER JOIN은 조인의 결과를 결정하는 아우터 테이블이 조인의 왼쪽에 있는지 오른쪽에 있느지에 따라 LEFT OUTER JOIN과 RIGHT OUTER JOIN, 그리고 FULL OUTER JOIN으로 다시 나뉩니다.

 

SELECT *
FROM employees e
LEFT OUTER JOIN salaries s ON s.emp_no = e.emp_no;

SELECT *
FROM salaries s
RIGHT OUTER JOIN employees e ON e.emp_no = s.emp_no;

LEFT OUTER JOIN과 RIGHT OUTER JOIN은 결국 처리 내용이 같으므로 혼동을 막기 위해 LEFT OUTER JOIN으로 통일해서 사용하는 것이 일반적입니다.

 

JOIN 키워드를 기준으로 왼쪽의 테이블도 OUTER JOIN을 하고 싶고, 오른쪽의 테이블도 OUTER JOIN을 하고 싶은 경우 사용하는 쿼리가 FULL OUTER JOIN인데, MySQL에서는 FULL OUTER JOIN을 지원하지 않습니다. 하지만 INNER JOIN과 OUTER JOIN을 조금만 섞어서 활용하면 FULL OUTER JOIN과 같은 기능을 수행하도록 쿼리를 작성할 수 있습니다.

 

MySQL의 실행 계획은 INNER JOIN을 사용했는지 OUTER JOIN을 사용했는지를 알려주지 않으므로 OUTER JOIN을 의도한 쿼리가 INNER JOIN으로 실행되는지 않는지 주의해야 합니다. 이 부분도 실수하기 쉬운 부분인데, OUTER JOIN에서 레코드가 없을 수도 있는 쪽의 테이블에 대한 조건은 반드시 LEFT JOIN의 ON 절에 모두 명시하는 것이 좋습니다. 그렇지 않으면 옵티마이저는 OUTER JOIN을 내부적으로 INNER JOIN으로 변형시켜서 처리해 버릴 수도 있습니다. LEFT OUTER JOIN의 ON 절에 명시되는 조건은 조인되는 레코드가 있을 때만 적용됩니다. 하지만 WHERE 절에 명시되는 조건은 OUTER JOIN이나 INNER JOIN에 관계없이 조인된 결과에 대해 모두 적용됩니다. 그래서 OUTER JOIN으로 연결되는 테이블이 있는 쿼리에서는 가능하다면 모든 조건을 ON 절에 명시하는 습관을 들이는 것이 좋습니다.

 

SELECT *
FROM employees e
LEFT OUTER JOIN salaries s ON s.emp_no=e.emp_no
WHERE s.salary > 5000;

 

위 쿼리의 LEFT OUTER JOIN 절과 WHERE 절은 서로 충돌되는 방식으로 사용된 것입니다. OUTER JOIN으로 연결되는 테이블의 칼럼에 대한 조건이 ON 절에 명시되지 않고 WHERE 절에 명시됐기 때문입니다. 그래서 MySQL 서버는 이 쿼리를 최적화 단계에서 다음과 같은 쿼리로 변경한 후 실행 합니다. MySQL 옵티마이저가 쿼리를 변경해버리면 원래 쿼리를 작성했던 사용자의 의도와는 다른 결과를 반환받습니다.

 

SELECT *
FROM employees e
INNER JOIN salaries s ON s.emp_no=e.emp_no
WHERE s.salary > 5000;

 

이런 형태의 쿼리는 다음 2가지 중의 한 방식으로 수정해야 쿼리 자체의 의도나 결과를 명확히 할 수 있습니다.

 

-- // 순수하게 OUTER JOIN으로 표현한 쿼리
SELECT *
FROM employees e
LEFT OUTER JOIN salaries s ON s.emp_no=e.emp_no AND s.salary > 5000;

-- // 순수하게 INNER JOIN으로 표현한 쿼리
SELECT *
FROM employees e
INNSER JOIN salaries s ON s.emp_no=e.emp_no
WHERE s.salary > 5000;

LEFT OUTER JOIN이 아닌 쿼리에서는 검색 조건이나 조인 조건을 WHERE 절이나 ON 절 중에서 어느 곳에 명시해도 성능상의 문제나 결과의 차이가 나지 않습니다.

 

오라클과 같은 DBMS에서는 OUTER JOIN 테이블에 대한 조건이라는 표기로 "(+)" 기호를 칼럼 뒤에 사용할 수도 있습니다. 하지만 MySQL은 이러한 형태의 표기법을 허용하지 않고 LEFT JOIN 또는 LEFT OUTER JOIN 절을 이용하는 SQL 표준 문법만을 지원합니다.

 

조인 관련 주의 사항

MySQL의 조인 처리에서 특별히 주의해야 할 부분은 "실행 결과의 정렬 순서"와 "INNER JOIN과 OUTER JOIN의 선택"으로 2가지 정도일 것입니다.

조인 실행 결과의 정렬 순서

일반적으로 조인으로 쿼리가 실행되는 경우, 드라이빙 테이블로부터 레코드를 읽는 순서가 전체 쿼리의 결과 순서에 그대로 적용되는 것이 일반적입니다. 이는 네스티드-루프 조인 방식의 특징이기도 합니다. 다음 쿼리를 한번 살펴보겠습니다.

SELECT de.dept_no, e.emp_no, e.first_name
FROM dept_emp de, employees e
WHERE e.emp_no = de.emp_no
AND de.dept_no = 'd005';

이 쿼리의 실행 계획을 보면, dept_emp 테이블의 프라이머리 키로 먼저 읽었다는 것을 알 수 있습니다. 그리고 dept_emp 테이블로부터 읽은 결과를 가지고 employees 테이블의 프라이머리 키를 검색하는 과정으로 처리되었습니다.

이 실행 계획 순서대로 살펴보면 dept_emp 테이블의 프라이머리 키는 (dept_no+emp_no)로 생성돼 있기 때문에 dept_emp 테이블을 검색한 결과는 dept_no 칼럼 순서대로 정렬되고 다시 emp_no로 정렬되어 반환된다는 것을 예상할 수 있습니다. 그런데 이 쿼리의 WHERE 조건에 dept_no='d005'로 고정돼 있으므로 emp_no로 정렬된 것과 같습니다. 결국 이 쿼리는 "ORDER BY de.emp_no ASC"를 명시하지 않았지만 emp_no로 정렬된 효과를 얻을 수 있습니다. 주로 조인이 인덱스를 이용해 처리되는 경우에는 이러한 예측을 할 수 있습니다.

하지만 결과가 이 순서로 반환된 것은 옵티마이저가 여러 가지 실행 계획 중에서 위의 실행 계획을 선택했기 때문입니다. 만약 옵티마이저가 다른 실행 계획을 선택했다면 이러한 결과는 보장되지 않습니다. 당연히 인덱스를 이용해 검색하고 조인하는 것이 당연할 것 같은 쿼리에서도 테이블의 레코드 건수가 매우 적거나 통계 정보가 잘못돼 있을 때는 다른 실행 계획을 선택할 수도 있습니다. 이처럼 옵티마이저가 선택하는 실행 계획에 의존한 정렬은 피하는 것이 좋습니다. 쿼리의 실행 계획은 언제 변경될지 알 수 없기 때문입니다. 테이블에 있는 대부분의 레코드가 어느 날 삭제됐다거나 인덱스가 삭제되거나 추가되어 실행계획이 바뀌는 것은 충분히 가능한 일이기 때문입니다.

위에서 살펴본 예제 쿼리에서 만약 사원 번호로 정렬되어 결과가 반환되기를 바란다면 반드시 "ORDER BY de.emp_no ASC" 절을 추가해서 정렬이 보장될 수 있게 하는 것이 좋습니다. ORDER BY 절이 쿼리에 명시됐다고 해서 옵티마이저는 항상 정렬 작업을 수행하는 것은 아닙니다. 실행 계획상에서 이 순서를 보장할 수 있다면 옵티마이저가 자동으로 별도의 정렬 작업을 생략하고 결과를 반환합니다. 만약 정렬이 보장되지 않는다면 강제로 정렬 작업을 통해 정렬을 보장해줍니다. ORDER BY 절이 사용된다고 해서 MySQL 서버가 항상 정렬을 수행하는 것은 아닙니다.

SQL 쿼리에서 결과의 정렬을 보장하는 방법은 ORDER BY 절을 사용하는 방법 뿐입니다.

오라클과 같이 여러 가지 조인 방법을 제공하는 DBMS에서는 조인 방법에 따라 반환되는 결과의 정렬이 달라질 수도 있습니다. 그래서인지 오라클 DBMS는 업그레이드할 때마다 "ORDER BY"가 항상 문제가 됩니다.

아주 가끔은 MySQL이 네스티드-루프 조인 방법만 가지고 있다는 것이 다행스럽게 느껴질 수도 있을 것입니다. 하지만 네스티드-루프 조인에서도 조인 버퍼를 사용할 때는 드라이빙 테이블의 순서와 관계없이 결과의 정렬 순서가 흐트러질 수도 있습니다. 결론적으로 어떤 DBMS를 사용하든, 어떤 조인 방식이 사용되든, 정렬된 결과가 필요할 때는 ORDER BY 절을 명시하는 것이 정답일 것입니다.

INNER JOIN과 OUTER JOIN의 선택

INNER JOIN은 조인의 양쪽 테이블 모두 레코드가 존재하는 경우에만 레코드가 반환됩니다. 하지만 OUTER JOIN은 아우터 테이블에 존재하면 레코드가 반환됩니다. 쿼리나 테이블의 구조를 살펴보면 OUTER JOIN을 사용하지 않아도 될 것을 OUTER JOIN으로 사용할 때가 상당히 많습니다. DBMS 사용자 가운데 INNER JOIN을 사용했을 때, 레코드가 결과에 나오지 않을까 걱정하는 사람들이 꽤 있는 듯합니다. OUTER JOIN과 INNER JOIN은 저마다 용도가 다르므로 적절한 사용법을 익히고 요구되는 요건에 맞게 사용하는 것이 중요합니다.

때로는 그 반대로 OUTER JOIN으로 실행하면 쿼리의 처리가 느려진다고 생각하고, 억지로 INNER JOIN으로 쿼리를 작성할 때도 있습니다. 가끔 인터넷에도 OUTER JOIN과 INNER JOIN의 성능비교를 물어보는 질문들이 자주 올라오곤 합니다. 사실 OUTER JOIN과 INNER JOIN은 실제 가져와야 하는 레코드가 같다면 쿼리의 성능은 거의 차이가 발생하지 않습니다. 다음의 두 쿼리를 한번 비교해 보겠습니다. 이 두 CACHE와 STRAIGHT_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
INNER JOIN employees e ON e.emp_no=de.emp_no;

대략적인 평균 시간은 INNER JOIN이 0.37초 정도이고, OUTER JOIN이 0.38초 정도입니다. OUTER JOIN은 조인되는 두번째 테이블(employees)에서 해당 레코드의 존재 여부를 판단하는 별도의 트리거 조건이 한번씩 실행되기 때문에 0.01초 정도 더 걸린 것으로 보입니다. 그 밖에 어떤 성능상의 이슈가 될만한 부분은 전혀 없습니다.

INNER JOIN과 OUTER JOIN은 성능을 고려해서 선택할 것이 아니라 업무 요건에 따라 선택하는 것이 바람직합니다. 레코드가 결과에 포함되지 않을까 걱정스러운 경우라면, 테이블의 구조와 데이터의 특성을 분석해 INNER JOIN을 사용해야할지 OUTER JOIN을 사용해야 할지 결정하면 됩니다. 데이터의 정확한 구조나 특성을 모르고 OUTER JOIN을 사용한다면 얼마 지나지 않아서 잘못된 결과가 화면에 표시되는 현상이 발생할 것입니다.

출처: https://12bme.tistory.com/165?category=682920 [길은 가면, 뒤에 있다.]