(Database Programming) TABLE JOIN

• TABLE JOIN
  테이블간 Primary key – Foreign Key 로 연결해 관련정보들을 가져온다.

INNER JOIN

• JOIN을 표현하는 여러가지 단어
  -- > JOIN
  -- > EQUIP JOIN
  -- > NATURAL JOIN
  -- > INNER JOIN

INNER JOIN 문법

SELECT *
  FROM TABLE A
 INNER JOIN TABLE B
	ON A.PK = B.PK
;

LEFT OUTER JOIN

INLINE VIEW (많이 씀)
조인만으로 풀리지 않는 문제를 INLINE VIEW 를 통해서 처리함.
보통 INLINE VIEW내부에는 복잡한 JOIN 쿼리가 작성된다.
임시의 테이블을 만들어 쓴다라는 개념

INLINE VIEW 문법

SELECT [COLUMN], [SCALA QUERY], […]
  FROM [TABLE A]
 INNER JOIN
      (SELECT .. FROM .. WHERE …
      ) B
   ON A.PK = B.FK
WHERE [CONDITIONS]

/*SELECT COLUMN....
  FROM (SELECT *
          FROM TABLE) A -- 소괄호가 인라인뷰 
 INNER JOIN (SELECT *
               FROM TABLE) B
 WHERE.... 
*/

실습

-- 사원의 정보를 INLINE VIEW를 이용해 조회한다.
SELECT EMPLOYEE_ID
     , SALARY 
  FROM (-- INLINE VIEW로 이용할 SELECT 문장을 먼저 작성.
	    SELECT EMPLOYEE_ID
     		 , FIRST_NAME
             , LAST_NAME
             , SALARY
          FROM EMPLOYEES) TEMP_E
;

// 에러
SELECT EMPLOYEE_ID
     , SALARY --> ORA-00904: "SALARY": 부적합한 식별자
  FROM (-- INLINE VIEW로 이용할 SELECT 문장을 먼저 작성.
	    SELECT EMPLOYEE_ID
     		 , FIRST_NAME
             , LAST_NAME
          FROM EMPLOYEES) TEMP_E
 ;

소괄호 SELECT 안에 없는 SALARY는 쓸 수 없다.

-- EMPLOYEES 테이블에서 ROW 3개만 조회한다.
SELECT *
  FROM EMPLOYEES
 WHERE EMPLOYEE_ID <= 102
;

SELECT *
  FROM EMPLOYEES
 WHERE ROWNUM <= 3  --> ROWNUM은 오라클 전용
;

-- EMPLOYEES 테이블에서 연봉을 가장 많이 받는 사람 3명을 조회한다.
SELECT *
  FROM EMPLOYEES
 WHERE ROWNUM <= 3
 ORDER BY SALARY DESC
; -- 이런 방식으로는 풀 수 없다.

-- INLINE VIEW 이용해서 풀기
SELECT *
  FROM (-- 1. EMPLOYEES 테이블에서 연봉을 기준으로 내림차순 정렬한다.
	    SELECT *
  	   	  FROM EMPLOYEES
         ORDER BY SALARY DESC) ORDERED_EMPLOYEES
 WHERE ROWNUM <= 3
;

-- EMPLOYEES 테이블에서 연봉을 가장 적게 받는 사람 3명을 조회한다.
SELECT *
  FROM EMPLOYEES
 WHERE ROWNUM <= 3
 ORDER BY SALARY ASC 
; -- 이런 방식으로는 풀 수 없다.

-- INLINE VIEW 이용해서 풀기
SELECT *
  FROM (-- 1. EMPLOYEES 테이블에서 연봉을 기준으로 오름차순 정렬한다.
  		SELECT *
  	      FROM EMPLOYEES
      ORDER BY SALARY ASC) ORDERED_EMPLOYEES
 WHERE ROWNUM <= 3
;

재귀 조인 Recursive Join (Oracle 문법, 표준X)

• 한 테이블에 PK가 존재하고, 해당 PK를 참조하는 FK가 동시에 있는 경우의 조인

• 주로 계층형 관계에 이용한다. (카테고리 만들 때 사용)

(EMPLOYEES 테이블)
여러 명의 사원(EMPLOYEE_ID: PK)은 한 명의 상사(MANAGER_ID: FK)가 있다.
동일 테이블에서 참조가 이루어진다.
BOSS는 ROOT NODE / 최하층 사원은 LEAF NODE

  SELECT *
    FROM EMPLOYEES
   START WITH EMPLOYEE_ID = 100 -- 최상위 ROW가 될 기준
 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID -- 데이터를 연결할 기준; 

