SQL의 꽃, JOIN 정복기 (INNER JOIN, OUTER JOIN, SELF JOIN, CROSS JOIN, NATURAL JOIN)

미키오·2024년 5월 28일

들어가며..

지난주 토요일 제 53회 5/25 SQLD 시험을 봤다. 사실 필자는 비전공자로, 프로젝트에서도 프런트엔드 개발만 했었어서 완전히 본인이 데이터베이스, SQL에서는 노베이스라는 것을 망각한채 어쩌다보니 일주일 안에 마스터 가능하다던 전공자 친구 따라서 시험을 보게 되었다. 특히 SQLD 2과목에서 JOIN에 대한 개념이 제대로 잡혀있지 않아서 복기해본 결과 JOIN에서 오답을 내서 오답 노트 겸 다시 복습을 하기 위해 예제들과 데이터를 직접 넣어보며 정리해보았다.

JOIN

조인은 두 개 이상의 여러 테이블을 연결하여 데이터를 출력할 경우 사용한다. 조인은 관계형 데이터베이스의 가장 큰 장점이면서 대표적인 핵심 기능으로 조건 형태에 따라 (EQUI JOIN, NON EQUI JOIN), 조인 결과에 따라 (INNER JOIN, OUTER JOIN) 유형에 따라 (논리적 조인, 물리적 조인) 다양하게 나누어볼 수 있다.

조건 형태에 따라

EQUI JOIN

다른 말로 등가 JOIN이라고도 하며 SQL 명령문에서 가장 많이 사용하는 조인 방법이다. JOIN 조건이 동등 조건인 경우 사용한다. 다시 말해, 조인 조건이 '='(equal) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 얻을 수 있다. FROM 절에 조인하고자 하는 테이블을 모두 명시하고, WHERE절에 두 테이블의 공통 칼럼에 대한 조인 조건을 나열한다.

NON-EQUI JOIN

'=' 조건을 사용하는 EQUI JOIN과 달리, 테이블을 연결 짓는 조인 칼럼에 대한 비교 조건을 연산자('<', BETWEEN A AND B)를 사용한다.

유형에 따라

논리적 조인

사용자 SQL 문에 표현되는 테이블 결합 방식으로, SQLD 에서 사용하는 대부분의 내부 조인, 외부 조인이 여기에 해당된다.

물리적 조인

Database 옵티마이저에 의해 내부적으로 발생하는 테이블 결합 방식으로 중첩 반복 조인, 정렬 합병 조인, 해시 조인이 해당된다.

INNER JOIN

내부 조인이라고 하며, 공통 존재 컬럼의 값이 같은 경우(조인 조건에 일치하는 행)를 추출한다. ANSI 표준의 경우 USING이나 IN 조건절을 필수적으로 사용하며 FROM 절에 INNER JOIN 혹은 줄여서 JOIN을 명시한다.

위와 같이 분리된 두 테이블을 아래 쿼리문을 통해 INNER JOIN이 가능하다.

OUTER JOIN

INNER JOIN과 대비되는 조인 방식으로 JOIN 조건에서 같은 값이 존재하지 않는 행도 반환할 때 사용한다. 외부 조인의 종류로는 왼쪽 외부 조인 (LEFT OUTER JOIN), 오른쪽 외부 조인 (RIGHT OUTER JOIN), 완전 외부 조인 (FULL OUTER JOIN) 이 있다.

1) LEFT OUTER JOIN

FROM 절에 나열된 왼쪽 테이블에 해당하는 모든 데이터와 오른쪽 테이블에서 JOIN 대상이 되는 동일 데이터를 추출한다. 다시 말해, 왼쪽 테이블이 기준이 되어 오른쪽 데이터를 채우는 방식이다. 이때 오른쪽 값에서 동일한 값이 없는 경우 NULL값이 채워져서 출력된다.

2) RIGHT OUTER JOIN

LEFT OUTER JOIN의 반대로, 이번에는 FROM 절에 나열된 오른쪽 테이블에 해당하는 모든 데이터와 왼쪽 테이블에서 JOIN 대상이 되는 동일 데이터를 추출한다. 다시 말해, 오른쪽 테이블이 기준이 되어 왼쪽 데이터를 채우는 방식이다. 이때 왼쪽 값에서 동일한 값이 없는 경우 NULL값이 채워져서 출력된다.

3) FULL OUTER JOIN

양쪽의 모든 데이터를 중복 데이터 삭제 후 리턴한 조인 결과이다. LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과를 UNION한 결과와 동일하며 ORACLE 표준에는 존재하지 않는다.

기타

CROSS JOIN

테이블 간 JOIN 조건이 존재하지 않는 경우 만들 수 있는 모든 데이터들의 조합을 출력한다. 양쪽 테이블 행의 곱한수의 데이터 조합이 발생하며 카타시안곱 (Cartesian Product)를 출력한다. (m*n)

SELF JOIN

자기 자신에게 별칭을 지정한 후 다시 조인하는 자기 참조 조인이다. 한 테이블을 참조할 때마다 명시해야하며 테이블명이 중복되므로 반드시 테이블 별칭을 사용한다.

NATURAL JOIN

두 테이블 간의 동일한 이름을 가지는 모든 칼럼들에 대해 EQUI JOIN을 수행한다. 이때 JOIN에 사용된 칼럼들은 데이터 유형이 동일해야 하며 접두사는 사용이 불가한다. 또한 USING, ON, WHERE 절에서 조건 정의가 불가하다. 여기서는 ID, 부서, 주소 컬럼들이 동일한 이름을 가지고 있다.

위 예시에서는 조인 조건을 만족하는 행들만 결과에 포함된다.
예를 들어: