날짜 : 2021-06-30
태그 : oracle SQL 조인 leftouter cross using natural
메모
용어설명
deptno : 사원번호
ename : 사원이름
mgr : 사원의관리자번호
job : 직업
sal : 연봉
hiredate : 고용일자
emp : 사원정보 테이블
dept : 부서정보 테이블
dname : 부서의이름
loc : 부서의위치
salgrade : 연봉 등급테이블
grade : 등급
losal : 연봉 하한
hisal : 연봉 상한5.outer 조인
outer조인은
조인조건에 만족하지않아도 결과를 보여준다.
oracle 에서는 outer조인 할 컬럼옆에 (+)를 붙여주고
ANSI 에서는 left outer 를 사용하게된다.
outer 조인 예제1
select e.ename 사원명 , [m.ename](http://m.ename/) 관리자명
from emp e, emp m
where e.mgr= [m.mgr(+);](http://m.mgr(+);/)
--self 와 outer조인의 혼합.
--또하나 주의할점은 조인하는 컬럼중에 하나에만 (+)를 사용할 수 있다는것임.
--null값이 존재하는곳에 (+)를 추가하면된다.
outer 조인 예제2
select e .ename 사원명 , m. ename 관리자명 , mm. ename 관리자의관리자명
from emp e , emp m , emp mm
where e.empno=[m.mgr(+)](http://m.mgr(+)/)
and [m.empno=mm.mgr(+);](http://m.empno=mm.mgr(+);/)6.ANSI 조인
6.1 Natural 조인
select ename, dname , deptno
from emp natural join dept;
-- nutural join이다. 공통컬럼을 알아서 매치해준다.
--oralce 조인처럼 where절에 사용하는게아니라 from 절에서사용하고 , 공통테이블을 따로 지정해서
--매치하지않아도 자동으로 찾아서 매치해준다. 단 2개이상의 공통컬럼이 있다면 오류가날수있다.
--주의할점은 공통컬럼을 사용할 때 별칭을 사용하면 안된다.
natural 조인 예제1
select ename , dname , deptno
from emp e natural join dept d
where deptno='30';
--부서번호가 30인 사원의 정보 내츄럴조인예제6.2 cross조인
cross 조인은 오라클조인의 cartesian product와 동일하게 생각하면되며. 잘 쓰이지않는다;
natural과 의 차이점은 공통컬럼을 사용할때 반드시 별칭을 지정해야한다.
select e.ename , d.dname , e.empno
from emp e cross join dept d; -- 이렇게 dept d << 별칭을 사용해야.
--cross join
6.3 using(컬럼) 절
select ename,dname,deptno
from emp e inner join dept d using(deptno);
-- using 절 사용, 문법은 위와같다. inner 는 생략가능 using괄호안에 공통컬럼을넣으면된다.
--두 개 이상의 공통컬럼이 있을시 엉뚱한결과가 출력된다(natural 사용), 그래서 나온개념이 using 이다.
-- 주의할 점은 natural 조인과 동일하게 공통컬럼에는 별칭을 사용할 수 없다.6.4 ON 절
on 절은 equi 나 non-equi 나 임의조건의 조인은 on 절을 사용해야한다. 기본문법은 아래와같다.
select empno , ename ,dname , loc
from emp
join dept
on emp.deptno = dept.deptno -- on 뒤에 조인할 공통컬럼을 매치시킨다.
and dept.deptno = 10; -- 그리고 deptno가 10인 조건 추가.
select empno , ename ,dname , loc , emp.deptno
from emp
join dept
on emp.deptno = dept.deptno
where dept.deptno = 10; -- 여기서는 and가 아니라 where를 사용해봤다.
--join ~ on 을 이용한 조인 , non-equi 조인이나 복잡한조건의 조인이 가능하다.
--둘다 같은결과.
select e.ename 사원명 , [m.ename](http://m.ename/) 관리자
from emp e join emp m
on e.mgr = [m.empno](http://m.empno/) ;
-- self 조인과 on절 사용.
select empno , ename , dname , sal , grade , e.deptno
from emp e
join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and s.hisal;
--join on 을이용한 equi와 non equi의 혼합.
-- 테이블이 총 3개가 사용되었다.
select empno , ename , dname ,sal
from emp e
join dept d
on e.deptno = d.deptno
and sal in(800);
-- in 으로 조건을 걸어준 join on 예제
select empno , ename , dname ,sal
from emp e
join dept d
on e.deptno = d.deptno
and sal =800;
-- in을 = 등호로 바꿔서도 사용가능.
select empno , ename , dname ,sal
from emp
join dept
on deptno =deptno
and sal in(800);
-- alias 를 사용안하면 오류가난다. 공통컬럼이 존재하는데 어느것 끼리 조인해야하는지 정해주지않았기때문
select empno , ename , dname ,sal
from emp e
join dept d
on e.deptno = d.deptno
where sal in(800);
-- and 자리에 where 사용도 가능 , on 자리에 using도 사용할수있다는걸 기억하자.
select e.ename 사원 , d.ename 관리자
from emp e
join emp d
on e.deptno=d.deptno;
-- self 조인 과 on6.5 Left outer / Right outer / Full outer 조인
오라클조인에서는 outer 가 (+)연산자를 이용하고 반드시 한쪽테이블에서만 사용할수 있었지만
ANSI 조인에서의 outer 조인은 + 대신에 left , right , full 키워드 이용하고. 한쪽 또는 양쪽테이블 모두지정가능
별로 중요한것은 아니기에 Left outer 조인만 해보고 지나간다.
select e.ename 사원명 , [m.ename](http://m.ename/) 관리자명
from emp e **left outer join emp** m
on e.mgr = [m.mgr](http://m.mgr/)
and e.empno=7902;
-- LEFT OUTER 조인 은 KING 까지 나온다. outer 조인이기때문에
-- (+) 연산자 대신에 LEFT / RIGHT / FULL 키워드를 이용한다.
-- 마찬가지로 조인조건을 명시할땐 on 절 또는 using 절을 사용할 수 있다.
select e.ename 사원명 , [m.ename](http://m.ename/) 관리자명
from emp e left outer join emp m
on e.mgr = [m.mgr](http://m.mgr/)
where e.empno=7902;
--and 를 where로 변경JOIN 문제 풀이
Join 개념을 확실히 배우기위해 여러 문제를 풀어보자.
문제마다 oracle 조인과 ansi 조인 모두구현
--실습문제 1
select e.empno , e.ename , e.deptno , d.dname
from emp e, dept d
where e.deptno=d.deptno
order by 2 ;
--ORACLE 조인
--조인을 이용하여 부서 이름 dname을 찾는 간단한문제
select e.empno , e.ename , e.deptno , d.dname
from emp e join dept d
on e.deptno = d.deptno
order by 2 ;
--ANSI 조인
select empno , ename , deptno , dname
from emp join dept
using(deptno) --using 사용시 alias 사용 못함.
order by 2 ;
--using 사용
--실습문제 2
select e.empno , e.ename , e.deptno , d.dname , e.sal
from emp e, dept d
where e.deptno=d.deptno
and e.sal>= 2000
order by e.sal desc ;
--sal 연봉이 2천 이상인 부서 이름과 기타정보를 출력하는내용 sal이 높은 순서대로 내림차순.
select e.empno , e.ename , e.deptno , d.dname , e.sal
from emp e join dept d
on e.deptno=d.deptno
and e.sal>= 2000
order by e.sal desc ;
--실습문제 3
select e.ename , e.empno , d.deptno
from emp e , dept d
where e.deptno = d.deptno
and e.job='MANAGER'
and sal >= 2500
order by 3 asc;
--OREALCE 업무 매니저이면서 연봉이 2500이상인 사원에 대해 사번기준 오름차순정렬 결과
select e.ename , e.empno , d.deptno
from emp e join dept d
on e.deptno = d.deptno
and e.job='MANAGER'
and sal >= 2500
order by d.deptno asc;
--ANSI
select e.ename , e.empno , deptno
from emp e join dept d
using(deptno)
where e.job='MANAGER'
and sal >= 2500
order by deptno asc;
--ANSI , using으로 바꿨을때 , and 도 where로 변경해도 마찬가지 결과가나온다.
--실습문제 4
select e.empno , e.ename , e.sal , s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal
and s.grade =4
order by e.sal desc;
--e sal이 losal 과 hisal 의 범위안에 들어가면서 연봉등급이 4인 사원의정보를 sal 기준 내림차순하라
select e.empno , e.ename , e.sal , s.grade
from emp e join salgrade s
on e.sal between s.losal and s.hisal
and s.grade =4
order by e.sal desc;
--ANSI
--실습문제 5
select e.empno, ename ,d.dname , e.sal , s.grade
from emp e, dept d , salgrade s
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal
order by s.grade desc;
--연봉이 범위안에 있는 직원들의 부서이름 및 기타정보
select e.empno, ename ,d.dname , e.sal , s.grade
from emp e
join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and s.hisal
order by s.grade desc;
--ANSI
--실습문제6
select e.ename , [m.ename](http://m.ename/)
from emp e, emp m
where e.empno= [m.mgr;](http://m.mgr;/)
--사원테이블에서 사원명과 해당사원의 관리자명을검색 slef 조인
select e.ename , [m.ename](http://m.ename/)
from emp e join emp m on e.empno=[m.mgr;](http://m.mgr;/)
--ANSI
--실습문제 7
select e.ename 사원이름 , [m.ename](http://m.ename/) 사원의관리자 ,m[m.ename](http://m.ename/) 관리자의관리자
from emp e, emp m, emp mm
where e.mgr=[m.empno](http://m.empno/)
and [m.mgr=mm.empno;](http://m.mgr=mm.empno;/)
--slef 3개 조인 . 관리자의 관리자 정보를 출력하는 문제.
select e.ename 사원이름 , [m.ename](http://m.ename/) 사원의관리자 ,m[m.ename](http://m.ename/) 관리자의관리자
from emp e join emp m
on e.mgr=[m.empno](http://m.empno/)
join emp mm
on [m.mgr=mm.empno;](http://m.mgr=mm.empno;/)
--실습문제8
select e.ename 사원이름 , [m.ename](http://m.ename/) 사원의관리자 ,m[m.ename](http://m.ename/) 관리자의관리자
from emp e , emp m , emp mm
where e.mgr=[m.empno(+)](http://m.empno(+)/)
and [m.mgr=mm.empno(+);](http://m.mgr=mm.empno(+);/)
-- outer 조인을이용해서 모든 사원의 관리자 정보 보기
select e.ename 사원이름 , [m.ename](http://m.ename/) 사원의관리자 ,m[m.ename](http://m.ename/) 관리자의관리자
from emp e left outer join emp m
on e.mgr=[m.empno](http://m.empno/)
left outer join emp mm
on [m.mgr=mm.empno;](http://m.mgr=mm.empno;/)
--ANSI
--실습문제 9
--20번부서의 이름과 그부서에 근무하는 사원의이름을출력하시오
select dname 부서이름, ename 근무하는사원이름
from emp e , dept d
where e.deptno=d.deptno
and e.deptno=20;
select dname 부서이름, ename 근무하는사원이름
from emp e join dept d
on e.deptno=d.deptno
where e.deptno=20;
--and e.deptno=20; 마지막 조건문은 and 로도 변경가능.
--실습문제 10
--커미션을 받는 사원의 이름 커미션, 부서이름을출력하시오
select e.ename , d.dname
from emp e, dept d
where e.deptno=d.deptno
and comm is not null
and comm !=0 ; --! 는 다르다 라는 의미이다. 같지않다라고 이해하면됨.
select e.ename , d.dname
from emp e join dept d
on e.deptno=d.deptno
where comm is not null
and comm !=0 ;
--ANSI
select e.ename , d.dname
from emp e join dept d
using(deptno)
where comm is not null
and comm !=0 ;
-- using 도 사용해보자.
--실습문제 11
--이름에 'A'가 들어가는 사원들의 이름과 부서명출력
select e.ename , d.dname
from emp e, dept d
where e.deptno=d.deptno
and e.ename like '%A%';
select e.ename , d.dname
from emp e join dept d
on e.deptno=d.deptno
where e.ename like '%A%';
--실습문제 12 DALLAS에 근무하는 사원중 급여 1500 이상인 사원의이름 ,급여,입사일,보너스 출력하시오
select e.ename , e.sal , e.hiredate , e.comm
from emp e, dept d
where e.deptno=d.deptno
and e.sal >=1500
and d.loc = 'DALLAS';
select e.ename , e.sal , e.hiredate , e.comm
from emp e join dept d
on e.deptno=d.deptno
where e.sal >=1500
and d.loc = 'DALLAS';
-- 실습13 자신의관리자보다 sal 을 많이받는 사원의 이름과 연봉을출력
select e.ename , e.sal
from emp e, emp m
where e.mgr = [m.empno](http://m.empno/)
and e.sal > [m.sal](http://m.sal/) ;
select e.ename , e.sal
from emp e join emp m
on e.mgr = [m.empno](http://m.empno/)
where e.sal > [m.sal](http://m.sal/) ;
-- ★실습문제 14 직원중 현재시간 기준 근무개월수가 30년(12*30) 보다 많은사람의 이름,급여,입사일,부서명출력
select e.ename , e.sal , e.hiredate , d.dname
from emp e, dept d
where e.deptno = d.deptno
and **months_between(sysdate,hiredate)**/12 > 30 ;
--현재시간과 고용일의 차이= 근무개월수 를 12로 나누면 근무 연 수가 나온다.
select e.ename , e.sal , e.hiredate , d.dname
from emp e join dept d
on e.deptno = d.deptno
where months_between(sysdate,hiredate)/12 > 30 ;
--실습문제 15
--각부서별로 1982년 이전에 입사한 직원들의 부서명을 출력하시오.
select d.dname , count(dname)
from emp e , dept d
where e.deptno = d.deptno
and to_char(hiredate,'YYYY') < '1982'
group by d.dname ;
select d.dname 부서명, count(e.empno) 인원수
from emp e join dept d
on e.deptno = d.deptno
where to_char(e.hiredate,'YYYY') < '1982'
group by d.dname ;
생각 (질문)
출처 (문헌)
학원에서 준 바인딩 책임