[PostgreSQL] PostgreSQL FULL OUTER 조인

FULL OUTER 조인

• INNER, LEFT OUTER, RIGTH OUTER 조인 집합을 모두 출력하는 조인 방식
• 즉, 두 테이블 간 출력 가능한 모든 데이터를 포함한 집합을 출력

실습

 

 1) A와 B의 테이블을 FRUIT 컬럼 기준으로 FULL OUTER 조인

SELECT 
	A.ID ID_A,
	A.FRUIT FRUIT_A,
	B.ID ID_B,
	B.FRUIT FRUIT_B
FROM BASKET_A A
FULL OUTER JOIN BASKET_B B ON A.FRUIT = B.FRUIT;

 

 2)  A와 B의 테이블을 FRUIT 컬럼 기준으로 FULL OUTER 조인. A.ID가 NULL 혹은 B.ID가 널인 값을 조회.

SELECT 
	A.ID ID_A,
	A.FRUIT FRUIT_A,
	B.ID ID_B,
	B.FRUIT FRUIT_B
FROM BASKET_A A
FULL OUTER JOIN BASKET_B B ON A.FRUIT = B.FRUIT
WHERE A.ID IS NULL OR B.ID IS NULL;

 

 3)  EMPLOYEES 테이블과 DEPARTMENTS 테이블을 FULL OUTER 조인

SELECT
	E.EMPLOYEE_NAME,
	D.DEPARTMENT_NAME
FROM EMPLOYEES E
FULL OUTER JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID;

 

 4)  EMPLOYEES 테이블과 DEPARTMENTS 테이블을 FULL OUTER 조인. 소속한 직원이 없는 부서만 출력.

SELECT 
	E.EMPLOYEE_NAME,
	D.DEPARTMENT_NAME
FROM EMPLOYEES E
FULL OUTER JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE E.EMPLOYEE_NAME IS NULL;

-- RIGHT JOIN 사용
SELECT
	E.EMPLOYEE_NAME,
	D.DEPARTMENT_NAME
FROM EMPLOYEES E
RIGHT JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE E.EMPLOYEE_NAME IS NULL;

 

 5)  EMPLOYEES 테이블과 DEPARTMENTS 테이블을 FULL OUTER 조인. 소속한 부서가 없는 직원만 출력.

SELECT
	E.EMPLOYEE_NAME,
	D.DEPARTMENT_NAME
FROM EMPLOYEES E
FULL OUTER JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE D.DEPARTMENT_ID IS NULL;

-- LEFT JOIN 사용
SELECT
	E.EMPLOYEE_NAME,
	D.DEPARTMENT_NAME
FROM EMPLOYEES E
LEFT JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE D.DEPARTMENT_ID IS NULL;