✍ 인프런 오라클 데이터베이스 강의를 들을 때 조인의 기본적인 문법과 정의에 공부했었다. 이 글에서는 다시 한 번 조인의 정의에 대해서 설명하고, PostgreSQL로 연습했던 데이터베이스 관계에 대한 조인 실습을 작성하려고 한다.

🔍 Join

✍ 관계형 DB에서 가장 기본이자 중요한 기능
✍ 두개 이상의 테이블을 서로 연결하여 데이터를 추출
✍ 관계형 DB에서는 조인을 통해 서로 다른 테이블간의 정보를 원하는 대로 가져올 수 있음

🔍 데이터베이스 관계

✍ 데이터베이스 관계는 1:1, 1:N, M:N이 존재한다. 예를 들어 한 명의 학생은 하나의 신체정보를 갖게 되서 1:1, 한 명의 학생은 여러 강의를 들을 수 있어서 1:N이다. 여기서 문제는 M:N인데, 한 명의 학생은 여러 강의를 들을 수 있고 강의는 여러 학생을 수용한다. M:N 관계를 논리적으로 표현 할 수 있지만, 2개의 테이블로 구현하는 것은 불가능하다. 이 사이에 연결되는 관계 테이블을 넣어서 M:1:N 방법으로 해결해야 한다.

🔍 예제

-- Berlin에 살고 있는 고객이 주문한 주문 정보를 구할것
-- 고객명, 주문id, 주문일자, 주문접수 직원명, 배송업체명을 구할것. 
select a.customer_id, a.contact_name, b.order_id, b.order_date, 
	c.first_name||' '||c.last_name as employee_name, d.company_name
from nw.customers a
	join orders b on a.customer_id = b.customer_id
	join employees c on b.employee_id  = c.employee_id
	join shippers d on b.ship_via = d.shipper_id
where a.city = 'Berlin';

--Beverages 카테고리에 속하는 모든 상품아이디와 상품명, 그리고 이들 상품을 제공하는 supplier 회사명 정보 구할것 
select a.category_id, a.category_name, b.product_id, b.product_name, c.supplier_id, c.company_name 
from nw.categories a 
	join nw.products b on a.category_id = b.category_id
	join nw.suppliers c on b.supplier_id = c.supplier_id
where a.category_name = 'Beverages';

-- 고객명 Antonio Moreno이 1997년에 주문한 주문 상품정보를 고객 주소, 주문 아이디, 주문일자, 배송일자, 배송 주소 및
-- 주문 상품아이디, 주문 상품명, 주문 상품별 금액, 주문 상품이 속한 카테고리명, supplier명을 구할 것. 
select a.contact_name, a.address, b.order_id, b.order_date, b.shipped_date, b.ship_address
	, c.product_id, d.product_name, c.amount, e.category_name, f.contact_name as supplier_name
from nw.customers a
	join nw.orders b on a.customer_id = b.customer_id
	join nw.order_items c on b.order_id = c.order_id
	join nw.products d on c.product_id = d.product_id
	join nw.categories e on d.category_id = e.category_id
	join nw.suppliers f on d.supplier_id = f.supplier_id
where a.contact_name = 'Antonio Moreno'
and b.order_date between to_date('19970101', 'yyyymmdd') and to_date('19971231', 'yyyymmdd')
;

✍ 테이블 간의 관계(1:1, 1:M, M:N)를 잘 파악하여 조인을 하게되면 조인의 결과가 어떤 집합레벨을 만드는지 알 수 있다.

🔍 LEFT/RIGHT/FULL OUTER JOIN

✍ 두 테이블이 합쳐 질때 왼쪽/오른쪽을 기준으로 했느냐에 따라 기준 테이블의 것은 모두 출력되어야 한다고 이해하면 된다.
✍ OUTER JOIN은 조인하는 테이블의 ON 절의 조건 중 한쪽의 데이터를 모두 가져온다

-- 부서정보와 부서에 소속된 직원명 정보 구하기. 부서가 직원을 가지고 있지 않더라도 부서정보는 표시되어야 함. 
select a.*, b.empno, b.ename
from hr.dept a
	left join hr.emp b on a.deptno = b.deptno;

-- 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 as shipper_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';

-- 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;

🔍 CROSS JOIN

✍ 두 테이블에 대한 카테시안 곱(Cartesian Product) 출력

with
temp_01 as ( -- 임시 테이블
select 1 as rnum 
union all
select 2 as rnum
)
select a.*, b.*
from hr.dept a 
	cross join temp_01 b;

📖 reference

인프런 데이터 분석 SQL Fundamentals
https://siyoon210.tistory.com/26
https://inpa.tistory.com/