SQL JOIN

조인(JOIN)은 관계형 데이터베이스(Relational Database, RDB)의 꽃이라고 불립니다. 조인을 명확하게 이해하기 위해서는 관계형 데이터베이스에 대한 이해가 필요합니다. 관계형 데이터베이스에 대한 설명은 이 글의 범위를 벗어나므로, 관계형 데이터베이스에 대한 자세한 내용은 다음 링크를 참조하시기 바랍니다. 참고로 관계형 데이터베이스의 특징을 한 문장으로 요약하면, 정규화(Normalization) 이론을 활용하여 데이터 이상 현상을 제거하고, 데이터 중복을 최소화하여 데이터를 관리하는 것입니다.

조인이 왜 필요할까요?

관계형 데이터베이스의 구조적 특징으로 말미암아 정규화를 수행하면 의미 있는 데이터의 집합으로 테이블이 구성되고, 각 테이블끼리는 관계(Relationship)를 갖게 됩니다. 이와 같은 특징으로 관계형 데이터베이스는 저장 공간의 효율성과 확장성이 향상되게 됩니다. 다른 한편으로는 서로 관계있는 데이터가 여러 테이블로 나뉘어 저장되므로, 각 테이블에 저장된 데이터를 효과적으로 검색하기 위해 조인이 필요합니다. 즉, 조인은 각 테이블 간 의미 있는 데이터(행)를 연결하는 데 활용되는 메커니즘입니다.

예제 데이터

이 글에서 사용하는 예제는 Oracle에서 제공하는 샘플 스키마(EMP, DEPT)를 활용하여 설명하겠습니다. 예제 데이터는 다음 링크에서 확인하실 수 있습니다. 이뿐만 아니라, 각 DBMS별로 조인을 사용하는 문법이 다르므로, ANSI SQL에서 추천하는 문법으로 설명하겠습니다.

내부 조인(INNER JOIN)

내부 조인(INNER JOIN)은 가장 일반적인 조인 형태로 가장 많이 활용하는 조인 기법입니다. 내부 조인은 둘 이상의 테이블에 존재하는 공통 컬럼(속성)의 값이 같은 것을 결과로 추출합니다. 

내부 조인에는 대표적으로 동등 조인(EQUI JOIN), 자연 조인(NATURAL JOIN), 그리고 교차 조인(CROSS JOIN) 등으로 구분할 수 있습니다. 

동등 조인(EQUI JOIN)

동등 조인 혹은 이퀴(Equi) 조인이라고 하며, 조인의 가장 일반적인 활용 형식입니다. 동등 조인은 이름에서 유추할 수 있듯이, 둘 이상의 테이블에 존재하는 공통 컬럼(속성)의 동등 비교만을 사용하며, 부등호 조인은 동등 조인에 포함하지 않습니다.

조인 문법은 명시적 표현법(Explicit Notation)과 묵시적 표현법(Implicit Notation)이 있습니다. 개인적으로는 명시적 표현법을 활용하는 것을 추천합니다만, 현실적으로는 묵시적 표현법이 더 많이 활용되는 것 같습니다. 일반적으로 두 표현법의 성능에는 큰 차이가 없는 것으로 알려져 있습니다.

Explicit Notation

SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno

Implicit Notation

SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno

동등 조인의 수행 결과

자연 조인(NATURAL JOIN)

자연 조인은 동등 조인과 거의 유사합니다. 단, 조인 대상 테이블의 모든 컬럼을 비교하여, 같은 컬럼명을 가진 컬럼으로 조인을 수행합니다. 이때, 같은 이름을 가진 컬럼은 한 번만 추출합니다. 동등 조인에서 수행했던 결과를 살펴보면 DEPTNO가 두 번 추출된 것을 확인할 수 있지만, 자연 조인의 결과를 살펴보면 DEPTNO를 기준으로 조인을 수행하고, 한 번만 추출된 것을 확인할 수 있습니다.

Explicit Notation

SELECT * FROM emp NATURAL JOIN dept

자연 조인의 수행 결과

교차 조인(CROSS JOIN)

교차 조인은 카티션 프로덕트(Cartesian Product)로 알려져 있으며, 조인에 참여한 테이블들의 모든 데이터가 추출됩니다. 교차 조인이 발생하는 상황은 다음과 같습니다.

1. JOIN 조건을 잘못 기술했을 때
2. JOIN 조건을 정의하지 않았을 때
3. 조인 조건이 조인 조건에 참여하는 테이블의 모든 행이 조인되는 경우

