[SQL 15] 조인 - ON 절, 외부 조인, 교차 조인(Cross Join)

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

이제부터 SQL:1999 표준에서 제시되어 있는 조인을 표현하는 방법을 배워보도록 하겠다

No. 01
ON 절을 이용한 조인

ON 절을 이용한 조인 표현법은 임의의 조인 조건을 지정하는 방법으로 일반적인 모든 형태의 조인을 표현할 수 있다.

기본 구조

SELECT 테이블1.컬럼, 테이블2.컬럼, …

FROM 테이블1

JOIN 테이블2 ON 조인 조건

[JOIN 테이블3 ON 조인 조건] …

WHERE 검색 조건;

• 자연 조인, USING 절을 이용한 조인 등을 모두 표현할 수 있다.
• 조인되는 컬럼의 이름이 서로 다르거나 비등가 조인인 경우 많이 사용한다.
• 비등가 조인과 같은 다양한 조인에서 사용한다.

ON 절을 이용한 조인 표현 기법은 외부 조인이나 교차 조인을 제외한 모든 조인 표현이 가능

◈ 예제

select employee_id 사원번호, first_name 이름,

departments.department_id 부서번호, department_name 부서명

from employees

join departments on employees.department_id = departments.department_id;

ON 절을 이용한 조인은 조인조건에 사용한 컬럼이라도 SELECT 절에 테이블명을 기술해야 한다.

▶ 여러 가지 표현법 비교

select department_id 부서번호, department_name 부서이름, d.location_id 지역번호, city 도시

from departments d, locations l

where d.location_id = l.location_id;

select department_id 부서번호, department_name 부서이름, location_id 지역번호, city 도시

from departments

natural join locations;

select department_id 부서번호, department_name 부서이름, location_id 지역번호, city 도시

from departments

join locations using (location_id);

select department_id 부서번호, department_name 부서이름, d.location_id 지역번호, city 도시

from departments d

join locations l on d.location_id = l.location_id;

위의 조인 문장들의 결과는 동일하다.

동일한 조인문에 대해서 경우에 따라 여러 가지 조인 방법이 있을 수 있다는 걸 알고있자.

▶ 검색 조건 쓰는 두 가지 방법

select department_id 부서번호, department_name 부서이름, d.location_id 지역번호, city 도시

from departments d

join locations l on d.location_id = l.location_id

where city = 'London';

select department_id 부서번호, department_name 부서이름, d.location_id 지역번호, city 도시

from departments d

join locations l on d.location_id = l.location_id

and city = 'London';

ON 절을 이용한 조인은 where 절 대신 on에 and로 이어서 검색 조건을 기술할 수 있다.

on 절에 검색 조건을 기술하다는 것만 알고 있고, where 절에 기술하기를 권한다.

▶ ON 절을 이용한 비등가 조인 표현 ([SQL 11]의 비등가조인 예제 참고)

select employee_id, first_name, salary, grade

from employees, salarygrade

where salary between losalary and hisalary;

select employee_id, first_name, salary, grade

from employees

join salarygrade on losalary and hisalary;

예전에 언급했듯이 아직 salarygrade 테이블이 없기 때문에 실습할 수 없다.

테이블을 만드는 법을 배우고나면 다시 이부분만 실습해 보도록 하겠다. 지금은 ON 절을 이용한 조인은 비등가 조인도 할 수 있다는 것만 알아두자.

▶ ON 절을 이용한 자체 조인(Self Join)

select e1.employee_id 사원번호, e1.first_name 이름,

e1.manager_id 상사번호, e2.first_name 상사이름

from employees e1

join employees e2 on e1.manager_id = e2.employee_id;

ON 절을 이용한 자체 조인이다.

▶ ON 절과 USING 절의 혼용

select department_id 부서번호, department_name 부서이름, city 도시, country_name 국가이름

from departments d

join locations l on d.location_id = l.location_id

join countries c on c.country_id = l.country_id

where city in ('Seattle', 'London');

select department_id 부서번호, department_name 부서이름, city 도시, country_name 국가이름

from departments d

join locations l using (location_id)

join countries c on c.country_id = l.country_id

where city in ('Seattle', 'London');

ON 절은 USING 절과 혼용이 가능하지만, 규칙을 정해놓고 사용해야 헷갈리지 않는다.

등가 조인은 USING 절로만 사용하고, 비등가 조인은 ON 절로만 사용한다.

물론, 항상 ON 절만 이용할 수도 있다.

No. 02
외부 조인(Outer Join) - LEFT, RIGHT, FULL

예전 표현법 중 (+)기호를 사용하던 외부 조인과 같은 결과를 출력할 수 있다. 게다가 이전 외부 조인에는 없던 양 쪽 모두 (+) 기호를 추가한 것과 동일한 표현이 가능하다.

