[Oracle] Join (Inner, Outer, ANSI Join)

JOIN의 목적 : 찾는 데이터가 여러 테이블에 분산되어 있기 때문임

 

1 ) Oracle 조인 > JOIN조건과 검색조건이 WHERE절에 명시됨

1.1 Inner join : 반드시 일치하는 data를 반환함 (교집합)

•   Equi Join : 동등 연산자를 이용한 조인 (FK/PK를 주로 사용함)

SELECT table1.column, table2.column
FROM table1, table 2
WHERE table1.column = table2.column;

*Oracle에서는 공통컬럼을 지칭할 때 어떤 Table에서 나왔는지 지칭하지 않으면 "column ambiguously defined" 에러 발생!

 join테이블에 둘 다 있는 컬럼 명이라면 컴퓨터에서 어떤 컬럼을 말하는거지? 헷갈리게 되는 것

 

[Table명 별칭 지정을 통해 where 구문에 활용 가능함 => 별칭 사용 시 별칭으로 통일필요]

SELECT nick.column, name.column
FROM table1 nick, table 2 name #별칭을 지정
WHERE nick.column = name.column
and 검색조건 ; #검색조건을 추가할 수 있음

단, 여기서 WHERE table1.column = table2.column로 table이름을 사용하면 에러가 발생함

 

• Non-Equi Join : 부등 연산자를 이용한 조인(아래 예제는 Oracle의 scott계정 자료를 활용함)

select * 
from emp e, salgrade s
where e.sal between s.losal and s.hisal; #동등연산자가 아닌 non-equi join!

 

• Self - Join : 가상테이블을 이용해 자기 자신을 조인함

#employee_id를 기반으로 manager_id에서 누가 누구의 매니저인지 찾아야 할 때
select e.employee_id,e.last_name,m.last_name
from employees e, employees m
where e.employee_id = m.manager_id

 

• Cartesian Product : 조인조건을 생략하면서 발생하는 컬럼 수 * 컬럼 수 개수 만큼의 더미 조인!

 

• 여러개의 테이블을 조인할 때

select ename, dname, sal, grade
from emp e, dept d, salgrade s
where e.deptno = d.deptno
  and e.sal between s.losal and s.hisal;

** 어느 테이블간 연결이 되는지 목적을 정확히 하고 join을 실시해야 함

 

 

2.2 Outer join : inner join(교집합) + 그 외 부분 (누락된 부분까지 포함이 됨)

• (+) 연산자는 조인하는 테이블 중 한번만 사용할 수 있음
• (+) 연산자는 매칭되는 데이터가 없는 테이블에 붙여서 1개 이상의 null값이 생성됨

SELECT 테이블1.컬럼, 테이블2.컬럼

FROM 테이블1, 테이블2

WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼(+)

 

---

 

2 ) ANSI 조인  > Join조건은 FROM절에, 검색조건은 WHERE절에 

• Natural Join (Equi join과 기능이 동일함)

SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 NATURAL JOIN 테이블2
[WHERE 검색조건];

자동으로 테이블1과 2의 공통컬럼을 찾아서 join 함

**하지만, 공통 컬럼이 여러개이면 의도하지 않은 데이터(pair로 비교)가 출력되므로 주의해야 함

** 자동으로 공통컬럼을 인지하기 때문에, 공통컬럼에 alias 별칭을 사용 시 에러 발생! 

 

SELECT last_name,department_name, department_id
FROM employees e NATURAL JOIN departments d
WHERE department_id=90;

 

• Cross Join (Cartesian Product와 유사하게 동작)

SELECT last_name,department_name, e.department_id
FROM employees e CROSS JOIN departments d;

 

• Using 절

Natural join에 2개 이상의 공통컬럼 존재시 발생하는 오류를 Using (공통컬럼)을 통해 명시적 제시

SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 [INNER] JOIN 테이블2 USING(공통컬럼)
[WHERE 검색조건];

SELECT last_name,department_name, department_id
FROM employees e JOIN departments d USING(department_id);

--> 이 경우 역시 e.department_id 처럼 별칭 사용시 에러 발생

 

• On 절

Non-Equi조인이나 임의 조건으로 조인시에는 ON절 사용 필요

SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 [INNER] JOIN 테이블2 ON 조인조건
[WHERE 검색조건];

 

[3개의 테이블을 조인해야 하는 경우]

SELECT e.last_name 사원명, d.department_name 부서명,
 g.grade_level 등급
 
FROM employees e INNER JOIN departments d 
ON e.department_id = d.department_id

 INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

 

[On절과 Using절은 혼합해서 사용가능]

SELECT e.last_name 사원명, d.department_name 부서명,
 g.grade_level 등급
FROM employees e INNER JOIN departments d 
USING(department_id)

 INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

 

• LEFT /RIGHT / FULL OUTER JOIN

SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 LEFT|RIGHT|FULL OUTER JOIN 테이블2 
ON 조인조건 | USING(컬럼)               #ON절 USING절 모두 사용가능
[WHERE 검색조건];

select * from table 1 left outer join table2 -> left에 있는 table1은 일치여부에 관계 없이 모두 출력하라

select * from table1 right outer join table2 -> right에 있는 table2는 일치여부에 관계없이 모두 출력하라

select * from table 1 full outer join table2 -> table1과 table2를 모두 출력하라 (Oracle join에는 없는 기능)