DB 3차시 - 조인(SELF, OUTER)

**조인(JOIN)

    ->2개 이상의 테이블에서 찾고자하는 데이터를 검색하기 위해서

 

문제> 'King'의 부서이름을 출력하시오.

        -> SELECT employee_id, department_id

                    FROM employees

                    WHERE last_name = 'King';

        -> SELECT department_id, department_name

                    FROM departments

                    WHERE department_id IN(80, 90);

 

-> 이런 번거로움을 덜고자 조인을 이용

==>조인 이용

 

조인사용방법

1. 내가 원하는 데이터가 무엇인가?(컬럼목록)

2. 원하는 데이터가 어느 테이블에 있는가?

3. 여러 테이블에 있다면 각각의 테이블의 공통컬럼을 찾는다.

 

->SELECT e.employee_id, e.department_id, d.department_name

            FROM employees e, departments d

            WHERE e.department_id = d.department_id

            AND last_name = 'King';

 

 

퀴즈 kosta202> '송강' 교수가 강의하는 과목을 검색하라.

-> SELECT p.pno,p.pname,c.cname

            FROM professor p, course c

            WHERE p.pno= c.pno

            AND pname = '송강';

 

 

<mission kosta202>

1. 학점이 2학점인 과목과 이를 강의하는 교수를 검색하라.

2. 화학과 1학년 학생의 기말고사 성적을 검색하라.

3. 화학과 1학년 학생이 수강하는 과목을 검색하라.(3개 테이블 조인)

->SELECT c.cname, c.st_num, p.pname

        FROM professor p, course c

        WHERE p.pno = c.pno

        AND st_num=2;

->SELECT s.syear,s.major, s.sname, c.result

        FROM student s, score c

        WHERE s.sno = c.sno

        AND syear = 1 AND major = '화학';

->SELECT s.syear, s.major, s.sname,p.pname, c.cname

        FROM student s, score s1, course c

        WHERE s.cno = c1.cno

        AND s1.cno = c.cno

        AND syear = 1 AND major = '화학';

 

==> ANSI JOIN

(이전)

->SELECT e.employee_id, e.department_id, d.department_name

            FROM employees e, departments d

            WHERE e.department_id = d.department_id

            AND last_name = 'King';

 

(이후)

->SELECT e.employee_id, e.department_id, d.department_name

            FROM employees e INNER JOIN departments d

            ON e.department_id = d.department_id

            WHERE last_name = 'King';     //where절이 간단해서 쓰기 좋음.

 

-3개 이상 조인

->테이블1 JOIN 테이블2

        ON 공통컬럼1 = 공통컬럼1

        JOIN 테이블3 

        ON 공통컬럼2 = 공통컬럼2

 

퀴즈 hr>3개 이상 테이블을 조인하여 사원이름, 이메일, 부서번호, 부서이름, 직종번호(job_id), 직종이름(job_title)을 출력하라.

 

->SELECT e.last_name e.email, d.department_id, d.department_name, j.job_id, j.job_title

            FROM employees e, departments d, jobs j

            WHERE e.department_id = d.department_id

            AND j.job_id = e.job_id;

            

->SELECT e.last_name e.email, d.department_id, d.department_name, j.job_id, j.job_title

            FROM employees e INNER JOIN departments d

            ON e.department_id = d.department_id

            INNER JOIN jobs j

            ON e.job_id = j.job_id;

 

퀴즈 hr> 'Seattle' (city)에 근무하는 사원이름, 부서번호, 직종번호, 직종이름, 도시이름을 출력하라

->SELECT e.last_name, e.department_id, j.job_id, j.job_title, l.city

      FROM employees e INNER JOIN jobs j

      ON e.job_id = j.job_id

      INNER JOIN departments d

      ON e.department_id = d.department_id

      INNER JOIN locations l

      ON l.location_id = d.location_id

      WHERE l.city = 'Seattle';

 

** SELF 조인

=> 'Kochhar' 직속상사의 정보를 출력하라.

->SELECT A.last_name || '의 매니저는 ' || B.last_name || '이다.'

            FROM employees A, employees B

            WHERE A.manager_id = B.employee_id //a의 매니저이름을 b의 직원이름으로 가져옴

            AND A.last_name = 'Kochhar';

 

퀴즈 kosta202> 학생 중에 동명이인을 검색하라.

