JOIN (조인)

--------------------------------------------------------------------------------

                        --■■■ JOIN (조인) ■■■--

--------------------------------------------------------------------------------

-- 덩치가 큰 테이블은 무겁고 효율적이지 않기 때문에 테이블을 나눈다

-- 나누는 행위를 정규화를 통해 실시함

-- 1. SQL 1992 CODE

SELECT *

FROM EMP;

SELECT *

FROM DEPT;

SELECT *

FROM EMP,DEPT;

--> CROSS JOIN → 수학에서 말하는 데카르트 곱(Cartersian Product)

--  두 테이블을 합친 모든 경우의 수. 자주 사용되지는 않는다.

SELECT COUNT(*)

FROM EMP;

--==>> 14

SELECT COUNT(*)

FROM DEPT;

--==>> 4

SELECT COUNT(*)

FROM EMP,DEPT;

--==>> 56 = 14*4

-- Equi Join : 서로 정확히 같은 것끼리 결합

SELECT *

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO;

SELECT *

FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO;

--TABLE명에 별칭을 붙여 사용할 수 있다.

-- Non Equi join : 범위 안에 적합한 것들 끼리 결합

SELECT *

FROM SALGRADE;

SELECT *

FROM EMP;

SELECT *

FROM EMP E, SALGRADE S

WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

--EMP TABLE의 급여가 SALGRADE TABLE의 LOW~HIGH까지의 범위안에 있는 것들만 보여줌

INSERT INTO EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) 

VALUES(7966,'RAY', 'CLERK', 7839, SYSDATE, 1000, 1000)

--==>>1 행 이(가) 삽입되었습니다.

COMMIT;

-- Equi join 시 (+) 사용방법

SELECT *

FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO (+);

--==>>7966 RAY CLERK 7839 2018-01-10 1000 1000

SELECT *

FROM EMP E, DEPT D

WHERE E.DEPTNO(+)=D.DEPTNO;

--==>> 40 OPERATIONS BOSTON

--※ (+) 가 없는 쪽 테이블의 데이터를 모두 먼저 메모리에 퍼올린 후 

--   (+) 가 있는 쪽 테이블의 데이터와 짝을 구성하는 과정을 통해 조회 결과를 종합.

SELECT *

FROM EMP E, DEPT D

WHERE E.DEPTNO(+)=D.DEPTNO(+);

--> 이와 같은 구문은 존재하지 않는다.

--==>>에러발생

--(+)가 없는쪽이 주인공 (+)가 없는 테이블 내용을 다 뿌린다음에 (+) 있는 것을 나열한다

--연결고리가 없을 시에는 NULL로 표시

-- 2. SQL 1999 CODE

-- CROSS JOIN

SELECT *

FROM EMP CROSS JOIN DEPT;

-- INNER JOIN

SELECT *

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO;

SELECT *

FROM EMP E JOIN DEPT D

ON E.DEPTNO=D.DEPTNO;

-->INNER JOIN의 경우 INNER의 키워드를 생략할 수 있다.

-->기존 조건을 작성하던 WHERE를 ON으로 대체

SELECT *

FROM EMP E JOIN SALGRADE S

ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

-- OUTER JOIN

SELECT *

FROM EMP E LEFT OUTER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO;

--> (+)가 오른쪽에 붙어 있는 결과와 같음

SELECT *

FROM EMP E RIGHT OUTER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO;

--> (+)가 왼쪽에 붙어 있는 결과와 같음

SELECT *

FROM EMP E FULL OUTER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO;

--> (+)를 양쪽에 붙인 결과 새로 생긴 문법 

-- OUTER JOIN도 OUTER 키워드를 생략할 수 있다.

SELECT *

FROM EMP E FULL JOIN DEPT D

ON E.DEPTNO=D.DEPTNO;

-- 위와 같이 조회한 결과에서

-- 직종이 MANAGER와 CLERK만 조회하고자 한다.

SELECT *

FROM EMP E JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

AND JOB IN ('MANAGER','CLERK');

--  쿼리문을 이렇게 구성해도 조회는 가능하지만 권장하지 않는다.

SELECT *

FROM EMP E JOIN DEPT D

ON E.DEPTNO=D.DEPTNO                  -- JOIN(결합) 조건

WHERE JOB IN ('MANAGER','CLERK');     -- 레코드의 선택 조건

--○ 퀴즈

-- EMP 테이블과 DEPT 테이블을 활용하여

-- 직종이 MANAGER와 CLERK인 사원들의 

-- 부서번호, 부서명, 사원명, 직종명, 급여 항목으로 조회한다.

SELECT *

FROM EMP;

SELECT *

FROM DEPT;

--연결고리가 DEPTNO임을 알 수 있음

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

FROM EMP E JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

AND JOB IN ('MANAGER','CLERK');

--증복 컬럼과 같은 경우는 소속을 명시해주어야 한다.

--소속을 명시해 줄때는 부모 테이블것을 명시해 주어야한다.

--관계형 데이터베이스에서 부모와 자식을 결정짓는 것은 1 : 다 관계이다

--하나가 여럿을 담당하고 있을때 그 하나가 부모이다.

--중복컬럼이 아닌 나머지 컬럼들인 한가지 테이블에 존재하는 것들도 소속을 명시해주는 것이 바람직함

--소속테이블을 명시해주지 않으면 컬럼이 존재하더라도 나머지 테이블도 조회해야 하기 때문에

--리소스 소모가 심하다.

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

FROM EMP E RIGHT JOIN DEPT D

ON E.DEPTNO=D.DEPTNO;

