<21, 22> JOIN ~ON 절 / 좌우 외부 조인 (Left Right Outer Join)

ON절은 가독성이 우수하다는 장점이 있다.

하지만, 사실 이거 뭐 어떻게 돌아가는지 잘 모르겠고 그냥 불편하다. Natural이 짱임..

 

[ON 절] : 가독성이 우수하다.
조인조건을 명시적으로 표현하기 때문에.
ON 절의 특징 : 등가 조인과 비등가 조인을 모두 표현할 수 있다.

-- 등가조인
1) 각 사원의 근무 부서를 검색하세요
SELECT eno 사번, ename 이름, dept.dno 부서번호, dname 부서명
FROM emp
JOIN dept ON emp.dno=dept.dno;

--비등가조인
2) 개발 업무를 담당하는 사원의 급여 등급을 검색하세요
SELECT eno 사번, ename 이름, job 업무, grade 급여등급
FROM emp
JOIN salgrade ON sal BETWEEN losal AND hisal
WHERE job='개발';

--SELECT eno 사번, ename 이름, job 업무, grade 급여등급
--FROM emp
--JOIN salgrade ON sal BETWEEN losal AND hisal
--AND job='개발';

--  >>가능은 하지만 비추
-- JOIN과 관련없는 조건을 AND로 묶으면 일반조건/ 조인조건의 기준이 모호해짐.
-- 가독성면에서도 비추


3) 직원의 부서명과 급여등급을 검색하세요
--원칙을 정하는 것이 좋다.
-- ex) 등가조인 : USING / 비등가 : ON  --> 이런식으로 원칙을 정해 놓으면 나중에 가독성이 좋아진다

SELECT eno 사번, ename 이름, dname 부서명, grade 급여등급
FROM emp
JOIN dept USING (dno)
JOIN salgrade ON sal BETWEEN losal AND hisal;

4) 직원의 이름과 담당 관리자 이름을 검색하시오
-- 자기 참조 조인 : ON 절로 표현
 SELECT e1.eno, e1.ename 사원, e2.eno, e2.ename 관리자
 FROM emp e1
 JOIN emp e2 ON e1.mgr=e2.eno;
 
 
 [좌우 외부 조인 (Left Right Outer Join)]
 : (+)기호로 하는 외부조인은 둘중 한쪽에만 사용가능
 여기서는 마지 양쪽에 (+)를 한 것같은 표현이 가능...
 a. (+) : POS 부서는 존재, POS 부서원 X
        dept.dno=emp.dno(+)
 b. 부서원이 없는 POS부서 존재
    홍길동 신입사원의 부서가 배정되기 전
    이럴 경우 FULL JOIN 을 사용해야 한다.
    
  
5) 홍길동 사원을 추가해보자
INSERT INTO emp(eno, ename, job)
VALUES ('3006', '홍길동', '설계');

COMMIT; --확정


6) 사원이름과 소속부서 검색
SELECT eno 사번, ename 이름, dno 부서번호, dname 부서명
FROM emp RIGHT JOIN dept USING (dno); --오른쪽 dept 에 기준을 맞춰라..

SELECT eno 사번, ename 이름, dno 부서번호, dname 부서명
FROM emp LEFT JOIN dept USING (dno); 

SELECT eno 사번, ename 이름, dno 부서번호, dname 부서명
FROM emp
FULL JOIN dept USING (dno); 

 

연습문제

--1) 각 과목의 과목명과 담당 교수의 교수명을 검색하라 -김가경
SELECT professor.pno 교수번호, pname 이름, cname 과목명
FROM professor
JOIN course ON professor.pno=course.pno;

--2) 화학과 학생의 기말고사 성적을 모두 검색하라
SELECT major 학과, s.sno 학번, sname 이름, result 성적
FROM student s
JOIN score sc ON s.sno=sc.sno
WHERE major='화학';

--3) 유기화학과목 수강생의 기말고사 시험점수를 검색하라
SELECT s.sno 학번, sname 이름, cname 과목이름, result 성적
FROM student s
JOIN score r ON s.sno=r.sno
--JOIN course c ON c.cno=r.cno
JOIN course USING (cno)
WHERE cname='유기화학';


--4) 화학과 학생이 수강하는 과목을 담당하는 교수의 명단을 검색하라
SELECT p.pno 교수번호, pname 이름, cno 과목번호, cname 과목이름
FROM professor p
JOIN course c ON p.pno=c.pno
WHERE section='화학';


--5) 모든 교수의 명단과 담당 과목을 검색한다
SELECT p.pno 교수번호, pname 이름, cno 과목번호, cname 과목이름
FROM professor p
LEFT JOIN course c ON p.pno=c.pno;

--6) 모든 교수의 명단과 담당 과목을 검색한다(단 모든 과목도 같이 검색한다)
SELECT p.pno 교수번호, pname 이름, cno 과목번호, cname 과목이름
FROM professor p
FULL JOIN course c ON p.pno=c.pno;