- 2개 이상의 테이블을 연결해서 데이터를 검색하는 방법
- 보통 두 개 이상의 행(row)들의 공통된 값의 기본키, 외래키를 사용해서 join함
- 기본 키(primary key) : 테이블에서 중복되지 않는 키
- 외래 키(foreign key) : 다른 테이블에서 pk(primary key), uk(unique key)인 경우
- 교집합
- ansi SQL
select employee_id, e.first_name,e.department_id, d.department_id, department_name from EMPLOYEES e inner join DEPARTMENTS d on e.department_id = d.department_id;
- oracle
select e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name from EMPLOYEES e, DEPARTMENTS d where e.department_id = d.department_id
- 아래 결과처럼 연결되어 출력된다
- 하나가 다른 모든것과 join함
- ansi
select e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name from EMPLOYEES e cross join departments d;
- oracle
select e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name from EMPLOYEES e, departments d;
- 동일한 테이블을 join
select a.employee_id, a.first_name, a.manager_id, b.employee_id, b.first_name from EMPLOYEES a, EMPLOYEES b -- a:사원, b:상사 where a.manager_id = b.employee_id;
- 출력 결과
- ansi
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id;
- oracle
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id(+);
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id(+) AND e.department_id IS NULL; -- null인 값만 취급하면 왼쪽만 구할 수 있음
- ansi
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;
- oracle
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id;
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id AND e.department_id IS NULL;
- 합집합
- ansi
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.department_id;
- oracle(잘 사용하지 않음)
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id(+) UNION SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id;
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.department_id WHERE e.department_id IS NULL OR d.department_id IS NULL;
사진 출처 : https://commons.wikimedia.org/wiki/File:Joins_del_SQL.svg