[SQL] 조인(JOIN) - Inner join, outer join, cross join

 

조인(JOIN)은 두 개 이상의 테이블(뷰나 서브쿼리와 같은 논리 테이블 포함)을

연결하여 데이터를 검색하는 방법이다. 

 

두 개 이상의 릴레이션(테이블)로부터 '관련된 튜플들'을 결합하여

하나의 튜플로 만드는 가장 대표적인 데이터 연결 방법이다. 

 

여기서 포인트는 '관련된 튜플들'이라는 점이다.

조인의 전제조건으로는 연결하려는 테이블에 

각각 '관련된 튜플들', 즉 매칭되는 컬럼이 있어야 한다. 

 

예를 들어, 회원 테이블에 있는 회원명 컬럼과 

자동차 등록정보 테이블에 있는 소유주명 컬럼과 같이

값이 사람의 이름이며, 조인 시 관련 튜플로서

연결될 수 있는 컬럼을 기준으로 조인할 수 있는 것이다. 

 

조인의 종류

 

조인은 크게 논리적 조인과 물리적 조인으로 구분된다.

분류	설명	유형 예시
논리적 조인	사용자 SQL 문에 표현되는 테이블 결합 방식	내부 조인 외부 조인 교차 조인 셀프 조인
물리적 조인	데이터베이스 옵티마이저에 의해 내부적으로 발생하는 테이블 결합 방식	중첩 반복 조인 정렬 합병 조인 해시 조인

 

혹은 세부 기준에 따라 아래와 같이 분류될 수도 있다. 

- 조인 연산자에 따른 구분 : 동등 조인, 안티 조인

- 조인 대상에 따른 구분 : 셀프 조인

- 조인 조건에 따른 구분 : 내부 조인, 외부 조인, 세미 조인, 카타시안 조인

- 기타 : ANSI 조인

 

이 조인들이 각각 독립적인 개념은 아니다. 

예를 들어, 셀프 조인이나 안티 조인 등은 모두 내부 조인에 포함되는 개념이다.

 

또한 ANSI 조인의 경우 내부, 외부, 동등, 안티, 셀프, 세미, 카타시안 조인을

모두 포함하는 개념으로 ANSI SQL을 사용한다는 점에서 차이가 있다.

(일반적으로 ANSI 조인이라고 굳이 구분하지는 않는다.)

 

이렇게 많은 조인을 다 암기할 필요는 없을 것 같다..!

중요한 것은 각 조인 방법을 이해하고 필요에 따라

적절히 사용할 줄 아는 것이라 생각한다. 

(물론 뭐가 있는 지 알아야 쓸 수 있긴 하겠지만...)

 

조인을 사용할 때는 서브쿼리와 함께 쓰이는 경우가 제법 있다.

또한 워낙 하나의 논리 테이블을 지칭하는 쿼리문이 길어서

각 테이블에 별칭을 지정해주고 해당 별칭을 활용한다는 점도 특징이다. 

 

 

 

이번 글에는 오라클에서 쓰이는 ANSI 조인에 대해,

그 중에서도 자주 쓰이는 4가지 조인 방법인

INNER, OUTER, CROSS, FULL OUTER 조인에 대해

자세히 알아 보고자 한다.  (기존문법과 비교)

 

출처 : Wikitechy.com

 

ANSI 조인이란

 

ANSI 조인은 ANSI SQL 문법을 사용한 조인을 말한다.

위에서 설명한 모든 조인은 ANSI SQL을 사용해 변환할 수 있다.

 

기존 문법과 ANSI 조인의 차이점은

기존 문법의 경우,

조인 조건이 WHERE절에 들어가고

ANSI 조인의 경우,

조인 조건이 WHERE절이 아닌 FROM 조건에 들어간다는 점이다.

 

ANSI 내부 조인 (INNER JOIN)

 

내부 조인은 조인 대상이 되는 테이블들에서

공통적으로 존재하는 컬럼의 값이 같은 경우

추출하는 방식을 말한다.

// 기존 문법
SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2 ...
FROM 테이블 A, 테이블 B
WHERE A.컬럼1 = B.컬럼1 -> 조인 조건
;

// ANSI 문법
SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2 ...
FROM 테이블 A
[INNER] JOIN 테이블 B
ON (A.컬럼1 = B.컬럼1) -> 조인 조건
WHERE ... 조건
;

 

*** INNER 는 생략 가능하다. 

 

위 문법을 토대로 employees 테이블과 departments 테이블을 조인하여

2013년 1월 1일 이후에 입사한 사원의 사원번호, 사원명, 부서번호, 부서명을

조회하는 쿼리를 작성해보자.

// 기존 문법 
SELECT 
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM 
    employees e,
    departments d
WHERE 
    e.department_id = d.department_id
    AND e.hire_date >= TO_DATE('2013-01-01', 'YYYY-MM-DD');


// ANSI 문법
SELECT 
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM 
    employees e
INNER JOIN 
    departments d 
ON e.department_id = d.department_id
WHERE 
    e.hire_date >= TO_DATE('2013-01-01', 'YYYY-MM-DD');

 

 

USING? 

만약 조인 조건 컬럼이 두 테이블 모두 동일하다면,

ON 대신 USING 절을 사용할 수도 있다. 

이때는 SELECT절에서 조인 조건에 포함된 컬럼명을

테이블명.컬럼명 형태가 아닌 컬럼명만 기술해야 한다.

