OUTER JOIN

 

JOB_HISTORY 테이블 데이터 

 

 

 

 

EMPLOYEES 테이블의 데이터 

여기서 LEFT RIGHT JOIN을 비교해본다 

 

select J.EMPLOYEE_ID, J.START_DATE , J.END_DATE, E.FIRST_NAME 

  FROM EMPLOYEES E LEFT JOIN JOB_HISTORY J ON E.EMPLOYEE_ID = J.EMPLOYEE_ID 

LEFT JOIN을하면 쿼리상으로 EMPLOYEES 기준으로 정렬이 된다.  

따라서 JOB_HISTORY 는 10개의 테이블 내용이 

EMPLOYEES는 더 많다 따라서 더 많은거 기준으로 정렬이 된다.  

그래서 NULL이 나오게 되는것이다  

이걸 작은테이블 기준으로 정렬하게되면 

 

 

 

  select J.EMPLOYEE_ID, J.START_DATE , J.END_DATE, E.FIRST_NAME 

  FROM EMPLOYEES E RIGHT JOIN JOB_HISTORY J ON E.EMPLOYEE_ID = J.EMPLOYEE_ID 

이렇게보면 JOB_HISTROY 기준으로 정렬하니깐 NULL이 안나오게 되는것이다. 

 

 

 

그리고 이 두가지를 합한것들이 FULL OUTER JOIN 이다 

 

select J.EMPLOYEE_ID, J.START_DATE , J.END_DATE, E.FIRST_NAME 

  FROM EMPLOYEES E FULL OUTER  JOIN JOB_HISTORY J ON E.EMPLOYEE_ID = J.EMPLOYEE_ID 

 

 

매칭되는것만  저런식으로 나오게된다.