1020-Join문

 

<Join>

 

1) cross join (안쓴다) = (ansi) cross join

- 공통되는 컬럼에 의한 결합이 발생하지 않는 경우 ( n * m = result(record))

 

select * from emp, dept;
(4 * 14 = 56) 단순결합

-ansi
select * from emp cross join dept;

 

2) equi join = (ansi) inner join 

- 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결 (동일 컬럼 기준 조인)

select * 
from a,b
where a.id = b.id;

-ansi innner join 1
select * 
from a inner join b
on a.id= b.id;

-ansi innner join 2
select *
from a inner join b 
using (id); --using은 별칭 지정 불가

- 테이블 별칭 설정

select e.ename, d.dname
from emp e ,dept d
where e.deptno = d.deptno
and e.ename = 'SCOTT';

 

 

2-2)join문으로 

select * from a,b where a.aid = b.bid;

select * from a join b on a.aid = b.bid;

select a.*, b.CarId,b.BuyingDate,b.BuyingPrice,c.CarId,c.CarName,c.Maker 
from Customer a, CarCustomer b,Car c  
where a.CustomerId=b.CustomerId and b.CarId = c.CarId 
order by BuyingDate;

select a.*, b.CarId,b.BuyingDate,b.BuyingPrice,c.CarId,c.CarName,c.Maker 
from customer a 
join  carcustomer b on a.cutomerid = b.customerid
join car c on b.carid = c.carid

 

3) non equi join

- 동일 컬럼 없이 다른 조건을 사용하여 join

- 연산자활용
select ename, sal, salgrade
where sal >= lasal and sal <= hisal;

-between a and be
select name, sal, salgrade
where sal between losal and hisal;

 

4) self join

- 하나의 테이블내에서 자기 자신과 join

select employee.ename, employee.mgr, manager.ename
from emp employee, emp manager
where employee.mgr = manager.empno;

 

 

5) Outer join  = (ansi) outer join 

- 조인조건에 만족하지 못해 누락되는 결과를 출력

- left outer join : 왼쪽 테이블의 해당되지 않는 데이터도 출력

- right outer join : 오른쪽 테이블의 해당되지 않는 데이터도 출력

- full outer join : 양쪽 모두 해당되지 않는 데이터 출력 --ansi에만 해당

select * 
from emp e, emp m
where e.mgr = m.empno(+);

select * 
from emp e, emp m
where e.mgr(+) = m.empno;

--불가
select * 
from emp e, emp m
where e.mgr(+) = m.empnoselect(+);

-ansi

select ename,sal,d.deptno,dname
from emp e right outer join dept d
on e.deptno = d.deptno;

select e.empno,e.ename,e.mgr,m.ename
from emp e left outer join emp m 
on e.mgr = m.empno;

select e.empno,e.ename,e.mgr,m.ename
from emp e full outer join emp m 
on e.mgr = m.empno;

 

natural join 

- 두 테이블에서 공통되는 컬럼 항목을 찾아 공통되어지는 결과 출력

select ename, dname from emp natural join dept;

 

<Join Update>

update CarCustomer a 
join Customer b 
on a.CustomerId = b.CustomerId 
set a.BuyingPrice=200 
where a.CustomerName = 'suji';

 

<Join Delete>

delete b 
from Customer a 
join CarCustomer b 
on a.CustomerId = b.CustomerId 
where a.CustomerName = 'suji';

 

DELETE [삭제할 테이블] 

               FROM [테이블] A INNER JOIN [테이블] B ON A.[컬럼명] = B.[컬럼명]

               (WHERE 절) 

 

 

 

--================================================ 1020 JOIN 

--2개 이상의 테이블에서 데이터를 조회
--FROM 절에서 두개 이상의 테이블을 작성한다
--WHERE 에 조인 조건을 작성한다 
--cross join (where절 없이 조인 잘 안씀)
--equi join (a와 b테이블에서 공통컬럼을 찾는다) (WHERE  등가연산자 : =)
--non equi join (WHERE 범위 연산자 : AND, OR)
--self join(WHERE 하나의 테이블을 사용한다)
--out join (WHERE에 누락되는 데이터를 같이 조회하기위해 : (+)

--equi join
--가져올 컬럼이 여러 테이블로 분산되어있음 
SELECT EMP.ENAME,EMP.JOB,EMP.DEPTNO,DEPT.DNAME,DEPT.LOC
FROM EMP,DEPT --가져올 테이블을 , 로 구분하여 작성
WHERE EMP.DEPTNO = DEPT.DEPTNO; --공통 컬럼을 비교해서 같은 정보만 가져온다 


--SELECT 문에 어느테이블의 컬럼인지 . 으로 명시 해주어야 한다 (양쪽테이블에 모두 존재하는 컬럼)
SELECT ENAME,JOB, EMP.DEPTNO,DNAME,LOC
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

--별칭
SELECT ENAME,JOB, E.DEPTNO,DNAME,LOC
FROM EMP E,DEPT D --테이블에 별칭부여 (SELECT 문의 테이블 명도 별칭으로 바꾸어줘야 한다)
WHERE E.DEPTNO = D.DEPTNO
AND SAL >= 3000; --조건추가 : 논리연산자 사용. JOIN 된 결과를 가지고 추가된 조건이 만족하는

--non equi join(EQUI 반대 개념이X) 비등가 연산
--급여의 등급 조회
--GRADE - SALGRADE 테이블, 나머진 EMP
SELECT ENAME,SAL,GRADE,LOSAL,HISAL --겹치는 테이블 아니기때문에 명시 X
FROM EMP E,SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
--WHERE E.SAL >= S.LOSAL AND E.SAL <= S.HISAL


--사번, 이름, 급여, 부서번호, 부서명, 급여등급 
--emp              dept           salgrade 에 
SELECT EMPNO,ENAME, SAL, D.DEPTNO, DNAME,GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO= D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL;





--SELF JOIN( 내가 내 껄 참조해서 비교한다)
--하나의 테이블에 데이터가 있긴 하나 하나의 SELECT 문으로 조회가 불가능할때
SELECT ENAME,MGR
FROM EMP;

--반드시 별칭 부여
SELECT E.EMPNO,E.ENAME,E.MGR,M.ENAME
FROM EMP E, EMP M  --물리적 하나의 테이블을 논리적으로 2개로 나눔
WHERE E.MGR = M.EMPNO; --SELFJOIND

--같은 EMP테이블에서20번에 근무하는 사원 : SELF JOIN 사용
SELECT ENAME,DEPTNO
FROM EMP
WHERE DEPTNO = 20;
--SCOTT과 같은 부서에 근무하는 사원(부서번호 모름)
SELECT WORK.ENAME,FRIEND.ENAME
FROM EMP WORK,EMP FRIEND
WHERE WORK.DEPTNO = FRIEND.DEPTNO
AND WORK.ENAME = 'SCOTT'
AND FRIEND.ENAME != 'SCOTT';




--외부조인 outerjoin
--등가시 누락되는 데이터를 같이 조회하기 위해서 사용 
--EX)NULL 값이 있을 때 동일하게 NULL을 가진 값이 없기 때문에 누락될 수 있음


