[DB] (중요) Join

DB/Oracle

[DB] (중요) Join

09009 2023. 3. 25. 19:28

동등조인 (Equi Join)

조인 조건에서 “=”을 사용하여 값들이 정확하게 일치하는 경우에 사용하는 조인.

대부분 PK와 FK의 관계를 이용하여 조인 수행

emp 테이블 구조

select * from emp;

dept 테이블 구조

select * from dept;

종업원의 부서명을 결정하기 위하여 emp 테이블의 deptno와 dept 테이블의 deptno와 값을 비교한다.

emp 테이블과 dept 테이블 사이의 관계는 양쪽 테이블의 deptno 컬럼이 같아야 한다. 이들은 PK와 FK로

연결되어 있다.

select empno, ename, job, dname, loc from emp, dept where emp.deptno = dept.deptno;

deptno 컬럼은 emp 테이블과 dept 테이블에 모두 존재하기 때문에 select문에 작성할때 어느 테이블의 컬럼인지

작성해주어야 한다.

--select empno, ename, job, deptno, dname, loc from emp, dept where emp.deptno = dept.deptno; 에러 발생
select emp.empno, emp.ename, emp.job, emp.deptno, dept.dname, dept.loc from emp, dept where emp.deptno = dept.deptno;

컬럼에 별칭도 사용할 수 있다.

select e.empno, e.ename, e.job, e.deptno, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno; 
-- 한쪽 테이블에만 존재하는 칼럼은 테이블명 앞에 별칭 생략 가능
select empno, ename, job, e.deptno, dname, loc from emp e, dept d where e.deptno = d.deptno;

실습 예제

-- 사번, 이름, 업무, 입사일, 부서명
-- 이름, 업무, 입사일, 부서명, 근무지 81년 입사만
-- 이름, 업무, 입사일, 부서코드, 부서명, comm이 null
-- 이름, 업무, 부서코드, 부서명,근무지, 근무지가 DALLAS
-- 이름, 업무, 부서명, 근무지 업무가 manager이거나 clerk인 사람
-- 이름, 업무, 부서명, 급여, comm, 연봉(=(급여+comm)*12 comm이 null이면 0) 연봉 큰 순
-- 사번, 이름, 업무, 부서코드, 부서명, 급여 급여가 1000 ~ 3000 사이 부서명 순, 급여 큰 순
-- 사번, 이름, 급여, 커미션, 연봉(comm이 null이면 0), 부서코드, 부서명, 근무지, 부서코드 순, 급여작은 순
-- 사번, 이름, 업무, 급여, 부서명, 근무지 부서명이 reserach인 경우 급여 큰 순

-- 사번, 이름, 업무, 입사일, 부서명
select empno, ename, job, hiredate, dname from emp e inner join dept d on e.deptno = d.deptno;
-- select empno, ename, job, hiredate, dname from emp e, dept d where e.deptno = d.deptno;

-- 이름, 업무, 입사일, 부서명, 근무지 81년 입사만
select ename, job, hiredate, dname, loc from emp e join dept d 
on e.deptno = d.deptno where substr(hiredate,1,2) = 81;
-- select ename, job, hiredate, dname, loc from emp e, dept d where e.deptno = d.deptno and to_char(hiredate, 'yy') = 81;

-- 이름, 업무, 입사일, 부서코드, 부서명, comm이 null
select ename, job, hiredate, e.deptno, dname from emp e join dept d
on e.deptno = d.deptno where comm is null;
-- select ename, job, hiredate, e.deptno, dname from emp e, dept d where e.deptno = d.deptno and comm is null;

-- 이름, 업무, 부서코드, 부서명,근무지, 근무지가 DALLAS
select ename, job, e.deptno, dname, loc from emp e join dept d
on e.deptno = d.deptno where loc = 'DALLAS';
-- select ename, job, e.deptno, dname, loc from emp e, dept d where e.deptno = d.deptno and loc = 'DALLAS';