기본 구조

SELECT 테이블1.컬럼, 테이블2.컬럼, …

FROM 테이블1

[LEFT | RIGHT | FULL] [OUTER] JOIN 테이블2 [ON 조인 조건 | USING (조인 조건)]

WHERE 검색 조건;

• 조인 조건에 일치하지 않는 데이터까지 모두 출력해 준다.
• 출력된 데이터의 신뢰성을 제공해주기 위한 방법이다.
• 조인 조건은 ON 절이나 USING 절 모두 사용할 수 있다.
• 자연 조인과 혼용해서 사용할 수 없다.
• 부족한 쪽 데이터는 자동으로 NULL 값으로 설정된다.

앞쪽에 먼저 기술한 테이블을 왼쪽 테이블, 뒤쪽 테이블을 오른쪽 테이블로 간주한다.  <테이블1> : 왼쪽 테이블, <테이블2> : 오른쪽 테이블
LEFT	왼쪽 테이블의 데이터는 모두 검색에 포함된다.
RIGHT	오른쪽 테이블의 데이터는 모두 검색에 포함된다.
FULL	양쪽 테이블 모두의 데이터가 검색에 포함된다.

각 옵션에 따라 조인 조건과 일치하지 않는 행도 검색에 포함한다.

외부 조인을 사용하는 이유는 이전에 언급했기 때문에 다시 말하지 않겠다. ([SQL 13] 참고)

◈ 예제

▶ OUTER 생략

select e.last_name, department_id, d.department_name

from employees e

left join departments d using (department_id);

OUTER 는 생략 가능하다.

▶ LEFT OUTER JOIN

select e.last_name, department_id, d.department_name

from employees e

left outer join departments d using (department_id)

order by department_id desc;

select e.last_name, e.department_id, d.department_name

from employees e

left outer join departments d on e.department_id = d.department_id

order by e.department_id desc;

왼쪽 테이블인 EMPLOYEES 테이블에서 Grant 사원은 부서가 없기 때문에 department_id는 null 값이다. 그러나 DEPARTMENTS 테이블은 department_id가 null인 부서의 이름을 갖고 있지 않다. 외부 조인을 사용하지 않았다면 무시 됐을 것이다. LEFT 외부 조인을 사용함으로써 왼쪽 테이블인 EMPLOYEES의 데이터가 모두 검색되었고, 매칭되지 않은 행은 NULL 값으로 표시된다.

▶ RIGHT OUTER JOIN

select e.last_name, d.department_id, d.department_name

from employees e

right join departments d on e.department_id = d.department_id;

이번에는 반대로 오른쪽 테이블인 DEPARTMENTS 테이블에는 27개의 부서가 있지만 16개의 부서에는 아직 사원이 없다. RIGHT 외부 조인으로 오른쪽 테이블의 부서가 모두 검색되었고, 매칭되지 않은 행은 NULL 값으로 표시된다.

▶ FULL OUTER JOIN

select e.last_name, department_id, d.department_name

from employees e

full outer join departments d using (department_id)

order by department_id desc;

LEFT 외부 조인과 RIGHT 외부 조인을 합쳐 놓은 결과와 같다.

No. 03
교차 조인(Cross Join)

카네이션 곱(Cartesian Product) 이라고도 부른다.

조인되는 테이블의 각각의 행들이 모두 매핑된 데이터가 검색되는 조인 방법이다.

기본 구조

SELECT 테이블1.컬럼, 테이블2.컬럼, …

FROM 테이블1

CROSS JOIN 테이블2;

• 조건이 생략된 조인문과 결과가 동일하다.
• 두 테이블의 교차 곱을 수행한다.
• 공통 컬럼과 관계가 없다.
• 테이블1과 테이블2를 교차 조인하면 각 테이블의 행의 수를 곱한 것과 같은 개수의 결과 행이 생긴다.

교체 조인을 사용한다면 결과 값에 대한 해석에 주의해야 한다.

교차 조인을 하면 다음과 같은 결과를 얻을 수 있다.

조인의 이름 그대로 Cross를 하는 것이다.

행의 개수가 2개인 테이블과 3개인 테이블의 교차 조인하면 2 x 3 = 6 개의 행이 생긴다.

◈ 예제

select department_id, department_name, city

from departments

cross join locations;

총 621개의 행이 검색되는 것을 확인할 수 있다.

DEPARTMENTS 27행 x LOCATIONS 23행 = 621행

교차 조인의 결과는 일반적으로 잘못된 정보이다.

전산업무에서 부득이 수행하는 경우가 있다고 하지만, 웬만하면 사용할 일은 없을 것이다.

그냥 이런 형태의 조인이 있다고만 알아두자.

이로써 모든 조인 표현법에 대해 배워봤다.