EMPLOYEE_ID(100)가 누군가의 MANAGER_ID(100)가 되어야 한다.
연결된 모든 부하직원들을 조회한다.

-- 재귀조인
-- 100번 사원의 모든 부하직원을 계층형(조직도) 구조로 조회한다.
 SELECT LEVEL 
 	  , EMPLOYEE_ID
      , FIRST_NAME || ' ' || LAST_NAME AS NAME
      , MANAGER_ID
   FROM EMPLOYEES
  START WITH EMPLOYEE_ID = 100 -- Root Node 지정.
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID -- 연결 조건 지정
										  -- 사원번호 100번은 누군가의 상사 사원번호다.
-- 100번부터 시작해서 모든 부하직원들을 계층 구조로 조회된다.
;

이렇게 트리구조로 조회 된다.

-- 60번 부서 부서장의 모든 부하직원을 조회한다.
-- 이렇게 서브쿼리로 푸는게 제일 깔끔하고 쉬움 / JOIN 써서 할 수도 있지만 복잡하고 욕심
 SELECT LEVEL
      , EMPLOYEE_ID
      , FIRST_NAME || ' ' || LAST_NAME AS NAME
      , MANAGER_ID
   FROM EMPLOYEES
  START WITH EMPLOYEE_ID = (SELECT MANAGER_ID
                              FROM DEPARTMENTS
                             WHERE DEPARTMENT_ID = 60) --> START WITH에서 WHERE은 이전에 쓰면 안된다. 써줄거면 이 안이나 밑에써줘야함(중복)
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
;

-- 부하직원이 없는 사원을 모두 조회한다. (START WITH CONNECT BY PRIOR 사용X)
-- 성능은 서브쿼리가 더 좋음
SELECT *
  FROM EMPLOYEES
 WHERE EMPLOYEE_ID NOT IN (SELECT MANAGER_ID  --> NOT IN에서는 NULL체크 꼭 하기
                             FROM EMPLOYEES
                            WHERE MANAGER_ID IS NOT NULL)
;
-- LEFT OUTER JOIN 사용해서 푸는 방법
SELECT MAN.*
  FROM EMPLOYEES MAN
  LEFT OUTER JOIN EMPLOYEES EMP
    ON MAN.EMPLOYEE_ID = EMP.MANAGER_ID
 WHERE EMP.EMPLOYEE_ID IS NULL 
 ORDER BY MAN.EMPLOYEE_ID
;

-- 부하직원이 없는 사원의 상사를 모두 조회한다.
 SELECT LEVEL 
 	  , EMPLOYEE_ID
      , FIRST_NAME || ' ' || LAST_NAME AS NAME
      , MANAGER_ID 
  FROM EMPLOYEES
  START WITH EMPLOYEE_ID IN (SELECT MAN.EMPLOYEE_ID
  							  FROM EMPLOYEES MAN
 							  LEFT OUTER JOIN EMPLOYEES EMP
    							ON MAN.EMPLOYEE_ID = EMP.MANAGER_ID
    					     WHERE EMP.EMPLOYEE_ID IS NULL)
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID
;  							 

PAGINATION (INLINE VIEW 두번 사용)

-- 85. 가장 적은 연봉을 받는 중 90번 째 부터 100번째를 조회한다.
SELECT *
  FROM (SELECT EMP.*
             , ROWNUM AS RNUM
          FROM (SELECT *
                  FROM EMPLOYEES
                  ORDER BY SALARY ASC) EMP
         WHERE ROWNUM <= 100) EMP2
 WHERE EMP2.RNUM >= 90
;

-- 86. 'PU_CLERK' 직무인 2번째 부터 5번째 사원의 부서명, 직무명을 조회한다.
SELECT *
  FROM (SELECT EMP.*
             , ROWNUM AS RNUM
          FROM (SELECT E.EMPLOYEE_ID
                     , D.DEPARTMENT_NAME
                     , J.JOB_TITLE
                  FROM EMPLOYEES E
                 INNER JOIN DEPARTMENTS D
                    ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
                 INNER JOIN JOBS J
                    ON J.JOB_ID = E.JOB_ID
                  WHERE J.JOB_ID = 'PU_CLERK'
                  ORDER BY J.JOB_ID ASC) EMP
         WHERE ROWNUM <= 5) EMP2
 WHERE EMP2.RNUM >= 2
;