데이터분석 SQL - Inner Join, Outer Join, Full Outer Join

Inner Join

• Key 컬럼의 공통 부분(파랑색 음영) 데이터만 Join
• Left / Right Table에만 있는 데이터(주황색 / 초록색 음영)는 Join 되지 않는다.
  

---

Left Outer Join

• Left Table이 기준 테이블이 되며, Key 컬럼의 데이터를 모두 포함하여 Join 된다.
• 이 때, Right Table 중 Left Table 의 Key 컬럼에 없는 데이터는 'NULL' 표시된다.
  

---

Right Outer Join

• Right Table이 기준 테이블이 되며, Key 컬럼의 데이터를 모두 포함하여 Join 된다.
• 이 때, Left Table 중 Right Table 의 Key 컬럼에 없는 데이터는 'NULL' 표시된다.
• 보통 Left Join을 자주 사용!
  

---

Full Outer Join

• Join 하는 두 Table 의 Key 컬럼 데이터를 모두 포함하며, 어느 한 쪽에 해당 Key 컬럼 데이터가 없는 경우, 'NULL' 처리된다.
  

---

예제

1. 주문이 단 한 번도 없는 고객 정보 데이터 추출

SELECT a.customer_id, a.contact_name, b.order_id, b.customer_id
FROM nw.customers a
   LEFT JOIN nw.orders b ON a.customer_id = b.customer_id
WHERE b.customer_id IS NULL;

---

2. 부서 정보와 부서에 소속된 직원명 정보 데이터 추출. 부서가 직원을 가지고 있지 않더라도 부서 정보는 표시되어야함.

SELECT a.*, b.empno, b.ename
FROM hr.dept a
   LEFT JOIN hr.emp b ON a.deptno = b.deptno
ORDER BY 1;

---

3. Madrid에 살고 있는 고객이 주문한 주문 정보를 데이터를 추출.
   고객명, 주문id, 주문일자, 주문접수 직원명, 배송 업체명을 구하되,
   만일 고객이 주문을 한 번도 하지 않은 경우라도 고객 정보는 빠지면 안 됨.
   이 경우 주문 정보가 없으면, 주문 ID를 0으로 나머지는, NULL로 구할 것.

SELECT a.customer_id, a.contact_name, COALESCE(b.order_id,0) AS order_id, b.order_date, c.first_name||' '||c.last_name AS employee_name,
      d.company_name
FROM nw.customers a
   LEFT JOIN nw.orders b ON a.customer_id = b.customer_id
   LEFT JOIN nw.employees c ON b.employee_id = c.employee_id
   LEFT JOIN nw.shippers d ON b.ship_via = d.shipper_id
WHERE a.city = 'Madrid';

주의! 3~4개 Table join시 left join 계속 해줘야 함!! Join 하면서 Null 값이 포함된 데이터를 left join으로 계속 유지해줘야 한다.

---

4. orders_items에 주문번호(order_id)가 없는 order_id를 가진 orders 데이터 찾기.

SELECT *
FROM nw.orders a 
   LEFT JOIN nw.order_items b ON a.order_id = b.order_id
WHERE b.order_id IS NULL;

---

5. orders 테이블에 없는 order_id가 있는 order_items 데이터 찾기.

SELECT *
FROM nw.order_items a
   LEFT JOIN nw.orders b ON a.order_id = b.ORDER_id
WHERE b.order_id IS NULL;

---

6. dept 테이블은 소속 직원이 없는 경우가 존재한다. 하지만 직원은 소속 부서가 없는 경우가 없다.
   데이터를 통해 확인해 보면 다음과 같다.

SELECT a.*, b.empno, b.ename
FROM hr.dept a 
   LEFT JOIN hr.emp b ON a.deptno = b.deptno;

full outer join 테스트를 위해 소속 부서가 없는 테스트용 데이터 생성.

DROP TABLE IF EXISTS hr.emp_test;

CREATE TABLE hr.emp_test AS
SELECT * FROM hr.emp;

SELECT * FROM hr.emp_test;

empno 7934의 소속 부서를 Null로 update

UPDATE hr.emp_test SET deptno = NULL WHERE empno = 7934;

SELECT * FROM hr.emp_test;

full outer join 하여 양 쪽 모두의 집합이 누락되지 않도록 함.

SELECT a.*, b.empno, b.ename
FROM hr.dept a
   FULL OUTER JOIN hr.emp_test b ON a.deptno = b.deptno
ORDER BY 1;