-- 이름, 업무, 부서명, 근무지 업무가 manager이거나 clerk인 사람
select ename, job, dname, loc from emp e join dept d
on e.deptno = d.deptno where lower(job) in ('manager','clerk');
s-- elect ename, job, dname, loc from emp e, dept d where e.deptno = d.deptno and lower(job) in ('manager','clerk');

-- 이름, 업무, 부서명, 급여, comm, 연봉(=(급여+comm)*12 comm이 null이면 0) 연봉 큰 순
select ename, job, dname, sal, comm, (sal+nvl(comm,0))*12 연봉
from emp e join dept d on e.deptno = d.deptno order by 연봉 desc;

-- 사번, 이름, 업무, 부서코드, 부서명, 급여  급여가 1000 ~ 3000 사이 부서명 순, 급여 큰 순
select empno, ename, job, e.deptno, dname, sal from emp e join dept d
on e.deptno = d.deptno where sal between 1000 and 3000 order by dname, sal desc;

-- 사번, 이름, 급여, 커미션, 연봉(comm이 null이면 0), 부서코드, 부서명, 근무지, 부서코드 순, 급여작은 순
select empno, ename, sal, (sal+nvl(comm,0))*12 연봉, e.deptno, dname, loc
from emp e join dept d on e.deptno = d.deptno order by e.deptno, sal;

-- 사번, 이름, 업무, 급여, 부서명, 근무지  부서명이 reserach인 경우 급여 큰 순
select empno, ename, job, sal, dname, loc from emp e join dept d
on e.deptno = d.deptno where dname = 'research' order by sal desc;

비동등조인 (Non-equi join)

: 테이블의 어떤 column도 조인할 테이블의 column에 일치하지 않을 때 사용한다.

salgrade 테이블 구조

급여등급 테이블(salgrade)에는 급여에 대한 등급을 아래와 같이 나누어 놓았다.

• 급여의 등급은 총 5등급으로 나누어져 있다.

1등급은 급여가 700부터 1200 사이이고, 2등급은 1201부터 1400 사이이고, 3등급은 1401부터 2000 사이이고,

4등급은 2001부터 3000사이이고, 5등급이면 3001부터 9999사이이다.

select * from salgrade;

급여등급을 5개로 나눈 salgrade 테이블에서 정보를 가져와 각 사원의 급여 등급을 지정하였다.

이를 위해 사원(emp) 테이블과 급여 등급(salgrade) 테이블을 조인하였다.

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

외부조인 (Outer join)

두 개 이상의 테이블에서 한쪽에만 존재하는 데이터도 출력하고 싶을 때 사용한다.

정상적으로 조인 조건을 만족하지 못하는 행들을 확인하고 싶을 때 outer join을 사용한다.

Outer join 연산자 "(+)"

• 조인조건에 만족되지 않는 값들을 구하고 싶을 때 해당 위치에 "(+)"를 작성한다.

• 외부조인이란 두 개 이상의 테이블을 조인할 경우, 테이블 중에서 기준이 되는 테이블의 데이터가

다른 테이블의 데이터와 일치되지 않는 경우에도 조인을 할 수 있도록 하기 위해 사용한다.

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

• 일반적인 조인

select distinct(e.deptno), d.deptno from emp e, dept d where e.deptno = d.deptno;

• 외부조인

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

이미지 출처: https://spidyweb.tistory.com/149

실습 예제

-- 사번, 이름, 업무, 부서명, 근무지 부서명순
-- 이름, 업무, 입사일, 급여, 부서코드, 부서명 부서코드 순 부서코드가 같으면 급여 큰 순
-- 사번, 이름, 업무, 급여, 급여등급 급여등급 순
-- 사번, 이름, 업무, 급여, 급여등급, 부서명
-- 이름, 업무, 입사일, 급여, 부서코드, 부서명, 근무지 직원없는 부서 포함
-- 이름, 업무, 입사일, 급여, 부서명, 근무지 급여가 1500~3000사이 부서명순
-- 이름, 업무, 급여, 연봉 (= (급여+comm) * 12 comm이 null이면 0), 급여등급, 부서명, 근무지
-- 이름, 업무, 입사일, 부서명, 근무지 81년에 입사한 사람 입사일 순
-- 이름, 업무, 입사일, 부서코드, 부서명, 근무지(직원없는 부서 포함) 근무지 순, 근무지가 같으면 급여 순

