21강 오라클 SQL Outer Join

-- 1. Outer Join
-- 2. ANSI Join

select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno(+);
-- KING은 이 회사의 사장(PRESIDENT)으로 매니저가 존재하지 않으므로
-- MGR 컬럼 값이 NULL 입니다. 사원 번호(EMPNO)가 NULL인 사원은
-- 없으므로 조인 조건에 만족하지 않아 KING은 Seif Join의 결과에서 배제되었습니다.

-- 조인 조건에 만족하지 못하였더라도 해당 로우를 나타내고 싶을 때에 사용하는 것이 외부 조인(Outer Join)입니다.
-- 외부 조인은 NULL 값이기에 배제된 행을 결과에 포함시킬 수 있으며,"(+)" 기호를 조인 조건에서 정보가 부족한 컬럼 뒤에
-- 덧붙입니다.

-- 사원 번호(EMPNO)가 NULL인 사원은 없으므로 manger.empro 뒤에 "(+)" 기호를 덧붙입니다.
select employee.ename ||'의 매니저는'||manager.ename||'입니다.'
from emp employee, emp manager
where employee.mgr=manager.empno(+);

-- <탄탄히 다지기>
-- 6. 사원 테이블과 부서 테이블을 조인하여 사원이름과 부서번호와 부서명을 출력하도록 하라.
-- 부서 테이블의 40번 부서와 조인할 사원 테이블의 부서번호가 없지만, 아래 그림과 같이 40번
-- 부서의 부서 이름도 출력되도록 쿼리문을 작성 해보시오.
select e.ename, d.deptno, d.dname
from emp e, dept d
where e.deptno(+) = d.deptno;
-- 없는 내용도 포함(+)하라.

-- ANSI Join
-- 7.1 ANSI Cross Join
select * from emp cross join dept;

-- 7.2 ANSI Inner Join
-- ANSI 조인은 FROM 다음에 INNER JOIN 이란 단어를 사용하여 조인할 테이블 이름을 명시하고
-- ON 절을 사용하여 조인 조건을 명시하여 다음과 같이 작성합니다.
-- select * from table1 inner join table2 on table1.column1 = table2.column2;


-- ANSI 조인에서는 조인 정보를 ON절에 기술하여 조인 조건을 명확하게 지정하고, 다른 조건에
-- 대해서는 WHERE 구문에서 지정하면 됩니다.
-- Equi 조인
select ename, dname 
from emp, dept
where emp.deptno = dept.deptno and ename='SCOTT';

-- ANSI 조인
select ename, dname 
from emp 
inner join dept 
on emp.deptno=dept.deptno -- using으로 간편하게 가능 아래 참조
where ename='SCOTT';

-- USING을 이용한 조인 조건 지정하기
-- 두 테이블에 각각 조인을 정의한 컬럼의 이름이 동일하다면 USING
-- 절에서 조인할 컬럼을 지정하여 구문을 더 간단하게 표현할 수 있습니다.
-- select * from table1 join table2 using(공통컬럼)

-- EMP와 DEPT에 DEPTNO 라는 같은 이름의 컬럼이 있기 때문에 다음과 같이 간간하게 조인문을 기술할 수 있습니다.
select emp.ename, dept.dname
from emp inner join dept
using(deptno);

-- NATURAL Join
-- 두 테이블에 각각 조인을 정의한 컬럼의 이름이 동일하다면 USING
-- 절에서 조인할 컬럼을 지정하여 구문을 더 간단하게 표현할 수 있습니다.
-- select * from table1 NATURAL JOIN table2;

-- EMP와 DEPT에 DEPTNO라는 같은 이름의 컬럼이 있기 때문에 다음과 같이
-- 간단하게 조인문을 기술할 수 있습니다.
select emp.ename, dept.dname
from emp
natural join dept;

-- Outer Join은 LEFT Outer Join, RIGHT Outer Join 그리고
-- FULL Outer Join 세 가지 타입의 조인을 제공합니다.
-- select * from table1 [LEFT | RIGHT | FULL] Outer Join table2;

-- Outer Join은 이미 설명했듯이 어느 한쪽 테이블에는 해당하는 데이터가 존재하는데
-- 다른쪽 테이블에는 데이터가 존재하지 않을 경우 그 데이터가 출력되지 않는 문제점을
-- 해결하기 위해 사용하는 조인 기법입니다.

-- <실습하기> 다양한 Outer Join
-- 1. DEPT 테이블과 닮은 DEPT01 테이블을 만들어 보겠습니다. 혹시 DEPT01
-- 테이블이 존재한다면 DEPT01 테이블이 생성되지 않으므로 DROP 명령어로 삭제 후
-- 생성하도록 합시다.
DROP table dept01;

create table dept02(deptno number(2), dname varchar2(14));

insert into dept01 values(10, 'ACCOUNTING');
insert into dept01 values(20, 'RESEARCH');

select * from dept01;

create table dept02(deptno number(2), dname varchar2(14));

insert into dept02 values(10, 'ACCOUNTING');
insert into dept02 values(30, 'SALES');

select * from dept02;

-- DEPT01 테이블에 20번 부서와 조인할 부서번호가 DEPT02dpsms
-- 없지만, 20번 부서도 출력되도록 하기 위해서 DEPT01 테이블이
-- 왼쪽에 존재하기에 LEFT OUTER JOIN을 사용합시다.
select * from dept01 
left outer join dept02
on dept01.deptno = dept02.deptno;

-- DEPT02 테이블에만 있는 30번 부서까지 출력되도록 하기 위해서
-- RIGHT OUTER JOIN을 사용합시다.
select * from dept01
right outer join dept02
using(deptno);

-- FULL Outer Join은 Left outer join, Right outer join을 합한 형태
select * from dept01 full outer join dept02 using(deptno);

select e1.ename, e2.ename 
from emp e1 
left outer join emp e2 
on e1.mgr = e2.empno;