SQL 작성자가 교차 조인을 유도하지 않은 상황에서 교차 조인이 발생하는 경우 큰 문제가 발생합니다. 그러므로 조인을 수행한 후, 반드시 결과를 확인하는 습관을 들여야 합니다. 교차 조인을 예방하기 위한 한 가지 팁은 조인에 참여하는 테이블의 수가 N개라면, 최소한 N-1개의 조인 조건이 질의 안에 포함되어야 합니다.

Explicit Notation

SELECT * FROM emp CROSS JOIN dept

Implicit Notation

SELECT * FROM emp, dept

교차 조인의 수행 결과

셀프 조인(SELF JOIN)

셀프 조인은 자가 조인이라고도 하며, 같은 두 테이블을 활용하여 데이터를 추출하는 조인 기법입니다. 예를 들어, EMP 테이블의 내용을 살펴보면, 각 사원별로 관리자(MGR)가 있습니다. 이때, 사원 정보와 관리자 정보를 함께 보고 싶을 때, 활용하는 조인 기법입니다.

Explicit Notation

SELECT e.empno, e.ename, e.job, e.hiredate, e.sal, m.empno, m.ename, m.job FROM emp E INNER JOIN emp M ON E.mgr = M.empno

Implicit Notation

SELECT e.empno, e.ename, e.job, e.hiredate, e.sal, m.empno, m.ename, m.job FROM emp E, emp M WHERE E.mgr = M.empno

셀프 조인의 수행 결과

외부 조인(OUTER JOIN)

지금까지 살펴본 내부 조인은 조인 대상 테이블에서 공통인 컬럼 값을 기반으로 결과 집합을 생성합니다. 그러나 때때로 조인 대상 테이블에서 특정 테이블의 데이터가 모두 필요한 상황이 있습니다. 이런 요구 사항이 발생했을 때, 외부 조인을 활용하여 효과적으로 결과 집합을 생성할 수 있습니다. 외부 조인은 모두 3가지가 있습니다. 외부 조인의 결과는 생략하겠습니다. 직접 수행하시고, 데이터를 보면서 이해하시는 것을 추천합니다.

외부 조인은 RDBMS 제조사 별로 표기법이 다릅니다. 이 글에서는 ANSI SQL 문법을 기준으로 표기하겠습니다.

왼쪽 외부 조인(LEFT OUTER JOIN)

왼쪽 외부 조인은 우측 테이블(예제: DEPT)에 조인할 컬럼의 값이 없는 경우 사용합니다. 즉, 좌측 테이블의 모든 데이터를 포함하는 결과 집합을 생성합니다. 왼쪽 외부 조인을 그림으로 표현하면 다음과 같습니다.

왼쪽 외부 조인의 예

Left outer join Notation

SELECT * FROM emp LEFT OUTER JOIN dept ON emp.deptno = dept.deptno;

오른쪽 외부 조인(RIGHT OUTER JOIN)

오른쪽 외부 조인은 좌측 테이블(예: EMP)에 조인할 컬럼의 값이 없는 경우 사용합니다. 즉, 우측 테이블의 모든 데이터를 포함하는 결과 집합을 생성합니다. 오른쪽 외부 조인을 그림으로 표현하면 다음과 같습니다.

오른쪽 외부 조인의 예

Right outer join Notation

SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.deptno = dept.deptno;

완전 외부 조인(FULL OUTER JOIN)

완전 외부 조인은은 양쪽 테이블 모두 OUTER JOIN이 필요할 때 사용합니다.

완전 외부 조인의 예

Full outer join Notation

SELECT * FROM emp FULL OUTER JOIN dept ON emp.deptno = dept.deptno;

안티 조인(ANTI JOIN)

안티 조인은 부정형 조인이라고도 하며, 테이블에서 조인의 대상이 되는 테이블과 일치하지 않는 데이터를 추출하는 연산하는 조인 기법입니다.

Full outer join Notation

SELECT e.* FROM emp e WHERE e.empno >= 7500 AND NOT EXISTS (SELECT 'x' FROM dept d WHERE d.deptno = e.deptno AND d.deptno <= 20);

안티 조인의 수행 결과

세미 조인(SEMI JOIN)

세미 조인이라는 이름에서 알 수 있듯이 조인과 유사하게 데이터를 연결하는 조인 기법입니다. 세미 조인은 분산 질의를 효율적으로 수행하기 위해 도입¹되었습니다. 최근에는 서브 쿼리²를 사용할 때, 메인 쿼리와의 연결을 하기 위해 적용하는 유사 조인을 의미하기도 합니다.

Full outer join Notation

SELECT e.* FROM emp e WHERE e.empno >= 7500 AND EXISTS (SELECT /*+ UNNEST NL_SJ */ 'x' FROM dept d WHERE d.deptno = e.deptno AND d.deptno <= 20);