/*

10 ACCOUNTING CLARK MANAGER 2450

10 ACCOUNTING MILLER CLERK 1300

10 ACCOUNTING KING PRESIDENT 5000

20 RESEARCH JONES MANAGER 2975

20 RESEARCH SMITH CLERK 800

20 RESEARCH SCOTT ANALYST 3000

20 RESEARCH FORD ANALYST 3000

20 RESEARCH ADAMS CLERK 1100

30 SALES WARD SALESMAN 1250

30 SALES TURNER SALESMAN 1500

30 SALES ALLEN SALESMAN 1600

30 SALES JAMES CLERK 950

30 SALES MARTIN SALESMAN 1250

*/

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

FROM EMP E RIGHT JOIN DEPT D

ON E.DEPTNO=D.DEPTNO;

/*

10 ACCOUNTING CLARK MANAGER 2450

10 ACCOUNTING MILLER CLERK 1300

10 ACCOUNTING KING PRESIDENT 5000

20 RESEARCH JONES MANAGER 2975

20 RESEARCH SMITH CLERK 800

20 RESEARCH SCOTT ANALYST 3000

20 RESEARCH FORD ANALYST 3000

20 RESEARCH ADAMS CLERK 1100

30 SALES WARD SALESMAN 1250

30 SALES TURNER SALESMAN 1500

30 SALES ALLEN SALESMAN 1600

30 SALES JAMES CLERK 950

30 SALES MARTIN SALESMAN 1250

30 SALES BLAKE MANAGER 2850

OPERATIONS

*/

--■■■ SELF JOIN (자기 조인) ■■■--

--○ EMP테이블의 정보를 다음과 같이 조회할 수 있도록 한다.

/*

사원번호   사원명    직종    관리자번호   관리자명    관리자직종명

  7369      SMITH     CLER      7902        FORD        ANALYST

  

*/

SELECT *

FROM EMP;

SELECT E.EMPNO"사원번호",E.ENAME"사원명",E.JOB"직종",E.MGR"관리자번호",EM.ENAME"관리자명",EM.JOB"관리자직종명"

FROM EMP E JOIN EMP EM

ON E.MGR = EM.EMPNO;

/*

  TBL_SAWON 테이블을 대상으로 다음과 같이 조회할 수 있도록 한다

  사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일, 정년퇴직일, 근무일수, 남은일수, 급여, 보너스

  단 현재나이는 한국나이 계산법에 따라 연산을 수행한다.

  또한, 정년퇴직일은 해당 직원의 나이가 한국나이로 60세가 되는 해의 그 직원의 입사 월, 일로 한다.

  그리고 보너스는 1000일 이상 2000일 미만 근무한 사원은 그 사원이 받는 급여의 30%로 하고

  2000일 이상 근무한 사원은 그 사원이 받는 급여의 50%로 한다.

  */

SELECT *

FROM TBL_SAWON;

SELECT SANO "사원번호", SANAME"사원명", JUBUN"주민번호"

    , DECODE(SUBSTR(JUBUN,7,1), '1','남자','4','남자', '여자') "성별"

    , CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '2') 

                    THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1899)

                    WHEN SUBSTR(JUBUN, 7, 1) IN ('3', '4')

                    THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1999)

                    ELSE 0

               END"현재나이"

    , HIREDATE "입사일"

    , TO_CHAR(ADD_MONTHS(SYSDATE,(60-(CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '2') 

                    THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1899)

                    WHEN SUBSTR(JUBUN, 7, 1) IN ('3', '4')

                    THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1999)

                    ELSE 0

               END))*12) ,'YYYY')||'-'||TO_CHAR(HIREDATE,'MM-DD') "정년퇴직일"    

    , TRUNC(SYSDATE - HIREDATE)"근무일수"

    , TRUNC(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE,(60-(CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '2') 

                    THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1899)

                    WHEN SUBSTR(JUBUN, 7, 1) IN ('3', '4')

                    THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1999)

                    ELSE 0

               END))*12) ,'YYYY')||'-'||TO_CHAR(HIREDATE,'MM-DD'), 'YYYY-MM-DD') - SYSDATE) "남은일수"

    , SAL"급여"

    , CASE WHEN TRUNC(SYSDATE-HIREDATE)=2000 THEN SAL*0.5

           WHEN TRUNC(SYSDATE-HIREDATE)=1000 THEN SAL*0.3

     ELSE 0

      END "보너스"

FROM TBL_SAWON;

--○ 문제

--  EMPLOYEES, DEPARTMENTS, JOBS, LOCATIONS, COUNTRIES, REGIONS

--  이 6개의 테이블을 대상으로 직원들의 정보를 다음과 같이 조회한다.

--  FIRST_NAME, LAST_NAME, JOB_TITLE, DEPARTMENT_NAME, CITY, COUNTRY_NAME, REGION_NAME

SELECT EM.FIRST_NAME, EM.LAST_NAME, JOBS.JOB_TITLE, DE.DEPARTMENT_NAME, LO.CITY, COUN.COUNTRY_NAME, RE.REGION_NAME

FROM EMPLOYEES EM LEFT JOIN DEPARTMENTS DE

ON EM.DEPARTMENT_ID = DE.DEPARTMENT_ID

  JOIN JOBS

  ON EM.JOB_ID=JOBS.JOB_ID

  LEFT JOIN LOCATIONS LO

  ON DE.LOCATION_ID=LO.LOCATION_ID

  LEFT JOIN COUNTRIES COUN

  ON COUN.COUNTRY_ID=LO.COUNTRY_ID

  LEFT JOIN REGIONS RE

  ON RE.REGION_ID=COUN.REGION_ID

     ;