-- 사번, 이름, 업무, 부서명, 근무지 부서명순
select empno, ename, job, dname, loc from emp e join dept d on e.deptno = d.deptno order by dname;

-- 이름, 업무, 입사일, 급여, 부서코드, 부서명 부서코드 순  부서코드가 같으면 급여 큰 순
select ename, job, hiredate, sal, e.deptno, dname from emp e join dept d 
on e.deptno = d.deptno order by e.deptno, sal desc;

-- 사번, 이름, 업무, 급여, 급여등급  급여등급 순
select empno, ename, job, sal, grade from emp join salgrade
on sal between losal and hisal order by grade;

-- 사번, 이름, 업무, 급여, 급여등급, 부서명
select empno, ename, job, sal, grade, dname from emp e join dept d
on e.deptno = d.deptno join salgrade on e.sal between losal and hisal;

-- 이름, 업무, 입사일, 급여, 부서코드, 부서명, 근무지 직원없는 부서 포함
select ename, job, hiredate, sal, e.deptno, dname, loc
from emp e right outer join dept d on e.deptno = d.deptno;

-- 이름, 업무, 입사일, 급여, 부서명, 근무지 급여가 1500~3000사이 부서명순
select ename, job, hiredate, sal, dname, loc from emp e join dept d
on e.deptno = d.deptno where sal between 1500 and 3000 order by dname;

-- 이름, 업무, 급여, 연봉 (= (급여+comm) * 12 comm이 null이면 0), 급여등급, 부서명, 근무지
select ename, job, sal, (sal+nvl(comm,0))*12 연봉, grade, dname, loc
from emp e join dept d on e.deptno = d.deptno 
join salgrade on e.sal between losal and hisal;

-- 이름, 업무, 입사일, 부서명, 근무지 81년에 입사한 사람 입사일 순
select ename, job, hiredate, dname, loc from emp e join dept d
on e.deptno = d.deptno where to_char(hiredate, 'yy') = 81 order by hiredate;

-- 이름, 업무, 입사일, 부서코드, 부서명, 근무지(직원없는 부서 포함) 근무지 순, 근무지가 같으면 급여 순
select ename, job, hiredate, e.deptno, dname, loc 
from emp e right outer join dept d on e.deptno = d.deptno
order by loc, sal;

셀프 조인 (self join)

• 같은 테이블 사이의 조인

• 같은 테이블에 대해 두 개의 별칭을 작성하여, FROM 절에 두 개의 테이블을 사용한다.

• 조인은 두 개 이상의 서로 다른 테이블을 서로 연결하는 것뿐만 아니라, 하나의 테이블 내에서 조인을 해야

원하는 자료를 얻을 수 있는 경우도 존재한다.

emp 테이블 구조

-- 1
select w.ename 직원, m.ename 관리자 from emp w, emp m where w.mgr = m.empno; -- 사장은 출력되지 않는다.

--2 
select w.ename 직원, m.ename 관리자 from emp w join emp m on w.mgr = m.empno;

--1
select w.ename 직원, m.ename 관리자 from emp w, emp m where w.mgr = m.empno(+); -- 사장까지 출력

--2
select w.ename 직원, m.ename 관리자 from emp w left outer join emp m on w.mgr = m.empno;

실습 예제

