ORACLE에서 OUTER JOIN만 ORACLE 전용이고 나머지는 공용이다.

NATURAL JOIN

솔직히 잘 안쓰는 JOIN이다. 유지관리가 어렵기 때문에 못 쓰게하는 회사도 있다.

EQUI JOIN의 일환으로 조인조건 술어를 명칭이 동일한 모든 칼럼을 기준으로 자동으로 생성한다.

동일한 명칭의 칼럼이 하나일 경우 유용하지만, 하나 이상일 경우 문제가 생길 수 있으며 또한 타입이 다를경우 오류가 발생한다. 타입이 달라도 자동으로 형 변환해 주지 않는다.

--[oracle]
SELECT e.employee_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id and e.manager_id = d.manager_id;

--ANSI
SELECT e.employee_id, d.department_name
FROM hr.employees e NATURAL JOIN hr.departments d;

위의 두 코드는 같은 코드로 department_id 와 manager_id 2개의 칼럼이 조인조건술어로 기술되어 있기 때문에 원하는 결과를 가져오지 못할 수 있다.

이 경우 USING을 이용해 NATURAL JOIN의 조인조건을 제한할 수 있다.

주의할 점

SELECT e.*, d.*
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id and e.manager_id = d.manager_id;

오라클로 작성하면 정상적으로 출력하지만,

SELECT e.*, d.*
FROM hr.employees e NATURAL JOIN hr.departments d;

ANSI표준으로 작성시 오류를 발생한다.

JOIN USING

NATURAL JOIN의 단점을 극복하여 조인조건을 제한할 수 있다.

단, 이 경우도 양쪽 테이블의 칼럼 이름이 동일한 경우만 가능하다.

또한 USING에서 사용된 기준 칼럼은 테이블을 지정해서는 안된다. 이는 SELECT, USING, WHERE 절에서도 동일하게 적용된다.

SELECT	 e.employee_id, d.department_name 
FROM	 hr.employees e JOIN hr.departments d
USING	( department_id );

SELECT	 e.employee_id, department_id, d.department_name -- 테이블 기술 X
FROM	 hr.employees e JOIN hr.departments d
USING	 ( department_id );
WHERE	 department_id IN ( 10,20,30 ); -- 테이블 기술 X

-- 오류 발생
SELECT	 e.*, d.* -- 오류 발생
FROM	 hr.employees e JOIN hr.departments d
USING	 ( e.department_id ); -- 오류 발생

여러개의 테이블을 조인할 때는 JOIN USING JOIN USING을 사용한다.

SELECT	 e.employee_id, d.department_name, location_id, i.city
FROM	 hr.employees e JOIN hr.departments d
USING	(department_id)
JOIN 	hr.locations i
USING	(location_id);

JOIN ON, INNER JOIN ON

실제로 자주쓰는 문법. ON절에서 조인조건술어를 직접 지정하며 모호성을 구분하기 위해 테이블을 명시한다.

-- oracle
SELECT e.employee_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id;

SELECT e.employee_id, d.department_name
FROM hr.employees e JOIN hr.departments d ON e.department_id = d.department_id;

SELECT e.employee_id, d.department_name
FROM hr.employees e INNER JOIN hr.departments d 
					ON e.department_id = d.department_id;

여러 테이블을 조인할 때도 동일하게 JOIN ON을 반복해서 사용한다. 비 조인조건은 where 절에 기술하면 된다.

SELECT e.employee_id, d.department_name, l.city
FROM hr.employees e JOIN hr.departments d ON e.department_id = d.department_id
					JOIN hr.locations l ON d.location_id = l.location_id;
WHERE e.last_name like '%a%' -- 비조인조건 술어

NONE EQUI JOIN ANSI

큰 차이 없다.

-- oracle
SELECT  e.manager_id, e.salary, j.grade_level
FROM    hr.employees e, hr.job_grades j
WHERE   e.salary >= j.lowest_sal and e.salary <= j.highest_sal;

-- ANSI
SELECT  e.manager_id, e.salary, j.grade_level
FROM    hr.employees e JOIN hr.job_grades j
ON   	e.salary >= j.lowest_sal AND e.salary <= j.highest_sal -- 조인조건 술어
WHERE 	e.salary >= 10000; -- 비조인조건 술어

SELF JOIN ANSI

--oracle
SELECT m.employee_id, m.last_name, w.employee_id, w.last_name
FROM hr.employees w, hr.employees m
WHERE w.manager_id = m.manager_id; 

--ANSI
SELECT m.employee_id, m.last_name, w.employee_id, w.last_name
FROM hr.employees w, hr.employees m
ON w.manager_id = m.manager_id; 

OUTER JOIN

ANSI표준이 더 직관적이다. ANSI표준을 쓰는 회사도 있고 아닌 회사도 있다. 회사 기준으로 사용하자.

문법만 다르지 개념은 같다.

LEFT OUTER JOIN

-- oracle
SELECT e.employee_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id(+);

-- ANSI
SELECT e.employee_id, d.department_name
FROM hr.employees e LEFT OUTER JOIN hr.departments d
	 ON e.department_id = d.department_id;

RIGHT OUTER JOIN

-- oracle
SELECT e.employee_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id(+) = d.department_id;

-- ANSI
SELECT e.employee_id, d.department_name
FROM hr.employees e RIGHT OUTER JOIN hr.departments d
	 ON e.department_id = d.department_id;

FULL OUTER JOIN

오라클에서 지원하지 않는 기능 양 테이블에서 조회되지 않는 데이터도 조건에 맞는다면 출력.

-- oracle
SELECT e.employee_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id(+) = d.department_id(+); -- 오류 발생
-- 해결방법 : 서브쿼리, 집합 연산자.

-- ANSI
SELECT e.employee_id, d.department_name
FROM hr.employees e FULL OUTER JOIN hr.departments d
	 ON e.department_id = d.department_id;

오라클 문법으로 굳이 풀고 싶다면 아래와 같이 풀면되는데 2가지 문제가 있다.

1. 같은 테이블을 2번 접근해야 한다.
2. 중복제거하는 과정을 거쳐 효율이 나쁘다.

-- oracle (동일한 테이블 2번 접근하는 문제, 중복제거과정 )
SELECT e.employee_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id(+);

UNION

SELECT e.employee_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id(+) = d.department_id;

CARTESIAN PRODUCT ,CROSS JOIN

오라클에서 CARTESIAN PRODUCT는 ANSI의 CROSS JOIN과 완전히 일치한다.

-- oracle
SELECT e.employee_id, d.department_name
FROM hr.employees e, hr.departments d;

-- ANSI
SELECT e.employee_id, d.department_name
FROM hr.employees e CROSS JOIN hr.departments d;