[SQL Fundamentals] 01. 조인(Join) - 조인 기반 메커니즘

🖥️ 데이터 분석 SQL Fundamentals (권철민, 인프런)

 

섹션1. 조인(Join) - 조인 기반 메커니즘

1-1. 조인(Join) 개요

조인(Join)

RDBMS(Relational DBMS, 관계형 DBMS)에서 가장 기본이자 중요한 기능

두 개 이상의 테이블을 서로 연결하여 데이터를 추출할 때 사용하는 기능이다.

두 테이블이 join 키 값으로 연결되면 원하는 데이터를 얼마든지 가져올 수 있다.

 

1-2. 조인 시 데이터 집합 레벨의 변화 이해

조인 시 데이터 집합 레벨의 변화

조인 컬럼을 기준으로 1인 집합(해당 데이터가 unique하게 존재)과 M(해당 데이터가 중복으로 존재)인 집합이 있을 때, 조인 결과는 M 집합이 된다.

즉, 1:M 조인 시 결과 집합은 M 집합의 레벨을 그대로 유지한다.

물론 where 조건 등으로 걸러지면 조인 결과의 데이터 건수는 M 집합의 데이터 건수와 동일하지 않을 수는 있다.

 

1-3. 조인 실습

강의 실습용 HR 스키마의 ERD

 

이미지 출처 : 강의자료

 

Master 테이블 : 기준 정보를 가진다.

이력 테이블 : 이력을 관리한다.

 

실습 예제

1. 부서명 SALES와 RESEARCH의 소속 직원들의 부서명, 직원번호, 직원명, JOB 그리고 과거 급여 정보 추출

select d.dname, e.empno, e.ename, e.job, esh.fromdate, esh.todate, esh.sal
from dept d 
    join emp e on d.deptno = e.deptno 
    join emp_salary_hist esh on e.empno = esh.empno 
where d.dname in ('SALES', 'RESEARCH')
order by d.dname, e.empno, esh.fromdate;

 

조인 결과 데이터 집합 레벨은 어떻게 변화할까?

dept와 emp 테이블의 관계가 1:M이고, 다시 emp와 emp_salary_hist 테이블의 관계가 1:M이다.

따라서 조인 결과 데이터 집합 레벨은 emp_salary_hist를 그대로 따라간다.

 

2. 부서명 SALES와 RESEARCH의 소속 직원들의 부서명, 직원번호, 직원명, JOB 그리고 과거 급여 정보중 1983년 이전 데이터는 무시하고 데이터 추출

select d.dname, e.empno, e.ename, e.job, esh.fromdate, esh.todate, esh.sal
from dept d 
    join emp e on d.deptno = e.deptno 
    join emp_salary_hist esh on e.empno = esh.empno 
where d.dname in ('SALES', 'RESEARCH')
and esh.fromdate >= to_date('19830101', 'yyyymmdd') 
order by d.dname, e.empno, esh.fromdate;

 

TO_DATE('문자열', '날짜 형식')

오라클 문자열(varchar) 데이터를 날짜(date) 타입으로 변환하는 함수

(예) TO_DATE('19830101', 'yyyymmdd')

 

3. 부서명 SALES와 RESEARCH 소속 직원별로 과거부터 현재까지 모든 급여를 취합한 평균 급여

with temp_01
as
(select d.dname, e.empno, e.ename, esh.fromdate, esh.todate, esh.sal 
from dept d
    join emp e on d.deptno = e.deptno
    join emp_salary_hist esh on e.empno = esh.empno
where d.dname in ('SALES', 'RESEARCH')
order by d.dname, e.empno, esh.fromdate
)
select empno, max(ename) as ename, avg(sal) as avg_sal
from temp_01
group by empno;

 

쿼리 수행 결과 데이터 집합 레벨을 어떻게 변화할까?

먼저 조인 결과로는 실습 예제 1에서 살펴본 바와 같이 emp_salary_hist의 데이터 집합 레벨을 따르게 된다.

그리고 나서 group by를 수행하면서 데이터 집합 레벨은 empno 레벨로 바뀌게 된다.

 

(주의) group by 수행 시 select 절에는 group by에 명시된 컬럼과 집계 함수(aggregate function)만 올 수 있다.

 

1-4. 데이터 연결 관계 파악하기

이미지 출처 : 강의자료

 

대부분은 1:M 관계다.

CUSTOMERS와 ORDERS의 관계는 1:M이며, EMPLOYEES와 ORDERS의 관계도 1:M이다.

 

그런데 PRODUCTS와 ORDERS의 관계, 혹은 PRODUCTS와 WAREHOUSE의 관계는 M:N이다.

예를 들어 상품 A는 주문 1에도 있을 수 있고, 주문 2에도 있을 수 있다.

마찬가지로 주문 1에는 상품 A가 있을 수도 있고, 상품 B가 있을 수도 있다.

 

이럴 경우 보통 중간에 관계테이블을 두어 관계를 풀어낼 수 있다.

PRODUCTS와 ORDERS의 사이에는 관계테이블 ORDER_ITEMS가 있다.

마찬가지로 PRODUCTS와 WAREHOUSES 사이에는 관계테이블 INVENTORIES가 있다.

관계테이블로 M:N 관계를 풀어내는 방식은 다음과 같다.

주문 1에 상품 A의 주문수량 1개, 주문 1에 상품 B의 주문수량 3개, 주문 2에 상품 A의 주문수량 10개, 주문 2에 상품 B의 주문수량 5개 라고 가정하자. 

이때, PRODUCTS와 ORDER_ITEMS의 관계는 1:M, ORDERS와 ORDER_ITEMS의 관계도 1:M이다.

PRODUCTS와 ORDERS 테이블 사이에 관계테이블 ORDER_ITEMS를 두어 M:N 관계를 풀어냈다.

 

강의 실습용 nw 스키마

 

이미지 출처 : 강의자료

 

1-5. 조인 실습

실습 예제

1. 고객명 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')
;

 

조인 결과 데이터 집합 레벨은 어떻게 변할까?

customer와 orders가 1:M, orders와 order_items가 1:M 관계다.

products와 order_items도 1:M 관계다.

categories와 products는 1:M, suppliers와 products도 1:M 관계이므로 결국 데이터 집합 레벨을 order_items를 따라가게 된다.

따라서 조인 결과 데이터는 order_items의 PK인 order_id, product_id를 키값으로 가진다.

최종적으로는 where절에 의해 제한된 건수의 결과 데이터가 나온다.

 

결과