--사원테이블엔 NULL데이터 있지만 M에는 없음. 비교했을 때 데이터가 없는 테이블이 된다

SELECT E.EMPNO,E.ENAME,E.MGR,M.ENAME
FROM EMP E,EMP M
WHERE E.MGR = M.EMPNO(+); --데이터가 없는 테이블 쪽에 (+)를 붙힌다


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


-----ANSI-JOIN (표준조인 방식)************* innerjoin outerjoin 
--cross join
--natural join
-- inner join(equi,non equi,self)
--outer join(+) [left,right,full] +outer join(셋 중 하나를 붙혀 써야한다.)

select ename,sal,dname,loc
from emp e inner join dept d  --equijoin
on e.deptno = d.deptno;


select ename,sal,dname,loc
from emp e inner join dept d
using(deptno) -- 양쪽테이블의 컬럼명이 동일한경우 
where ename = 'SCOTT'; --추가 조건을 넣을 수 있다 


select e.empno,e.ename,e.mgr,m.ename
from emp e inner join emp m
on e.mgr = m.empno;

------------------------outer join 

select empno,emname, sal,grade
from emp e inner join salgrade s
 on e.sal between s.losal and hisal;

select e.empno,e.ename,e.mgr,m.ename
from emp e left outer join emp m 
on e.mgr = m.empno;


select empno,enmae,sal,d.deptono,dname,grade
from emp e inner join dept d
on e.deptno = d.deptno
inner join salgrades s
on e.sal between s.losal and s.hisal;

--(+) 사용과 동일하게 작성(일반 outer join을 표준 ansi join방식으로)
select ename,sal,d.deptno,dname
from emp e right outer join dept d
on e.deptno = d.deptno;

--양쪽모두 null 값이 있는경우 outer join all

-----------------------------------------------p237연습문제
--8-1
--급여가 2000 초과인 사원들의 부서정보, 사원정보를 출력
select e.deptno,dname,empno,ename,sal
from emp e, dept d
where e.deptno = d.deptno 
and sal > 2000;
--order by deptno;

--ANSI 답

SELECT D.DEPTNO,D.DNAME,E.EMPNO,E.ENAME,E.SAL
FROM EMP E INNER JOIN DEPT D
--USING(DEPTNO)
ON E.DEPTNO = D.DEPTNO
WHERE E.SAL > 2000;



--8-2
--각 부서별 평균 급여 ,최대급여, 최소급여, 사원수 출력

select e.deptno,dname,avg(e.sal),max(e.sal),min(e.sal)
from emp e inner join dept d
on e.deptno = d.deptno
group by e.deptno,d.dname; ---왜 d.dname꼭 있어야 하지????

--답
SELECT E.DEPTNO,
        D.DNAME,
        TRUNC(AVG(SAL)),
        MAX(SAL),
        MIN(SAL),
        COUNT(*)
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
GROUP BY E.DEPTNO,D.DNAME;




--8-3
--모든 부서 정보와 사원저옵를 부서번화, 사원이름순으로 정렬
select d.deptno,dname,empno,ename,job,sal
from emp e right outer join dept d
on e.deptno = d.deptno
order by deptno, ename;

---답
select d.deptno,d.dname,e.empno,ename,e.job,e.sal
from emp e right outer join dept d
on e.deptno = d.deptno
order by d.deptno, e.ename;



--4
select d.deptno,dname,empno,ename,mgr,sal,e.deptno,losal,hisal,grade,
from emp e,dept d,salgrade s
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal;

--답

select d.deptno,dname,
        e.empno,e.ename,e.mgr,e.sal,e.deptno,
        s.losal,s.hisal,s.grade,
        m.empno, m.ename    
from emp e right outer join dept d
on e.deptno = d.deptno
full outer join salgrade s
on e.sal between s.losal and s.hisal
left outer join emp m 
on e.mgr = m.empno
order by d.deptno, e.empno;

--using (deptno ) group by 적용시 사용불가 ******