외부조인으로 누락 행까지 포함하는 SQL 조회

일치하지 않는 행까지 남기는 조인

내부조인은 조인 조건이 맞는 행만 반환한다. 반면 외부조인은 상대 릴레이션에 대응되는 튜플이 없거나 조인 애트리뷰트에 NULL 값이 있는 경우까지 다루기 위해 확장된 연산이다.

조인 조건을 만족하지 못한 행도 결과에 포함하고, 반대편에 대응 데이터가 없으면 해당 열을 NULL로 채운다. 보고서나 분석 결과에서 누락 행을 함께 확인해야 할 때 두 테이블의 관계를 정보 손실 없이 표현할 수 있다.

보존할 테이블을 기준으로 고르는 방식

LEFT OUTER JOIN은 왼쪽 테이블의 모든 행을 유지한다. 오른쪽 테이블에 일치하는 행이 없으면 오른쪽 열은 NULL이 되므로, 기준 테이블을 중심으로 관련 데이터의 존재 여부를 확인할 때 적합하다.

RIGHT OUTER JOIN은 오른쪽 테이블의 모든 행을 남긴다. 왼쪽 테이블에 대응 행이 없으면 왼쪽 열이 NULL로 채워지며, 테이블 순서만 바꾸면 LEFT JOIN으로 표현할 수 있다.

FULL OUTER JOIN은 양쪽 테이블의 모든 행을 결과에 넣는다. 한쪽에만 존재하는 행도 남기므로 두 테이블의 전체 데이터를 유지한 채 관계를 파악할 수 있다.

Oracle 문법과 ANSI 표준 문법

Oracle의 전통 문법에서는 (+)로 선택적인 테이블을 표시한다. 아래 쿼리는 b 테이블에 대응 데이터가 없어도 a 테이블의 행을 포함한다.

SELECT a.user_id, a.user_name, b.address
FROM ikep_ev_user a, ikep_ev_add b
WHERE a.user_id = b.user_id(+);  -- LEFT OUTER JOIN

대부분의 DBMS에서 사용할 수 있는 ANSI 문법은 조인 방향과 조건을 명시적으로 드러낸다.

-- LEFT OUTER JOIN
SELECT a.user_id, a.user_name, b.address
FROM ikep_ev_user a LEFT OUTER JOIN ikep_ev_add b
ON a.user_id = b.user_id;

-- RIGHT OUTER JOIN
SELECT a.user_id, a.user_name, b.address
FROM ikep_ev_user a RIGHT OUTER JOIN ikep_ev_add b
ON a.user_id = b.user_id;

-- FULL OUTER JOIN
SELECT a.user_id, a.user_name, b.address
FROM ikep_ev_user a FULL OUTER JOIN ikep_ev_add b
ON a.user_id = b.user_id;

조인 범위를 한눈에 보는 도식

⤢✕

누락 데이터를 드러내는 조회

고객을 기준으로 주문 이력을 확인하면, 주문하지 않은 고객도 함께 확인할 수 있다.

SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM customers c LEFT OUTER JOIN orders o
ON c.customer_id = o.customer_id;

부서와 사원 정보를 결합할 때도 LEFT OUTER JOIN을 사용하면 사원이 없는 부서를 결과에서 제외하지 않는다.

SELECT d.dept_id, d.dept_name, e.emp_id, e.emp_name
FROM departments d LEFT OUTER JOIN employees e
ON d.dept_id = e.dept_id;

설문 결과에서는 모든 질문과 응답을 연결하면서 응답이 없는 질문, 선택되지 않은 답변 옵션까지 확인할 수 있다.

SELECT q.question_id, q.question_text, a.answer, COUNT(r.response_id) as response_count
FROM questions q
LEFT OUTER JOIN responses r ON q.question_id = r.question_id
LEFT OUTER JOIN answers a ON r.answer_id = a.answer_id
GROUP BY q.question_id, q.question_text, a.answer;

NULL과 조인 순서가 결과를 바꾼다

외부조인은 내부조인보다 일반적으로 더 많은 리소스를 사용한다. 특히 큰 테이블 사이의 FULL OUTER JOIN은 상당한 시스템 부하를 발생시킬 수 있으므로, 필요한 경우에만 사용하고 조건을 추가해 처리 대상 데이터를 줄일 필요가 있다.

외부조인 결과에는 NULL이 포함될 수 있다. 집계 함수나 연산에서 예상하지 못한 결과를 만들 수 있으므로 NVL, ISNULL, COALESCE 등의 함수로 적절히 대체하는 처리가 필요하다.

-- Oracle에서 NULL 처리 예시
SELECT c.customer_id, c.name,
       NVL(COUNT(o.order_id), 0) as order_count
FROM customers c
LEFT OUTER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

여러 테이블을 연결할 때는 조인 순서와 종류를 함께 검토해야 한다. 외부조인과 내부조인을 섞으면 의도하지 않은 결과가 나올 수 있으며, 복잡한 조인은 WITH 절이나 임시 테이블로 단계를 나누어 구현할 수 있다.

DBMS별 지원 범위

Oracle은 (+) 연산자를 사용하는 전통 문법과 ANSI 표준 문법을 모두 지원하며 FULL OUTER JOIN도 지원한다.

MySQL은 5.0 이전 버전에서 LEFT JOIN과 RIGHT JOIN만 지원했고, 5.0 이후 버전부터는 UNION을 사용해 FULL OUTER JOIN을 구현할 수 있다. Oracle 스타일의 (+) 연산자는 지원하지 않는다.

SQL Server는 ANSI 표준 문법과 FULL OUTER JOIN을 지원한다. *= 및 =* 연산자를 이용한 전통 문법은 SQL Server 2008 이후 지원이 중단됐다.

PostgreSQL은 ANSI 표준 문법과 FULL OUTER JOIN을 지원하며 Oracle 스타일의 (+) 연산자는 지원하지 않는다.

엔진이 외부조인 결과를 만드는 과정

데이터베이스 엔진은 먼저 양쪽 테이블에서 조인 조건을 만족하는 행을 결합한다. 이어서 지정한 외부조인 유형에 따라 조건을 만족하지 못한 행을 결과에 추가하고, 상대 테이블에 해당하는 열에는 NULL을 할당한다.

이 처리는 데이터베이스 엔진 내부에서 최적화되어 수행된다. 외부조인은 SQL 문법 선택을 넘어 데이터 모델의 관계와 누락 데이터의 의미를 결과에 반영하는 방법이다.