[2] 데이터 분석 SQL 실습 - Inner Join, Outer Join, Full Outer Join

2일차

---

오늘의 실습 내용 요약
1. 여러 번 조인하는 예제 마무리
2. Outer Join 예제

 

실습 전 스키마 nw 속 데이터 확인

• 주어진 테이블
  • Customers 정보, Orders, Order_items,
  • Products정보 - Inventories 재고 - Warehouses 창고
  • 고객의 Contacts, Employee,  (제품과 재고, 창고와 재고는 1:M관계이지만, 제품과 창고는 M:N 관계이다.)
  • Regions, Countries, Locations
• ERD
  • 한 손님이 여러 개의 주문을 할 수 있음 => Customers : Orders = 1:M
  • 한 직원이 여러 개의 주문을 처리할 수 있음 => Employee : Orders = 1:M
  • Products : Categories = 1:M
  • Products : Supplies = 1:M (여기서는 한 제품은 한 공급자에게만 받는 케이스)
  • Shippers : Orders = 1:M (일반적인 이커머스에서는 M:N)

조인 실습

1. 고객명 Antonio Moreno가 1997년에 주문한 주문정보를 주문id, 주문/배송 일자, 배송주소, 고객주소와 함께 구하기

 

쿼리

• 고객명은 contact_name
•  모두 조인 실습했던 것과 유사, 추가된 부분은 order_date가 1997년 0101부터 1231까지 between으로 입력해주는 것

---

2. Berlin에 살고 있는 고객의 주문정보 - 고객명, 주문id, 주문일자, 주문접수 직원명, 배송업체명 구하기

 

쿼리

• customers 테이블 기준 (from)
  • customer id를 order 테이블과,
  • employee id를 employee 테이블과,
  • shipper id를 shippers테이블과 조인하기

---

3. Beverages 카테고리에 속하는 상품의 id, 상품명, supplier 회사명 정보 구하기

 

쿼리

• categories 테이블을 product 테이블과 category_id로 조인하고, 
• product테이블과 supplier 테이블이 조인 됨

---

4. 고객명 Antonio Moreno이 1997년에 주문한 주문 상품정보 - 고객 주소, 주문 아이디, 주문/배송 일자, 배송 주소 및 주문 상품 아이디, 주문 상품명, 주문 상품별 금액, 주문 상품이 속한 카테고리명, supplier명 구하기

 

쿼리

결과

• 하이라이트된 join절 이후로는 1:M에서 1이 모두 order_items이기 때문에, 최종적으로 order items level로 정리된 모습

---

Inner Join, Outer Join, Full Outer Join 실습

• 이제까지 사용한 조인은 Inner Join
  • left (Outer) Join : left 테이블 전체가 보존된다. 공통되지않은 부분의 level은 남아있되, 조인 대상의 값이 Null로 뜸
    • right (Outer) Join도 같은 결과를 출력하기 때문에 (outer 위치만 다르고) 헷갈리므로 Left만 사용
  • full Outer Join은 모든 level이 남아있고 공통되지 않은 부분에 Null
  • 데이터 정합성 체크시 많이 활용된다. 부모-자식 관계에 있는 테이블의 경우, 자식 테이블에 있는 데 부모 테이블에 없는 그런 경우를 Outer Join으로 찾아낼 수 있음
• "~가 없더라도 출력 .." ~ 테이블 기준 left join!

1. 주문이 단 한번도 없는 고객 정보 구하기

 

쿼리

결과

Null로 표시되는 모습

• from nw.customers : 특정 조건을 만족하는 customers 를 출력하되, 
• customers 테이블에 있으면 order 테이블에 id가 없더라도 출력하라는 의미, 즉 고객 정보 중 주문이 단 한번도 없는 고객을 알 수 있음
• 주문이 없더라도 고객 정보 출력 => 주문 테이블 기준 left join

---

2. 부서정보와 부서에 소속된 직원명을 구하되, 직원이 없는 부서이더라도 표시가 되도록 하기

 

쿼리

결과

• 직원이 없는 부서라도 출력 => 직원 기준 left join

---

3. Madrid에 살고 있는 고객이 주문한 주문 정보 - 고객명, 주문id, 주문일자, 주문접수 직원명, 배송업체명 구하기

단, 주문하지 않은 고객정보도 포함시키고, 주문정보가 없는 경우 주문id는 0으로, 나머지는 Null으로 구하기

 

left join을 여러 번 했을 때, inner join으로 바꾸게 되면 left join한 의미가 없어지게 된다. left join으로 만들어진 테이블에 Null값이 있는 부분은 inner join시 사라지기 때문, 유의하기

쿼리

• coalesce 사용 : 인자로 주어진 컬럼 중 Null이 아닌 첫 번째 값을 반환하는 함수
• order 주문 테이블, employee 직원 테이블, shipper 배송업체 테이블 모두 left join 해주기
• 마지막에 출력할 고객 조건 where절로 추가

---

4. orders 테이블에서 order_items 테이블에 주문번호가 없는 데이터 찾기 (데이터 정합성 체크)

 

orders에 주문 번호가 없는 orders

5. order_items 테이블에서 orders 테이블에 주문번호가 없는 order_id를 가진 데이터 찾기 (데이터 정합성 체크)

 

orders_items에 주문 번호가 있는 orders

• Is Null : SELECT "필드명" FROM "테이블명" WHERE "필드명" IS NULL

---