Oracle SQL DML : SELECT 04(JOIN)

JOIN

두개 이상의 테이블이 필요할때, 테이블을 연결하여 데이터를 검색하는 방법이다.
보통 두개이상의 행(row)들의 공통된 값 Primary key(기본키), Foreign key(외래키)값을 사용해서 JOIN한다.

 

Primary key(기본키) : 테이블에서 중복이 되지 않는 키
Foreign key(외래키) : 다른 테이블에서 Primary key, Unique key 일 가능성이 크다.

(키에 대한 자세한 설명은 Oracle - DDL - DDL02 글을 참고.)

 

여기서 설명할 JOIN의 종류는 총 5가지이며, 주로 사용하는것은 inner JOIN과, self JOIN이다.

INNER JOIN

-- Ansi SQL
SELECT employee_id, first_name, e.department_id, d.department_id, d.department_name
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id;

-- Oracle SQL
SELECT employee_id, first_name, e.department_id, d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

departments 테이블의 department_id 기본키(PK)를 

employees 테이블의 한 COLUMN에 외래키(FK)인 department_id를 넣어두고 이를 이용하여,

테이블을 일치 시켜, 필요한 테이터를 선택하여 사용하는 방법이다.

employees 테이블에 department_id는 null을 허용하지만,

departments테이블에서 department_id는 null을 허용하지 않기 때문에 더 많은 department_id를 갖고 있다.

하지만, INNER JOIN의 경우 employees, departments 두개의 테이블 모두 갖고 있는 데이터만을 선택할 수 있다.

즉, 교집합과 같은 의미를 갖는다.

CROSS JOIN

-- Ansi SQL
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e CROSS JOIN departments d;

-- Oracle SQL
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e, departments d;

FULLOUTER JOIN

-- Ansi SQL full outer Join
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;

OUTER JOIN(left, right)

-- Ansi SQL
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 SQL
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(+) -- 현재 left join
AND e.department_id IS NULL;

SELF JOIN

-- Ansi SQL = Oracle SQL
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;

CONNECT BY : self join을 활용한 계층형구조 상향, 하향

-- 계층형 구조 오름, 내림
SELECT a.employee_id, a.first_name as "사원", a.manager_id as "사원의 상사", b.employee_id, b.first_name as "상사"
FROM employees a, employees b
WHERE a.manager_id = b.employee_id
-- CONNECT BY PRIOR a.manager_id = a.employee_id;  -- 상향식
CONNECT BY a.manager_id = PRIOR a.employee_id; -- 하향식