주) 필터 방식으로 처리가 되어 세미 조인으로 유도하기 위해 힌트(UNNEST NL_SJ)를 적용했습니다.

세미 조인의 수행 결과

조인을 사용할 때 주의사항

본 절에서는 조인을 사용할 때, 주의해야 할 사항을 몇 가지 소개하겠습니다.

첫째, SQL 문장의 의미를 제대로 파악해야 합니다. 조금 어려운 내용일지도 모르겠습니다만, SQL을 어떻게 작성하느냐에 따라 성능이 크게 좌우됩니다. 그러므로 어떤 질의를 수행할 것인지를 명확하게 정의한 후, 비효율을 제거하여 최적의 SQL을 작성해야 합니다.

둘째, 조인 조건을 명확하게 제공해야 합니다. 조인 조건을 명확하게 제공하지 않을 경우, 의도치 않게 CROSS JOIN(Cartesian Product)가 수행될 수 있기 때문입니다. 

셋째, 조인 적용 후 테스트를 수행해야 합니다. 적은 수의 테이블을 조인할 때는 큰 문제가 없지만, 여러 개의 테이블을 조인할 때는 예상하지 않은 결과를 얻을 수 있습니다. 이때, 각각의 조인을 따로 테스트하면, 문제가 발생했을 경우 빠르면서도 쉽게 해결할 수 있습니다.

조인을 사용할 때 고려사항

이 단락을 작성하는 것이 옳을지 아닐지에 대해 고민을 많이 했습니다. 자세하게 들어가면, 이 글에서 이야기하고자 하는 내용의 범위를 벗어나므로 간략히 소개하는 것으로 정리를 하겠습니다.

조인은 비교적 많은(?) 자원을 소비하는 연산입니다. 그러므로 아무런 계획과 생각 없이 오로지 결과만 바라보고, 조인을 맺는 질의를 작성하면 DBMS는 고통받고 사용자는 답답한 상황에 놓일 것입니다. 이 단락에서 당부하고 싶은 내용은 크게 두 가지입니다.

첫째, 조인 대상의 양을 최소화하는 것입니다. 다른 표현으로는 조인할 대상의 집합을 최소화하라고 볼 수 있습니다. 즉, 집합을 최소화할 방법이 있으면, 조건을 먼저 적용하여 관계를 맺을 집합을 최소화한 후, 조인을 맺는 것이 효율적이란 이야기입니다.

둘째, 효과적인 인덱스의 활용입니다. 인덱스를 활용하면, 조인 연산의 비용을 극적으로 낮출 수 있습니다.

마치면서

지금까지 조인에 대하여 간략하게 알아봤습니다. 이 글에서 소개하는 내용은 비교적 쉽게 이해할 수 있을 것으로 예상합니다. 그러나 실제 환경에서 조인을 사용하는 것은 어떨까요? 실제 환경에서의 조인은 많은 생각을 하게 만듭니다. 또한, 앞에서 간략히 언급했듯이, 조인을 효과적으로 사용하기 위해 고려해야 할 사항도 많습니다.

조인을 제대로 활용하는 방법은 무엇일까요? 개인적인 생각으로는 하나밖에 없습니다. 많이 경험하고 실패하고 깨우침을 얻는 것입니다.

References

Thomas Kyte, Expert Oracle Database Architecture

박성호 · 오수영, SQL 튜닝의 시작

http://wiki.gurubee.net/pages/viewpage.action?pageId=14221329

1. 분산질의 : 두 테이블 간에 조인을 할 때 한 테이블을 다른 사이트에 전송하기 전에 먼저 조인에 필요한 속성만을 추출(프로젝션)하여 전송한 후 조인에 성공한 로우의 집합만을 다시 전송함으로써 네트워크를 통해 전송되는 데이터의 양을 줄이고자 하는 개념으로 도입 [본문으로]
2. 하나의 SQL문안에 포함되어 있는 또 다른 SQL문 [본문으로]

출처: http://moon.smartdisk.org:8080/client/Fboard.editor.blank.jsp?enc=b64utf8&mode=editor&rootname=note&filename=aHR0cCUyMWNvbCUzQmJsb2cubmdlbG1hdW0ub3JnLmVudHJ5LmxhYi1ub3RlLXNxbC1qb2luLW1ldGhvZA==&treepath=aHR0cCUyMWNvbCUzQmJsb2cubmdlbG1hdW0ub3JnLmVudHJ5LmxhYi1ub3RlLXNxbC1qb2luLW1ldGhvZA==&msgid=&msgfn= [ngelmaum notes]