->SELECT a.sno, a.sname,b.sno, b.sname

    FROM student a, student b

    WHERE a.sname = b.sname AND a.sno != b.sno;

->SELECT DISTINCT a.sno, a.sname //중복제거

    FROM student a, student b

    WHERE a.sname = b.sname AND a.sno != b.sno;

 

★★면접 **INNER JOIN VS OUTER JOIN, index, 주식별자/비식별자, dom

inner join : 공통된 부분 조인

outer join : 공통되지 않은 부분까지 조인

 

**OUTER조인(외부 조인)

SELECT * FROM employees; 107row

 

->SELECT e.employee_id, e.department_id, d.department_name

            FROM employees e, departments d

            WHERE e.department_id = d.department_id(106row)

 

데이터값이 없더라도 누락된 값없이 조회되기를 원함. => 아웃조인

 

->OUTER JOIN 적용

->SELECT e.employee_id, e.department_id, d.department_name

            FROM employees e, departments d

            WHERE e.department_id = d.department_id(+); //e에 있는 department_id가 null이면 그 반대식에 +를 적용

 

**ANSI JOIN(outer join)

->SELECT e.employee_id, e.department_id, d.department_name

            FROM employees e LEFT JOIN departments d (null이 발생하는 값(데이터가 없는쪽)에 LEFT/LIGHT써줌)

            ON e.department_id = d.department_id;

 

퀴즈 kosta202 > 이번 학기 등록된 과목에 대한 모든 교수를 검색하라. (등록하지 않아도 교수 출력)

SELECT * FROM professor; (36rows)

SELECT * FROM course; (32rows)

Inner Join(29rows)

SELECT p.pno, p.pname, p.section, c.cname

            FROM professor p  JOIN course c

            ON p.pno = c.pno; (32rows)

Outer Join(32rows)

SELECT c.pno, c.cname, c.st_num, p.pname

            FROM professor p ,course c

            ON p.pno = c.pno(+);

SELECT c.pno, c.cname, c.st_num, p.pname

            FROM professor p LEFT JOIN course c

            ON p.pno = c.pno;

 

SELECT c.pno, c.cname, c.st_num, p.pname

            FROM professor p FULL JOIN course c

            ON p.pno = c.pno; (양쪽 누락값 다 추출)

 

 

**조인 퀴즈(내가푼것)

--1 이름이 ‘Himuro’인 사원의 부서명을 출력하라.

SELECT d.department_name

      FROM employees e ,departments d

      WHERE e.department_id = d.DEPARTMENT_ID

      AND last_name = 'Himuro';

--2 직종명이 'Accountant'인 사원의 이름과 부서명을 출력하라.

SELECT e.last_name, d.department_name

      FROM employees e INNER JOIN jobs j

      ON e.job_id = j.job_id

      INNER JOIN departments d

      ON d.department_id = e.department_id

      WHERE job_title = 'Accountant';

--3 커미션을 받는 사람의 이름과 그가 속한 부서를 출력하라.

SELECT e.last_name, department_name

    FROM employees e, departments d

    WHERE e.department_id = d.DEPARTMENT_ID

    AND e.commission_pct IS NOT NULL;

--4 급여가 4000이하인 사원의 이름, 급여, 근무지를 출력하라.

SELECT e.last_name, e.salary, l.city

    FROM employees e, departments d, locations l

    WHERE e.department_id = d.department_id AND d.location_id=l.location_id

    AND e.salary <=4000;

--5 'Chen'과 동일한 부서에서 근무하는 사원의 이름을 출력하라.

SELECT e2.last_name

    FROM employees e, employees e2

    WHERE e.department_id = e2.department_id

    AND e.last_name = 'Chen';

 

**조인 답 (강사님 코드)

1. 이름이 ‘Himuro’인 사원의 부서명을 출력하라.

SELECT*FROM employees;

SELECT*FROM departments;

 

SELECT em.last_name, de.department_name

FROM employees em, departments de

WHERE em.department_id = de.department_id

AND em.last_name = 'Himuro';

 

 

SELECT em.last_name, de.department_name

FROM employees em

JOIN departments de

ON em.department_id = de.department_id

WHERE em.last_name = 'Himuro';

 

 

 

2. 직종명이 'Accountant'인 사원의 이름과 부서명을 출력하라.

SELECT*FROM employees;

SELECT*FROM jobs;

SELECT*FROM departments;

 

SELECT jobs_title, last_name, demaprtment_name

FROM jobs js, emlpoyees es, departments ds

WEHRE js.job_id = es.job_id

AND es.department_id = ds.department_id;

 

SELECT js.job_title, es.last_name, ds.department_name

FROM jobs js

JOIN emlpoyees es

ON js.job_id = es.job_id

JOIN departments ds

ON es.department_id = ds.department_id;

 

 

3. 커미션을 받는 사람의 이름과 그가 속한 부서를 출력하라.

SELECT*FROM employees;

SELECT*FROM departments;

 

SELECT es.commission_pct, ds.department_name, es.last_name

FROM employees es, departments ds

WEHRE es.department_id = ds.department_id;

 

SELECT es.commission_pct, ds.department_name, es.last_name

FROM employees es

JOIN departments ds

ON es.department_id = ds.department_id;

 

 

 

4. 급여가 4000이하인 사원의 이름, 급여, 근무지를 출력하라.

SELECT*FROM employees

SELECT*FROM departments

SELECT*FROM locations ls

 

SELECT es.last_name, es.salary, ls.city

FROM employees es, departments ds, locations ls

WHERE es.department_id = ds.department_id

AND ds.location_id = ls.location_id

AND es.salary<4000;

 

SELECT es.last_name, es.salary, ls.city

FROM employees es

JOIN departments ds

ON es.department_id = ds.department_id

JOIN locations ls

ON ds.location_id = ls.location_id

WHERE es.salary<4000;

 

 

5. 'Chen'과 동일한 부서에서 근무하는 사원의 이름을 출력하라.

SELECT*FROM employees;

 

SELECT A.last_name, B.last_name

FROM employees A, employees B

WHERE A.department_id = B.department_id

AND A.last_name = 'Chen';

 

SELECT A.last_name, B.last_name

FROM employees A

JOIN employees B

ON A.department_id = B.department_id

WHERE A.last_name = 'Chen';

 

SELECT A.last_name, B.last_name

FROM employees A

JOIN employees B

ON A.department_id = B.department_id

WHERE A.last_name = 'Chen';

 

 

SELECT A.last_name, B.last_name

FROM employees A, employees B

WHERE A.department_id = B.department_id

AND A.last_name = 'Chen';

 

모든~~ =OUTER JOIN!!

 

SQL#4 퀴즈

 

--1

SELECT * FROM EMP;

SELECT * FROM DEPT;

SELECT * FROM BONUS;

SELECT * FROM SALGRADE;

 

SELECT d.deptno, d.dname, e.empno, e.ename, e.sal

  FROM dept d, emp e

  WHERE d.deptno = e.deptno

  AND SAL >2000;

--2

SELECT d.deptno, d.dname, TRUNC (avg(sal),0) AS AVG_sAL, max(sal) AS MAX_SAL, min(sal) AS MIN_SAL, count(*) AS CNT

  FROM dept d, emp e

  WHERE d.deptno = e.deptno

  GROUP BY  d.deptno, d.dname;

 

--3

SELECT d.deptno, d.dname, e.empno, e.ename,e.job,e.sal

  FROM dept d LEFT JOIN emp e

  ON d.deptno = e.deptno

  ORDER BY d.DEPTNO, e.ename;

  

--4 self join & outer join

SELECT DISTINCT d.deptno, d.dname,

e.empno, e.ename,e.mgr, e.sal, e.deptno AS DEPTNO_1,

 s.losal, s.hisal, s.grade, 

e2.empno AS MGR_EMPNO, e2.ename AS MGR_ENAME

  --FROM dept d , emp e, salgrade s,  emp e2

-- WHERE d.deptno = e.deptno(+) 

  --AND e.sal BETWEEN s.losal(+) AND s.hisal(+)

  --AND e.mgr = e2.empno(+)

  --ORDER BY d.deptno, e.empno;

  FROM emp e LEFT JOIN emp e2 --매니저가 없을 수도 있으니까 왼쪽이 더 큼

  ON e.mgr = e2.empno

  LEFT JOIN salgrade s //s보다 원래 있던 값들이 데이터범위 더 큼

  ON e.sal BETWEEN s.losal AND s.hisal

  RIGHT JOIN dept d

  ON e.deptno = d.deptno

  ORDER BY d.deptno, e.empno;

 

