[SQL] JOIN (LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, INNER JOIN)

join : 두개 이상의 여러 테이블에서 원하는 컬럼값을 검색하기 위한 기능

 

오라클 조인문법 

1. equi join

1) 정의 

equi join(동등조인) : 조인조건에 = 연산자를 사용하여 결과가 참인 행만 검색함

 

2) 문제 

> 사원테이블과 부서 테이블을 조인해서 이름과 부서위치(LOC)를 출력하세요. 

select e.ename, d.loc, e.deptno
	from emp e, dept d
	where e.deptno = e.deptno;

// 컬럼명 앞에 테이블 명을 붙여줘야 더 성능이 좋아진다. 

 

> 직업이 SALESMAN인 사원들의 이름과 직업과 부서위치를 출력하세요. 

select e.ename, e.job, d.loc
	from emp e, dept d
	where e.deptno = d.deptno and e.job = 'SALESMAN';

 

> DALLAS에서 근무하는 사원들의 이름과 월급과 부서위치를 출력하세요. 

select e.ename, e.sal, d.loc
	from emp e, dept d
	where e.deptno = d.deptno and d.loc = 'DALLAS';

 

 

 

2. non equi join 

1) 정의

non equi join(비동등조인) : 조인조건에 = 연산자를 제외한 연산자를 사용해 결과가 참인 행만 검색함

 

2) 문제 

> 사원테이블과 급여테이블을 조인해서 이름, 월급, 월급에 대한 등급을 출력하세요. 

select e.ename, e.sal, s.grade
	from emp e, salgrade s
	where e.sal between s.losal and s.hisal --서로 공통된 컬럼이 존재하지 않는 경우

 

> 급여등급이 4등급인 사원들의 이름과 월급을 출력할 때 월급이 높은 사원부터 출력하세요. 

select e.ename, e.sal
	from emp e, salgrade s
	where e.sal between s.losal and s.hisal --조인조건
	and s.grade = 4 --검색조건
	order by e.sal desc

 

2-1. outer join 

1) 정의 

outer join(외부조인) : 교차조인된 검색결과에서 조인조건이 참인 행만 검색하는 것이 아니라 조건에 누락된 행도 검색함

- 조인 조건의 테이블에 (+)을 사용해 조인조건에 누락된 행의 컬럼값을 NULL로 검색되도록 함 

 

2) 문제 

> 이름과 부서위치를 출력하는데 다음과 같이 BOSTON도 출력하세요. 

- equi join이었을 경우 

select e.name, d.loc
	from emp e, dept d
	where e.deptno = d. deptno;

 

- outer join이었을 경우

select e.ename, d.loc
	from emp e, dept d
	where e.deptno (+) = d.deptno;

 

> 사원 테이블 전체에 이름과 부서위치를 출력하는데 JACK도 출력하세요. 

select e.ename, d.loc
	from emp e, dept d
	where e.deptno = d.deptno (+);

 

 

ANSI 조인문법 

1. on절

1) 문제

> 이름과 월급, 직업, 부서위치를 출력하는데 직업이 'SALESMAN'인 경우를 출력하세요. 

- 기존 조인의 경우 

select e.ename, e.sal, e.job, d.loc
	from emp e, dept d
	where e.deptno = d.deptno;

 

- on절을 사용한 조인의 경우 

select e.ename, e.sal, e.job, d.loc
	from emp e join dept d
	on (e.depno = d.deptno) --조인조건
	where e.job = 'SALESMAN'; --검색조건

 

> 월급이 1000에서 3000사이인 사원들의 이름과 월급과 부서위치를 on절을 사용한 조인문법으로 출력하세요. 

select e.ename, e.sal, d.loc
	from emp e join dept d
	on (e.deptno = d.deptno)
	where e.sal between 1000 and 3000;

 

> 부서가 30이고 급여 1500 이상인 사원의 이름, 급여, 부서명 , 부서번호를 출력하세요. 

