-펌-[DB] 조인(Join) 함수

출처 - http://repilria.tistory.com/285

조인(Join)이란? 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법

★ 조인의 종류?
- 등가 조인 (equi join)
- 비등가 조인 (non-equi join)
- 포괄 조인 (outer join)
- 자체 조인 (self join)
- 교차 조인 (Cross Joins)
- 자연 조인 (Natural Joins)

1. 카티시안 곱 (Cartesial Product)
- 조인 조건이 부적합하거나 조인 조건을 완전히 생략한 경우 행의 모든 조합을 표시하는 카티시안 곱이 생성된다.
첫번째 테이블의 모든 행이 두번째 테이블의 모든 행에 조인된다.
카티시안 곱은 너무 많은 행을 생성하므로 결과가 유용하게 사용되지 않는다.
특별히 모든 테이블에 있는 모든 행을 조합해야 하는 경우가 아니라면 where절에 유효한 조인 조건을 포함시켜야 한다.
적정 양의 데이터를 시뮬레이션하기 위해 많은 수의 행을 생성해야 하는 경우 등 일부 테스트에서는 유용하게 사용된다.

SQL> select last_name, department_name
2 from employees, departments;

2. 등가 조인 (equi join) - 단순 조인 or 내부 조인(inner join)이라고도 함
- 주로 Primary Key와 Foreign Key를 보조 수단으로 사용하여 조인
- departments 테이블과 employees 테이블은 department_id를 공통된 컬럼으로 가지고 있다.
따라서 이 컬럼을 where절에서 조건으로 지정해 준다면 등가 조인을 할 수 있다.
- 등가 조인의 성능을 높이기 위해서는 Index 기능을 사용하는 것이 좋다.

SQL> select e.employee_id, e.last_name, d.department_id, d.location_id
2 from employees e, departments d
3 where e.department_id = d.department_id;

3. 비등가 조인 (non-equi join)
- 동등 연산자(=)가 아닌 연산자를 포함하는 조인
- between and, is null, is not null, in, not in
- ex) employees 테이블의 급여는 jobs 테이블의 min_salary와 max_salary 사이에 있어야 한다.

SQL> select e.last_name, e.salary, j.job_title
2 from employees e, jobs j
3 where e.salary between min_salary and max_salary;

4. 포괄 조인 (outer join)
- outer join은 조인 조건을 만족하지 않는 행도 반환한다.
- 연산자는 더하기 기호 (+) 이고, 조인시킬 값이 없는 조인측에 위치시킨다.
- outer join 연산자는 표현식의 where절에서 한 편에만 올 수 있고, in, or 연산자는 사용할 수 없다.
- 아래 쿼리문은 사원이 없는 부서의 테이블 이름도 반환한다.

SQL> select e.last_name, e.department_id, d.department_name
2 from employees e, departments d
3 where e.department_id(+) = d.department_id;

- 4.1 Left Outer Join : 왼쪽 테이블에 조인시킬 컬럼의 값이 없는 경우 사용한다. (아래의 SQL문은 같은 의미)

SQL> 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)

SQL> select e. last_name, e.department_id, d.department_name
2 from employees e, departments d
3 where d.department_id (+) = e.department_id;

 

- 4.2 Right Outer Join : 오른쪽 테이블에 조인시킬 컬럼의 값이 없는 경우 사용한다.

SQL> select e.last_name, e.department_id, d.department_name
2 from employees e
3 right outer join departments d
4 on (e.department_id = d.department_id);

SQL> select e.last_name, e.department_id, d.department_name
from employees e, departments d
where d.department_id = e.department_id (+)

 

- 4.3 Full Outer Join : 양쪽 테이블에 모두 Outer Join을 하는 것으로 Two-way Outer Join 이라고도 한다.

SQL> select e.last_name, e.department_id, d.department_name
from employees e
full outer join departments d
on (e.department_id = d.department_id)

5. 자체 조인 (self join)
- Equi Join과 동일하지만 하나의 테이블 안에서 조인이 일어난다.
- 같은 테이블을 마치 2개의 테이블을 사용하는 것처럼 조인한다.
- ex) 각 사원의 관리자 이름을 찾으려면 Employees 테이블을 자체 조인해야 한다.
즉, 각 사원의 매니저 ID와 사원 ID가 동일한 사원을 찾아야 한다.

SQL> select worker.last_name || ' works for ' || manager.last_name
2 from employees worker, employees manager
3 where worker.manager_id = manager.employee_id;

 

6. 교차 조인 (cross join)
- cross join은 두 테이블의 상호간의 조합을 생성한다.
- 두 테이블 사이의 카티시안 곱(cartesian product)의 결과와 동일하다.

SQL> select last_name, department_name
from employees
cross join departments

7. 자연 조인 (Natural Join)
- 두 테이블의 하나 이상의 공통된 컬럼(데이터 타입과 이름이 일치해야함)을 기반으로 조인연산을 수행한다.
- DEPARTMENTS 테이블과 LOCATIONS 테이블은 공통적으로 NUMBER(4) 타입의 LOCATION_ID 속성이 존재한다.
따라서 두 테이블은 LOCATION_ID 컬럼을 통해서 자연 조인이 가능하다.
- 일반적으로 등가 조인 (equi join)과 동일하다고 보면 된다.
단지 where절로 조건을 거느냐 natural join 키워드로 조인을 거느냐의 차이?
(자연 조인안에서 다시 where절로 조건을 걸수 있음)

SQL> select department_id, department_name, location_id, city
2 from departments
3 natural join locations;

- 7.1 Using절을 포함하는 조인
-- Using절을 사용하면 equi join에 사용될 열만을 지정할 수 있다.
-- Using절 안에 포함되는 컬럼에 alias를 지정하면 오류가 발생한다.

SQL> select e.employee_id, e.last_name, d.location_id
from employees e join departments d
using (department_id)

- 7.2 On 절을 이용한 조인
-- 자연 조인의 기본 조인 조건은 기본적으로 같은 이름을 가진 모든 열의 equi join 이다.
-- 임의 의 조건을 지정하거나 조인할 열을 지정하려면 on절을 사용해야 한다.

SQL> select e.last_name emp, m.last_name mgr
from employees e join employees m
on (e.manager_id = m.employee_id)

테이블 Alias를 사용하면 데이터베이스의 액세스 속도를 높이고, SQL 코드의 양이 작아지므로 메모리 사용이 줄어든다.