--1. xxx의 상사는 xxx입니다.
--2. emp 테이블에서 manager가 'KING'인 사원들의 이름(ename)과 직급(job)을 출력하시오.
--3. 이름, 사번, 입사일, 부서명, 관리자명
--4. 이름, 업무, 입사일, 급여, 부서명, 관리자명, 관리자 없는 직원 포함
--5. 이름, 급여, 급여등급, 부서명, 관리자명, 관리자 없는 직원 포함
--6. 이름, 업무, 급여, 급여등급, 부서명, 근무지, 관리자명 업무가 salesman, manager인 경우
--7. 이름, 업무, 급여, 급여등급, 연봉(=(급여+comm)*12, comm이 null이면 0), 부서명, 관리자명
--8. 이름, 급여, 급여등급, 관리자명 (관리자 없는 직원 포함), 부서명순, 급여 큰 순

--1. xxx의 상사는 xxx입니다.
select w.ename || '의 상사는 ' || m.ename ||'입니다.' from emp w join emp m
on w.mgr = m.empno;

--2. emp 테이블에서 manager가 'KING'인 사원들의 이름(ename)과 직급(job)을 출력하시오.
select w.ename, w.job from emp w join emp m on w.mgr = m.empno where m.ename = 'KING';

--3. 이름, 사번, 입사일, 부서명, 관리자명
select w.ename 직원, w.empno, w.hiredate, dname, m.ename 관리자
from emp w join dept d on w.deptno = d.deptno join emp m on w.mgr = m.empno;


--4. 이름, 업무, 입사일, 급여, 부서명, 관리자명, 관리자 없는 직원 포함
select w.ename 직원, w.job, w.hiredate, w.sal, dname, m.ename 관리자
from emp w join dept d on w.deptno = d.deptno left outer join emp m on w.mgr = m.empno;

--5. 이름, 급여, 급여등급, 부서명, 관리자명, 관리자 없는 직원 포함
select w.ename 직원, w.sal, grade, dname, m.ename 관리자
from emp w join dept d on w.deptno = d.deptno join salgrade on w.sal between losal and hisal
left outer join emp m on w.mgr = m.empno;

--6. 이름, 업무, 급여, 급여등급, 부서명, 근무지, 관리자명 업무가 salesman, manager인 경우
select w.ename 직원, w.job, w.sal, grade, dname, loc, m.ename 관리자
from emp w join dept d on w.deptno = d.deptno join salgrade on w.sal between losal and hisal
left outer join emp m on w.mgr = m.empno where lower(w.job) in ('salesman','manager');

--7. 이름, 업무, 급여, 급여등급, 연봉(=(급여+comm)*12, comm이 null이면 0), 부서명, 관리자명
select w.ename 직원, w.job, w.sal, grade, (w.sal+nvl(w.comm,0))*12 연봉,
dname, m.ename 관리자 from emp w join dept d on w.deptno = d.deptno
join salgrade on w.sal between losal and hisal join emp m on w.mgr = m.empno;

--8. 이름, 급여, 급여등급, 관리자명 (관리자 없는 직원 포함), 부서명순, 급여 큰 순
select w.ename 직원, w.sal, grade, m.ename 관리자
from emp w join dept d on w.deptno = d.deptno join salgrade on w.sal between losal and hisal
left outer join emp m on w.mgr = m.empno order by dname, w.sal desc;