그 이유는 USING 절의 컬럼 부분은 식별자를 가질 수 없기 때문이다.

 

물론 두 테이블 간 조인 조건에 사용되는 컬럼명이 동일한 경우가 많지만

다를 때도 있으므로, USING 대신 ON절을 사용하는 것이 일반적이다.

 

아래 예시를 통해 USING에 대해 간단히 살펴보자.

 

SELECT e.employee_id, e.employee_name, department_id, b.department_name

FROM employees

INNER JOIN departments

USING (department_id)

WHERE hire_date >= TO_DATE('2013-01-01', 'YYYY-MM-DD') ;

 

 

ANSI 외부 조인 (OUTER JOIN)

구분	설명
왼쪽 외부 조인 (Left Outer Join)	왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출
오른쪽 외부 조인 (Right Outer Join)	오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출
완전 외부 조인 (Full Outer Join)	양쪽의 모든 데이터를 추출

 

외부 조인도 내부 조인과 형식은 유사하다. 

차이가 있다면,

기존 문법에서는 기존 테이블과 대상 테이블(데이터가 없는 테이블)에서

대상 테이블쪽 조인 조건에 (+)표시를 붙였지만, 

ANSI 문법에서는 (+)표시 없이 FROM절에 명시된 테이블 순서에

근거하여 먼저 명시된 테이블 기준으로 해당 조인이 LEFT 조인인지

RIGHT 조인인지 맞게 작성해야 한다.

 

아래 기존 문법 예시에서 보면,

WHERE절에서 테이블 B의 컬럼1옆에 (+)표시를

추가해야 한다. (+) 표시는 한 쪽에만 붙일 수 있다. 

// 기존 문법
SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2 ...
FROM 테이블 A, 테이블 B
WHERE A.컬럼1 = B.컬럼1(+) -> 조인 조건
;

// ANSI 문법
SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2 ...
FROM 테이블 A
LEFT(RIGHT) [OUTER] JOIN 테이블 B
ON (A.컬럼1 = B.컬럼1) -> 조인 조건
WHERE ... 조건
;

 

*** OUTER는 생략 가능하다. 

 

예시를 통해 살펴보자.

// 기존 문법 
SELECT 
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM 
    employees e,
    departments d
WHERE 
    e.department_id(+) = d.department_id
    AND e.hire_date >= TO_DATE('2013-01-01', 'YYYY-MM-DD');


// ANSI 문법
SELECT 
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM 
    employees e
LEFT OUTER JOIN 
    departments d ON e.department_id = d.department_id
WHERE 
    e.hire_date >= TO_DATE('2013-01-01', 'YYYY-MM-DD');

 

FULL OUTER 조인

 

FULL OUTER 조인은 외부 조인의 하나로

ANSI 조인에서만 제공하는 기능이다.

기존 문법으로는 FULL OUTER 조인을 사용할 수 없지만,

같은 결과를 내기 위해서는

집합 연산자 UNION ALL을 활용하면 가능하다.

 

*기존문법 UNION ALL 예시보기

SELECT e.employee_id, e.employee_name, d.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id(+) = d.department_id

UNION ALL

SELECT e.employee_id, e.employee_name, d.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id(+);

 

앞서 설명한 조인의 경우 조인한 두 테이블 중 어느 한 테이블에

조인 조건에 만족하는 데이터가 없더라도,

즉 NULL이어도 조회가 되었다. 

 

이와 달리 FULL OUTER 조인은

두 테이블 모두 조인 조건에 만족하는 데이터가 없어도,

즉 NULL인 값이 존재해도 조회할 수 있다. 

(한쪽만 NULL을 허용하되, 그 한쪽이 A테이블이 될 수도

B테이블이 될 수도 있다는 의미이다.)

 

예시를 통해 살펴보자.

SELECT 
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM 
    employees e
FULL OUTER JOIN 
    departments d 
ON e.department_id = d.department_id;

 

이렇게 조인하면 결과로 출력되는 튜플 중에서

어떤 튜플의 값이든 NULL을 가지고 있어도 

모든 튜플이 출력된다. 

 

ANSI 교차 조인(CROSS JOIN)

 

교차조인은 논리적 조인의 한 종류로, 

WHERE 절에 조인 조건이 없는 모든 데이터 조합을 추출한다.

즉, FROM 절에 테이블을 명시했으나, 

두 테이블 간 조인 조건이 없는 조인이다. 

 

조인 조건이 없으므로 엄밀히 말하면 조인이라 

하기 애매하지만, FROM 절에서 2개 이상 테이블을 

명시했으므로 일종의 조인이라 할 수 있다. 

 

조인 조건이 없는 모든 데이터 조합을 추출하기 때문에

ON절이 별도로 필요 없다.

 

결과적으로 조인을 하는 테이블A와 테이블B에서

각각 테이블이 가진 튜플수를 서로 곱한 수만큼

결과 건수가 조회된다. 

 

테이블A의 튜플수 * 테이블B의 튜플수 = 교차조인 결과 건수

 

이러한 조인을 ANSI 조인에서는 교차조인이라 말하고

기존 문법에서는 카타시안 조인이라 부른다.

 

예시를 통해 살펴보자.

// 기존 문법 (카타시안 조인)
SELECT 
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM 
    employees e,
    departments d
;


// ANSI 문법 (교차 조인)
SELECT 
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM 
    employees e
CROSS JOIN 
    departments d
;