[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를 교차 조인하면 각 테이블의 행의 수를 곱한 것과 같은 개수의 결과 행이 생긴다.
교체 조인을 사용한다면 결과 값에 대한 해석에 주의해야 한다.
교차 조인을 하면 다음과 같은 결과를 얻을 수 있다.
select department_id, department_name, city
from departments
cross join locations;
총 621개의 행이 검색되는 것을 확인할 수 있다.
DEPARTMENTS 27행 x LOCATIONS 23행 = 621행
교차 조인의 결과는 일반적으로 잘못된 정보이다.
전산업무에서 부득이 수행하는 경우가 있다고 하지만, 웬만하면 사용할 일은 없을 것이다.
그냥 이런 형태의 조인이 있다고만 알아두자.
이로써 모든 조인 표현법에 대해 배워봤다.