SQL)Oracle_SELECT_JOIN절/ON절/좌우외부조인

--[ON 절]

--; 가독성이 우수하다
--  (조인조건을 명시적으로 표현)
--등가 조인과 비등가 조인을 모두 표현할 수 있다
  

--1) 각 사원의 근무부서를 검색하세요
--등가 조인

 

SELECT dno,dname,eno,ename
 FROM dept
 JOIN emp USING(dno);
 
SELECT dept.dno, dname, eno, ename
 FROM dept
 JOIN emp ON dept.dno=emp.dno;
 
SELECT d.dno, dname, eno, ename
 FROM dept d
 JOIN emp e ON d.dno=e.dno;

 

--2) 개발 업무를 담당하는 사원의 급여 등급을 검색하세요
--비등가 조인

 

SELECT eno,ename,job,sal,grade 
 FROM emp
 JOIN salgrade ON sal BETWEEN losal AND hisal
 WHERE job='개발'
 ORDER BY 4 DESC;
 
 SELECT eno,ename,job,sal,grade 
 FROM emp
 JOIN salgrade ON sal BETWEEN losal AND hisal
 AND job='개발'
 ORDER BY 4 DESC;

 

위처럼 해도 실행은 된다. 하지만
 아래처럼 조인과 관련없는 job='개발'같은 일반 조건을 
 ON 절에 쓰는 것은 안하는 것이 좋다.
 왜냐하면 일반조건과 조인조건의 기준이 모호해지기 때문에
 그래서 위처럼 비등가조인은 ON 절에, 일반 조건은 WHERE 절에
 주는 것이 가독성 면에서 좋다.

 

 

 

-- 3) 직원의 부서명과 급여등급을 검색하세요

 

--원칙을 정하는 것이 좋다
--예를 들어 등가 조인은 USING 절을
--비등가 조인은 ON 절을 사용한다
--이런 식으로 원칙을 정해놓으면 나중에 가독성이 좋아진다

 

SELECT eno,ename,dno,dname,sal,grade
 FROM emp
 JOIN dept USING(dno)
 JOIN salgrade ON sal BETWEEN losal AND hisal;
 
SELECT eno,ename,d.dno,dname,sal,grade
 FROM emp e
 JOIN dept d ON e.dno=d.dno
 JOIN salgrade ON sal BETWEEN losal AND hisal;

 

 

--4) 직원의 이름과 담당 관리자이름을 검색하세요

-- 자기참조 조인 : ON 절로 표현
--e1 : 사원테이블
--e2 : 관리자테이블

 

SELECT e.eno 사번 , e.ename 사원명 ,m.eno "관리자 사번", m.ename 관리자명
 FROM emp e
 JOIN emp m ON e.mgr=m.eno;

 

 

[좌우 외부 조인(Left Right Full Outer Join)]

; (+)기호로 하는 외부조인은 둘중에 한쪽에만 사용가능하다
  그러나 여기서는 마치 양쪽에 (+)를 한 것 같은 표현이 가능하다
  a. (+) : POS부서는 존재, POS에 부서원이 없을 때
           dept.dno=emp.dno(+)
  b. 부서원이 없는 POS부서 존재
     홍길동 신입사원의 부서가 배정되기 전
     이럴 경우는 FULL JOIN을 사용해야 한다.

 

5) 홍길동 사원을 추가해보자

 

INSERT INTO emp(eno,ename)
 VALUES('3007', '임꺽정');
 
COMMIT;

SELECT * 
 FROM emp
 WHERE ename IN ('홍길동','임꺽정');

 

 

--부서별 사원을 검색하라

 

SELECT d.dno,dname,eno,ename
 FROM emp e
 JOIN dept d ON e.dno(+)=d.dno;

 

--소속 사원이 없는 부서까지 출력

 

SELECT e.dno,dname,eno,ename
 FROM emp e
 JOIN dept d ON e.dno=d.dno(+);

 

 

--사원들의 부서를 검색하라

--사원이 아직 부서배정을 못받은 사원까지 출력

 

SELECT e.dno,dname,eno,ename
 FROM emp e
 FULL OUTER JOIN dept d ON e.dno=d.dno;
 
 SELECT e.dno,dname,eno,ename
 FROM emp e
 FULL  JOIN dept d ON e.dno=d.dno;

 

 

--사원이 없는 부서 : POS
--부서가 없는 사원 : 홍길동
--둘 다 출력하려면

 

--LEFT JOIN 은 왼쪽 테이블의 모든 정보를 출력하겠다
--LEFT 에 기준을 맞추겠다

 

 SELECT dno,dname,eno,ename
FROM emp 
LEFT JOIN dept USING(dno);

 SELECT dno,dname,eno,ename
FROM emp 
RIGHT JOIN dept USING(dno);

 

--RIGHT JOIN 은 오른쪽 테이블의 모든 정보를 출력하겠다
--즉 , RIGHT 테이블에 기준을 맞추겠다

 

 

--6) 사원 이름과 소속 부서를 검색하세요

--OUTER 는 써도 되고 생략해도 됨
--FULL OUTER JOIN
--LEFT OUTER JOIN
--RIGHT OUTER JOIN

 

SELECT dno,dname,eno,ename
 FROM emp 
 FULL  JOIN dept USING(dno);