FULL JOIN

-- FULL JOIN : LEFT와 RIGHT JOIN 결합형태

 

-- 인천에 사는 사람 쉘렉
   SELECT num, name, buseo, city FROM insa
   WHERE city='인천';

-- 개발부에 사는 사람 쉘렉  
   SELECT num, name, buseo, city FROM insa
   WHERE buseo='개발부';

 

-- EQUI JOIN
SELECT a.num, b.num, a.name, b.name, a.city, b.city, a.buseo, b.buseo
  FROM
  (
    SELECT num, name, buseo, city FROM insa
      WHERE city='인천'
  ) a
  JOIN
  (
    SELECT num, name, buseo,city FROM insa
      WHERE buseo='개발부'
  ) b
  ON a.num=b.num;
 
--LEFT OUTER JOIN
SELECT a.num, b.num, a.name, b.name, a.city, b.city, a.buseo, b.buseo
  FROM
  (
    SELECT num, name, buseo, city FROM insa
      WHERE city='인천'
  ) a
  LEFT OUTER JOIN
  (
    SELECT num, name, buseo,city FROM insa
      WHERE buseo='개발부'
  ) b
  ON a.num=b.num; --왼쪽에 있는건 모두 출력 오른쪽은 조건에 맞는것만
 
--RIGHT OUTER JOIN
SELECT a.num, b.num, a.name, b.name, a.city, b.city, a.buseo, b.buseo
  FROM
  (
    SELECT num, name, buseo, city FROM insa
      WHERE city='인천'
  ) a
  RIGHT OUTER JOIN
  (
    SELECT num, name, buseo,city FROM insa
      WHERE buseo='개발부'
  ) b
  ON a.num=b.num; --왼쪽에 있는건 조건에 맞는것만 오른쪽은 모두 출력
 
-- FULL JOIN(FULL OUTER JOIN)
SELECT a.num, b.num, a.name, b.name, a.city, b.city, a.buseo, b.buseo
  FROM
  (
    SELECT num, name, buseo, city FROM insa
      WHERE city='인천'
  ) a
  FULL OUTER JOIN
  (
    SELECT num, name, buseo,city FROM insa
      WHERE buseo='개발부'
  ) b
  ON a.num=b.num; --왼쪽 오른쪽 모두 출력