셀프 조인

정의

• 실제로는 하나의 테이블인데 칼럼의 값이 그 테이블의 다른 칼럼을 참조할 때 사용하는 조인이다.
• A 테이블의 a 칼럼이 A테이블의 b 칼럼을 참조할 때, 셀프 조인을 한다.

예시

• emp 테이블의 mgr 은 emp 테이블의 eno를 참조하고 있다. 만약 직원의 이름과 관리자의 이름을 출력하고자 한다면 emp 테이블을 하는 "직원"이라고 애칭을 주고 하나는 "관리자"라고 애칭을 주어 조인할 수 있다.
• emp e(직원), emp m(관리자)

select e.ename, m.ename
from emp e, emp m
where e.mgr = m.eno;

 

연습

• 개발팀에 근무하는 모든 직원들의 사원번호, 사원명 관리자명, 부서번호, 부서명을 출력하시오

select e.eno, e.ename empolyee, m.ename manager, d.dno, dname
from emp e,emp m, dept d
where d.dno = e.dno and
e.mgr = m.eno and
dname like '%개발%';

• 개발팀에 근무하는 직원들 중에 관리자보다 입사일이 빠른 직원의 사원번호, 사원명, 관리자명, 입사일, 관리자의 입사일을 출력한다.

select e.eno, e.ename, m.ename, e.hiredate, m.hiredate
from emp e, emp m
where e.mgr = m.eno and
e.hiredate < m.hiredate;

• '판교'나 '종각'에 근무하고 '사원'이거나 '대리'인 직원들 중에 입사일이 관리자보다 빠르거나 급여가 관리자보다 많은 직원의 사원번호,사원명, 관리자명, 입사일, 관리자의 입사일, 급여, 관리자의 급여를 출력하시오.
  단, 입사일 순으로 출력하고 동일할 때 급여가 높은 순으로 출력한다.

select e.eno, e.ename, m.ename, e.hiredate, m.hiredate, e.salary, m.salary
from emp e, emp m, dept d
where e.mgr = m.eno and d.dno = e.dno and
dloc in ('판교', '종각') and
e.job in ('사원', '대리') and
(e.hiredate < m.hiredate or 
e.salary > m.salary)
order by e.hiredate, e.salary desc;

• '박성빈'의 부하 직원들의 사원번호, 사원명, 입사일, 급여를 출력. 단, 입사일 순으로 출력한다.

select e.eno, e.ename, e.hiredate, e.salary
from emp e, emp m
where e.mgr = m.eno and
m.ename = '박성빈'
order by hiredate;

• 고격번호별로 주문한 건수 출력

select custid, count(*) from orders group by custid;

• 고객 이름별로 주문한 건수 출력

select name, count(*) 
from orders o, customer c
where o.custid = c.custid
group by name;

 

명령어 각 실행 순서

select 컬럼들		-5
from 테이블 이름들 -1
where 조건식		-2
group bu			-3
having				-4
order by 			-6

 

조인의 종류

• 일반적인 조인(inner join)
  • 2개의 테이블에 모두 조건식을 만족하는 레코드를 검색
  • 양쪽 테이블에 모두 조건을 만족하는 레코드만 조회한다.
  • 공통으로 모든 조건에 맞는 레코드만 조회하는것
  • 여태까지 한 것
  • orders 테이블을 조건에 따라 조회해보면 주문이 없는 고객은 출력되지 않는다.
• 외부조인 (Outer join) : 조건에 만족하지 않더라도 조회하는것(0)
  • 두 개의 테이블 중에 조건을 만족하지 않는 레코드 또한 조회하고자 할 때 사용한다.
  • 두 개의 테이블이에 왼쪽이나 오른쪽에 만족하지 않더라도 레코드를 검색한다.
  • left outer join:  조건을 만족하지 않더라도 왼쪽의 테이블은 무조건 검색
  • right outer join: 조건을 만족하지 않더라도 오른쪽의 테이블은 무조건 검색 
  • 0개를 가지고 있더라도 나와야하는 항목이 있는 테이블쪽 방향(left | right)을 선택해준다.

Outer join

• 형식 

select 컬럼이름(들)
from 테이블1 (right | left) outer join 테이블2
on 조건식

• 고객 이름별로 주문한 건수 출력

//주문한 고객 별로 주문한 건수
//괄호 안에 orders 테이블에 있는 컬럼명을 적어주면 된다.
select name, count(o.custid) 
from orders o right outer join customer c
on o.custid = c.custid
group by name;

 

예제

• 관리자 이름별로 부하 직원의 수를 입력하세요. 단, 부하직원이 없는 직원의 이름도 출력한다.

select m.ename, count(e.ename) 
from emp e right outer join emp m
on e.mgr = m.eno 
group by m.ename;

• 부서명별로 총 직원 수를 출력한다. 단, 소속된 직원이 없는 부서명도 출력한다.

select dname, count(ename) 
from emp e right outer join dept d
on e.dno = d.dno
group by d.dname;

• 출판사 별로 총 주문 건수, 총 주문 금액을 출력한다. 주문 내역이 없는 출판사도 출력한다.

select publisher, count(custid), sum(saleprice)
from book b left outer join orders o
on b.bookid = o.bookid
group by publisher;

 

nvl() 

• count 함수는 조건을 만족하는 레코드가 한개도 없으면 0, count 이외의 집계함수는 조건을 만족하는 레코드가 없으면 null
• sum 의 null 을 다른 값으로 표현시켜주는 오라클의 함수
• null인 값을 다른 값으로 채우고 싶을 때 nvl 함수를 이용한다. 

nvl(컬럼, 값)

select publisher, count(custid) count, nvl(sum(saleprice), 0) sum
from book b left outer join orders o
on b.bookid = o.bookid
group by publisher;