[SQL] 6. 조인을 이용한 다중 테이블 검색

조인이란?

- 하나 이상의 테이블로부터 데이터를 질의하기 위해 조인을 사용

- WHERE 절에 조인 조건 사용

- 하나 이상의 테이블에 똑같은 열 이름이 있을 때 열 이름 앞에 테이블 이름을 붙임

- 오라클 조인과 ANSI JOIN이 있음.

 

오라클 JOIN

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column;

 

1. CARTESIAN PRODUCT

- 조인 조건이 생략된 경우

- 조인 조건이 잘못된 경우

- 첫 번째 테이블의 모든 행이 두 번째 테이블의 모든 행과 조인되는 경우

SELECT table1.col, table2.col
FROM table1, table2;
--카티션프로덕트발생(조인 조건이 없기 때문)

2. EQUI JOIN

- 2개 이상의 테이블이 공통된 열에 의해 논리적 결합하는 조인 기법

- WHERE 절에 사용된 열들이 동등연산자(=) 에 의해 비교

select e.first_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;

 

3. SELF JOIN

- 자체적 테이블 조인

select e.first_name, m.first_name
from employees e, employees m
where e.manager_id = m. employee_id and e.employee_id=103;

 

4. NON-EQUI JOIN

- equal 연산자(=)로 조인하지 않음

select e.first_name, e.salary, j.job_title
from employees e, jobs j
where e.salary
between j.min_salary and j.max_salary
order by e.first_name;

 

5. OUTER JOIN

- 조인 조건을 만족하지 않는 행들도 보기 위해서 Outer 조인을 사용함

- Outer 조인 연산자는 더하기 기호(+) 임.

SELECT table.column, table.column
FROM table1, table2
WHERE table1.column(+) = table2.column;

SELECT table.column, table.column
FROM table1, table2
WHERE table1.column = table2.column(+);

select e.employee_id, e.first_name, e.hire_date,
j.start_date, j.end_date, j.job_id, j.department_id
from employees e, job_history j
where e.employee_id = j.employee_id
order by j.employee_id;

안시 조인

- 오라클 9i 버전부터 ANSI SQL 표준 문법 사용이 가능

- 조인의 형태가 FROM절에서 지정되며, 조인 조건이 ON 절 또는 USING 절에 표시

SELECT t1.col, t2, col
FROM t1
[LEFT|RIGHT|FULL][OUTER] JOIN t2
ON jooin_conditions
[WHERE conditions];

 

1. CROSS JOIN

- 두 개의 테이블에 대한 Cartesian Product와 같은 결과 출력

SELECT table1.column1, table2.column2
FROM table1
CROSS JOIN table2;

select employee_id, department_name
from employees
cross join departments;

 

 

2. NATURAL JOIN

- 모든 같은 이름을 갖는 열들에 대해 조인

- 지정해주지 않아도 자동으로 같은 이름가진 열에 대해 Equi조인을 수행한다.

SELECT table1.column1, table2.column2
FROM table1
NATURAL JOIN talbe2;

select first_name, job_title
from employees
natural join jobs;

 

3. USING JOIN

- using 절을 이용하여 원하는 열에 대해서만 선택적으로 Equi 조인을 할 수 있음.

SELET table1.column1, table2.column2
FROM table1
JOIN table2
USING (column);

select first_name, department_name
from employees
join departments
using (department_id);

 

4. ON JOIN

- on을 이용해 where에 들어갈 조인 조건을 적을 수 있음

SELECT table1.column1, table2.column2
FROM table1
JOIN table2
ON join_condition;

select department_name, street_Address, city, state_province
from departments d
join locations l
on d.location_id = l.location_id;

1) 여러 테이블의 조인

select e.first_name, d.department_name,
l.street_address || ', ' || l.city || ', '||
l.state_province as address
from employees e
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id;

2) WHERE 절과의 혼용

select e.first_name as name,
d.department_name as department
from employees e
join departments d
on e.department_id = d.department_id
where employee_id = 103;

select e.first_name, d.department_name,
l.street_address || ', ' || l.city || ', '||
l.state_province as address
from employees e
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id
where employee_id=103;

 

3) ON절에 WHERE절의 조건 추가

select e.first_name as name,
d.department_name as department
from employees e
join departments d
on e.department_id = d.department_id and employee_id = 103;

select e.first_name, d.department_name,
l.street_address || ', ' || l.city || ', '||
l.state_province as address
from employees e
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id and employee_id=103;

 

5. OUTER JOIN

SELECT table1.column, table2.column
FROM table1
[LEFT|RIGHT|FULL][OUTER]JOIN table2
ON join_conditions

select e.employee_id, e.first_name, e.hire_date,
j.start_date, j.end_date, j.job_id, j.department_id
from employees e
left outer join job_history j
on e.employee_id = j.employee_id
order by e.employee_id;

1) LEFT OUTER JOIN

- 왼쪽의 테이블이 기준

select department_name, first_name 
from departments d
left join employees e
on d.manager_id = e.employee_id;

 

2) RIGHT OUTER JOIN

- 오른쪽 테이블이 기준

select department_name, first_name 
from employees e
right join departments d
on d.manager_id = e.employee_id;

 

3) FULL OUTER JOIN

- 왼쪽 오른쪽 모두 기준