select e.ename, e.sal, d.dname, d,deptno
	from emp e join dept d
	on e.depno = d.deptno
	where d.deptno = 30
	and e.sal >= 1500;

 

> '여자' 직원들의 사원이름, 급여, 은행명, 계좌번호를 출력하세요.(은행명은 코드명으로 출력)

select e.ename, s.sal, s.bank, s.act_no
	from salgrade s join emp e
	on s.empno = e.empno
	where sex = 'W';

 

> 부서위치가 '시카고'가 아닌 부서위치, 부서명, 사원번호, 사원이름을 출력하세요. 

select d.loc, d.dname, e.empno, e.ename
	from dept d join emp e
	on d.deptno = e. deptno
	where loc != '시카고';

 

 

OUTER JOIN

: 조인조건을 명시하여 조인조건이 맞는 행과 조인조건이 맞지 않아 누락된 행도 결합하여 검색하는 조인

*형식 

SELECT 검색대상 FROM 테이블명1 {LEFT | RIGHT | FULL} [OUTER] JOIN 테이블명2 ON 조인조건

 

2. left/right outer join 

1) 정의

left outer join : 왼쪽 방향에 선언된 테이블의 모든 행을 검색하되 오른쪽 방향의 선언된 테이블은 조인조건이 참인 행과 결합하여 검색하고 조인조건에 참인 행이 없는 경우 NULL로 결합하여 검색

right outer join :  오른쪽 방향에 선언된 테이블의 모든 행을 검색하되 왼쪽 방향의 선언된 테이블은 조인조건이 참인 행과 결합하여 검색하고 조인조건에 참인 행이 없는 경우 NULL로 결합하여 검색 

 

2) 문제 

> 이름, 직업, 월급, 부서위치를 outer join으로 출력하세요. 

select e.name, e.job, e.sal, d.loc
	from emp e right outer join dept d
	on e.deptno = d.deptno;

 

> 다음 데이터를 사원테이블에 입력하고 이름과 직업, 월급과 부서위치를 출력하는데 사원테이블에 JACK도 출력될 수 있도록 하세요. 

insert into emp (empno, ename, sal, job, deptno)
	values (8282, 'JACK', 3000, 'ANALYST', 50);
commit;

 

-anssi join인 경우 

select e.ename, e.job, e.sal, d.loc
	from emp e left outer join dept d
	on e.deptno = d.deptno;

 

-oracle join인 경우

select e.ename, e.job, e.sal, d.loc
	from emp e, dept d 
	where e.deptno = d.deptno (+):

 

 

3. full outer join 

1) 정의

양쪽 방향에 선언된 테이블의 모든 행을 검색하되, 조인조건이 참인 행은 결합하여 검색하고  조인조건이 참인 행이 없는 경우 NULL로 결합하여 검색 

2) 문제

> 직업이 ANALYST거나 부서위치가 BOSTON인 사원들의 이름과 직업, 월급과 부서위치를 출력하는데 full outer joun을 사용하여 출력하세요. 

select e.ename, e.job, e.sal, d.loc
	from emp e full outer join dept d
	on e.deptno = d.deptno
	where e.job = 'ANALYST' or d.loc='BOSTON';

 

 

4. inner join 

1) 정의 

조인조건을 명시하여 조인조건이 맞는 경우 결합하여 검색하는 조인 

select 검색대상,... 
from 테이블명1 inner join 테이블명2 on 조인조건

 

2) 문제 

> emp 테이블과 salgrade 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 급여등급을 검색해 출력하세요. 

select empno, ename, sal, grade
	from emp join salgrade 
	on sal between losal and hisal;

 

> emp 테이블과 dept 테이블에서 SALES 부서에 근무하는 사원의 사원번호, 사원이름, 급여, 부서이름 부서위치 검색해 출력하세요.

select empno, enme, sal, dname, loc
	from emp join depn
	on emp.deptno = dept.deptno 
	where sname='SALES';