-- 강사님코드

-- 8-1

-- SQL-99 �씠�쟾 諛⑹떇

SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL

  FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO

   AND E.SAL > 2000;

 

-- SQL-99諛⑹떇

SELECT DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL

  FROM EMP E NATURAL JOIN DEPT D //공통부분 조인

WHERE E.SAL > 2000;

 

-- 8-2

--SQL-99 �씠�쟾 諛⑹떇

SELECT D.DEPTNO,

       D.DNAME,

       TRUNC(AVG(SAL)) AS AVG_SAL,

       MAX(SAL) AS MAX_SAL,

       MIN(SAL) AS MIN_SAL,

       COUNT(*) AS CNT

  FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO

GROUP BY D.DEPTNO, D.DNAME;

 

--SQL-99 諛⑹떇

SELECT DEPTNO,

       D.DNAME,

       TRUNC(AVG(SAL)) AS AVG_SAL,

       MAX(SAL) AS MAX_SAL,

       MIN(SAL) AS MIN_SAL,

       COUNT(*) AS CNT

  FROM EMP E JOIN DEPT D USING (DEPTNO) //using = on

GROUP BY DEPTNO, D.DNAME;

 

-- 8-3

--SQL-99 �씠�쟾 諛⑹떇

SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL

  FROM EMP E, DEPT D

WHERE E.DEPTNO(+) = D.DEPTNO

ORDER BY D.DEPTNO, E.ENAME;

 

--SQL-99 諛⑹떇

SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL

  FROM EMP E RIGHT OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)

ORDER BY D.DEPTNO, E.ENAME;

 

-- 8-4

--SQL-99 �씠�쟾 諛⑹떇

SELECT D.DEPTNO, D.DNAME,

       E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO,

       S.LOSAL, S.HISAL, S.GRADE,

       E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME

  FROM EMP E, DEPT D, SALGRADE S, EMP E2

WHERE E.DEPTNO(+) = D.DEPTNO

   AND E.SAL BETWEEN S.LOSAL(+) AND S.HISAL(+)  // between값에 포함안되어도 null값으로 추출해라.

   AND E.MGR = E2.EMPNO(+)

ORDER BY D.DEPTNO, E.EMPNO;

 

--SQL-99諛⑹떇

SELECT D.DEPTNO, D.DNAME,

       E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO,

       S.LOSAL, S.HISAL, S.GRADE,

       E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME

  FROM EMP E RIGHT OUTER JOIN DEPT D

                ON (E.DEPTNO = D.DEPTNO)

              LEFT OUTER JOIN SALGRADE S

                ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)

              LEFT OUTER JOIN EMP E2

                ON (E.MGR = E2.EMPNO)

ORDER BY D.DEPTNO, E.EMPNO;

 

 

**SQL3 과제

--sql 3

SELECT * FROM EMP;

--1

SELECT deptno, TRUNC(avg(sal),0) AS AVG_SAL,

  max(sal) AS MAX_SAL, min(sal) AS MIN_SAL, COUNT(*) AS CNT

  FROM emp

  GROUP BY deptno

  ORDER BY deptno DESC;

--2

SELECT job, count(*)

  FROM EMP

  GROUP BY job

  HAVING COUNT(*)>=3;

--3

SELECT DISTINCT TO_CHAR(HIREDATE,'YYYY') AS HIRE_YEAR, DEPTNO, COUNT(*) AS CNT

  FROM EMP

  GROUP BY TO_CHAR(HIREDATE,'YYYY'), DEPTNO;

  

--4

SELECT * FROM EMP;

SELECT NVL2(COMM,'O','X') AS EXIST_COMM, COUNT(*) AS CNT

  FROM EMP

  GROUP BY NVL2(COMM,'O','X');

 

--5

SELECT  DEPTNO, TO_CHAR(HIREDATE,'YYYY') AS HIRE_YEAR , COUNT(*) AS CNT,

  max(sal) AS MAX_SAL, sum(sal) AS SUM_SAL, avg(sal) AS AVG_SAL

  FROM EMP

  GROUP BY ROLLUP(DEPTNO, TO_CHAR(HIREDATE,'YYYY')); -> 3가지의 총합계가 보임 

1. 부서별, 2. 날짜별, 3. 총합

 

★★모든 데이터를 조인해라 => 아우터 조인