--1. EMP 테이블에서 모든 사원에 대한 이름,부서번호,부서명을 출력하는 SELECT 문장을 작성하여라.
--2. EMP 테이블에서 NEW YORK에서 근무하고 있는 사원에 대하여 이름,업무,급여,부서명을 출력하는 SELECT 문장을 작성하여라.
--3. EMP 테이블에서 보너스를 받는 사원에 대하여 이름,부서명,위치를 출력하는 SELECT 문장을 작성하여라.
--4. EMP 테이블에서 이름 중 L자가 있는 사원에 대하여 이름,업무,부서명,위치를 출력하는 SELECT 문장을 작성하여라.
--5. 사번, 사원명, 부서코드, 부서명을 검색하라. 사원명기준으로 오름차순 정렬
--6. 사번, 사원명, 급여, 부서명을 검색하라. 단 급여가 2000이상인 사원에 대하여 급여를 기준으로 내림차순으로 정렬하시오
--7. 사번, 사원명, 업무, 급여, 부서명을 검색하시오. 단 업무가 MANAGER이며 급여가 2500이상인 사원에 대하여 사번을 기준으로 오름차순으로 정렬하시오.
--8. 사번, 사원명, 업무, 급여, 등급을 검색하시오. 등급은 급여가 하한값과 상한값 범위에 포함되고 급여기준 내림차순으로 정렬하시오
--9. 사원테이블에서 사원명, 사원의 관리자명을 검색하시오
--10. 사원명, 관리자명, 관리자의 관리자명을 검색하시오
--11. 위의 결과에서 상위 관리자가 없는 모든 사원의 이름도 사원명에 출력되도록 수정하시오

--1. EMP 테이블에서 모든 사원에 대한 이름,부서번호,부서명을 출력하는 SELECT 문장을 작성하여라.
select ename, e.deptno, dname from emp e join dept d on e.deptno = d.deptno;

--2. EMP 테이블에서 NEW YORK에서 근무하고 있는 사원에 대하여 이름,업무,급여,부서명을 출력하는 SELECT 문장을 작성하여라.
select ename, job, sal, dname from emp e join dept d on e.deptno = d.deptno
where loc = 'NEW YORK';

--3. EMP 테이블에서 보너스를 받는 사원에 대하여 이름,부서명,위치를 출력하는 SELECT 문장을 작성하여라.
select ename, dname, loc from emp e join dept d on e.deptno = d.deptnowhere comm is not null;

--4. EMP 테이블에서 이름 중 L자가 있는 사원에 대하여 이름,업무,부서명,위치를 출력하는 SELECT 문장을 작성하여라.
select ename, job, dname, loc from emp e join dept d on e.deptno = d.deptno 
where ename like '%L%';

--5. 사번, 사원명, 부서코드, 부서명을 검색하라. 사원명기준으로 오름차순 정렬
select empno, ename, e.deptno, dname from emp e join dept d on e.deptno = d.deptno order by ename;

--6. 사번, 사원명, 급여, 부서명을 검색하라. 단 급여가 2000이상인 사원에 대하여 급여를 기준으로 내림차순으로 정렬하시오
select empno, ename, sal, dname from emp e join dept d on e.deptno = d.deptno
where sal >= 2000 order by sal desc;

--7. 사번, 사원명, 업무, 급여, 부서명을 검색하시오. 단 업무가 MANAGER이며 급여가 2500이상인 사원에 대하여 
-- 사번을 기준으로 오름차순으로 정렬하시오.
select empno, ename, job, sal, dname from emp e join dept d on e.deptno = d.deptno
where lower(job) = 'manager' and sal >= 2500 order by empno;

--8. 사번, 사원명, 업무, 급여, 등급을 검색하시오. 등급은 급여가 하한값과 상한값 범위에 포함되고 급여기준 내림차순으로 정렬하시오
select empno, ename, job, sal, grade from emp join salgrade
on sal between losal and hisal order by sal desc;

--9. 사원테이블에서 사원명, 사원의 관리자명을 검색하시오
select w.ename 직원, m.ename 관리자 from emp w join emp m on w.mgr = m.empno;

--10. 사원명, 관리자명, 관리자의 관리자명을 검색하시오
select w.ename 직원, m.ename 관리자, h.ename "관리자의 관리자"
from emp w join emp m on w.mgr = m.empno join emp h on m.mgr = h.empno;

--11. 위의 결과에서 상위 관리자가 없는 모든 사원의 이름도 사원명에 출력되도록 수정하시오
select w.ename 직원, m.ename 관리자, h.ename "관리자의 관리자"
from emp w left outer join emp m on w.mgr = m.empno 
left outer join emp h on m.mgr = h.empno;

5/6