220708 TIL

국비교육 28일차 Oracle : EQUI JOIN, Multi Table JOIN

1. EQUI JOIN

1) EQUI JOIN(SQL 1992 CODE 방식)

select D.department_id AS 부서번호 
         , department_name AS 부서명     
from employees E, departments D 
where E.department_id = D.department_id; -- join 조건절
-- 테이블 두개를 합칠 때, where절의 조건이 일치하는 행만 가져옴
-- 양쪽 테이블 모두에 존재하는 컬럼명은 반드시 테이블 소속을 밝혀야 한다. 

• HR테이블에서 Kimberely Grant 누락없이 출력되게 하기

select D.department_id AS 부서번호 
     , department_name AS 부서명
     , employee_id AS 사원번호
     , first_name || ' ' || last_name AS 사원명
from employees E, departments D 
where E.department_id = D.department_id(+);     

• (+)가 안붙은 테이블인 E의 모든 행(107)을 출력해주고 나서 그 다음에 where E.department_id = D.department_id 조건에 만족하는 행들을 보여준다.

2) EQUI JOIN을 SQL 1999 CODE

(1) Inner Join(내부조인) ==> SQL 1992 CODE 방식에서 (+) 가 없는 것.
(2) Outer Join(외부조인) ==> SQL 1992 CODE 방식에서 (+) 가 있는 것.
- Left Outer Join, Right Outer Join, Full Outer Join

• INNER JOIN

-- from employees E INNER JOIN departments D  -- INNER는 생략이 가능함
from employees E JOIN departments D 
ON E.department_id = D.department_id; -- JOIN 조건절

• HR 테이블 예시

select E.department_id AS 부서번호  
     , department_name AS 부서명
     , employee_id AS 사원번호
     , first_name || ' ' || last_name AS 사원명
     , salary AS 기본급여
-- from employees E LEFT OUTER JOIN departments D -- outer는 생략가능함
from employees E LEFT JOIN departments D 
ON E.department_id = D.department_id;
-- 왼쪽에 있는 employees 테이블의 모든 행(107)을 출력한 다음에 
--ON E.department_id = D.department_id 조건에 맞는 행들을 찾아서 
--짝을 지어서 보여주라는 것이다. 

• SQL 1999에서 JOIN만 있으면 Inner Join
• Inner Join의 경우, NULL은 연산자를 사용할 수 없기 때문에 NULL 값은 빠지게 된다.
• null은 보고 싶은데 왼쪽 null만 보고 싶으면 Left join, 오른쪽만 보고싶으면 Right join, 양쪽 null 다 보고 싶으면 Full join
• Outer은 생략 가능

3) With절을 사용한 inline view

• WITH절은 오라클에서만 사용이 가능하다.
  
  

-- 부서번호가 10, 30, 50번 부서에 근무하는 사원들의 
--'부서번호    사원번호    사원명     기본급여    부서평균기본급여 
-- 부서평균급여와의차액  부서내기본급여등수   전체기본급여등수' 값이 나오도록 하세요. 
        
WITH A as (
    select department_id   
    
         , employee_id
         , first_name || ' ' || last_name AS ENAME
         , salary  
         , rank() over(partition by department_id
                       order by salary desc) AS department_rank
         , rank() over(order by salary desc) AS entire_rank
    from employees
    where department_id in(10,30,50)
), 
B as (
    select department_id
             , trunc(avg(salary)) AS AVG_DEPTID_SALARY
    from employees
    where department_id in(10,30,50)
    group by department_id
) 
select A.department_id as 부서번호 
     , employee_id as 사원번호
     , ENAME as 사원명
     , salary as 기본급여
     , B.AVG_DEPTID_SALARY as 부서평균기본급여
     , salary - AVG_DEPTID_SALARY as 부서평균급여와의차액
     , department_rank AS 부서내기본급여등수
     , entire_rank AS 전체기본급여등수
FROM A JOIN B
ON A.department_id = B.department_id;

4) NON-EQUI JOIN

• 조인조건절에 사용되는 컬럼의 값이 특정한 범위에 속할 때 사용하는 것이 NON-EQUI JOIN이다.
  NON-EQUI JOIN에서는 조인조건절에 = 을 사용하는 것이 아니라 between A and B를 사용한다.

2. Multi Table JOIN (다중 테이블 조인)

• 3개 이상의 테이블(뷰)을 가지고 조인 시켜주는 것이다.
• 다중테이블 JOIN 시 시작을 부모테이블부터 시작하는 것이 편하다
• 제일 큰 범위에서 시작하는 것이 생각하기 쉬움(regions -> employees)

-- 부서번호가 없는 킴벨리까지 나오게 107개 행 출력하기
select *
from REGIONS R
JOIN COUNTRIES C
ON R.region_id = C.region_id
JOIN LOCATIONS L
ON C.country_id = L.country_id
JOIN DEPARTMENTS D
ON L.location_id = D.location_id
RIGHT JOIN EMPLOYEES E -- 오른쪽만큼은 전부 다 나와야하기 때문에 right join을 사용하여야 한다. 
ON D.department_id = E.department_id
order by 1;