[SQL Fundamentals] 02. 조인(Join) - Outer 조인, Non Equi 조인, Cross 조인

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

 

섹션2. 조인(Join) - Outer 조인, Non Equi 조인, Cross 조인

2-1. Inner 조인, Left/Right Outer 조인, Full Outer 조인의 이해

Inner Join

 

from A
	join B on A.key = B.key

 

inner를 생략하고 (inner) join으로도 사용할 수 있다.

join의 방향은 LEFT -> RIGHT이든 LEFT <- RIGHT이든 상관없다.

위의 예시에서 LEFT 테이블과 RIGHT 테이블의 관계가 M:1이므로 조인 결과 데이터 집합 레벨은 LEFT 테이블의 집합 레벨을 따르게 되며 결과적으로는 두 테이블의 키값이 일치하는 데이터만 걸러져서 나온다.

 

섹션1의 실습 예제가 모두 inner join의 예시이므로 이번 글에서는 inner join의 실습 예제는 생략한다.

 

(참고) inner join 실습 예제

https://moominie.tistory.com/33

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

 

Left Outer Join

 

from A
	left join B on A.key = B.key

 

outer를 생략하고 left (outer) join으로도 사용할 수 있다.

join의 방향은 LEFT -> RIGHT로 정해지며, 이때 LEFT 테이블이 Outer 테이블이 되고, RIGHT 테이블이 Inner 테이블이 된다.

위의 예시에서는 LEFT 테이블과 RIGHT 테이블의 관계가 M:1이므로 조인 결과 집합은 LEFT (Outer) 테이블의 데이터 집합 레벨을 따라가면서 RIGHT 테이블에 데이터가 존재하지 않는 LEFT 테이블의 데이터들도 보존된다.

 

Right Outer Join

 

from A
	right join B on A.key = B.key

 

Left Outer Join의 반대 개념이다.

outer를 생략하고 right (outer) join으로도 사용할 수 있다.

join의 방향은 LEFT <- RIGHT로 정해지며, 이때 RIGHT 테이블이 Outer 테이블이 되고, LEFT 테이블이 Inner 테이블이 된다.

위의 예시에서 LEFT 테이블과 RIGHT 테이블의 관계가 M:1이므로 조인 결과 집합은 LEFT (Inner) 테이블의 데이터 집합 레벨을 따라가면서 LEFT 테이블에 데이터가 존재하지 않는 RIGHT 테이블의 데이터도 보존된다.

 

(참고)

right join은 LEFT 테이블과 RIGHT 테이블의 위치만 바꾸면 left join과 결과가 똑같다.

아래 두 join의 결과는 같다.

from A
	right join B on A.key = B.key

 

from B
	left join A on B.key = A.key

 

Full Outer Join

 

from A
	full join B on A.key = B.key

 

Online보다는 Batch에서 사용하는 경우가 많다.

outer를 생략하고 full (outer) join으로도 사용할 수 있다.

위의 예시에서 LEFT 테이블과 RIGHT 테이블의 관계가 M:1이므로 조인 결과 데이터 집합 레벨은 LEFT 테이블의 데이터 집합 레벨을 따라가면서 서로의 테이블에 데이터가 존재하지 않는 데이터들까지 모두 보존된다.

 

2-2. Outer 조인 실습

실습예제

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

select c.*
from customers c 
    left join orders o on c.customer_id = o.customer_id
where o.order_id isnull;

 

단 한번도 주문을 하지 않았더라도 고객 테이블의 정보는 보존이 돼야 한다.

따라서 customers 테이블을 LEFT (Outer) 테이블로 하여 left join을 하게 되면 orders 테이블에 데이터가 없더라도 customers 테이블의 데이터가 온전히 보존된다.

 

2. 부서정보와 부서에 소속된 직원명 정보 구하기. 부서가 직원을 가지고 있지 않더라도 부서정보는 표시되어야 함

select d.*, e.empno, e.ename 
from hr.dept d 
    left join hr.emp e on d.deptno = e.deptno
order by d.deptno;

 

부서에 소속된 직원이 없더라도 부서 테이블의 정보는 보존이 돼야 한다.

따라서 dept 테이블을 LEFT (Outer) 테이블로 하여 left join을 하게 되면 emp 테이블에 데이터가 없더라도 dept 테이블의 데이터가 온전히 보존된다.

 

아래와 같이 emp 테이블을 LEFT (Outer) 테이블로 하여 right join을 하더라도 결과는 동일하게 나온다.

select d.*, e.empno, e.ename
from hr.emp e 
    right join hr.dept d on e.deptno = d.deptno 
order by d.deptno;

 

3. Madrid에 살고 있는 고객이 주문한 주문 정보를 구할 것.

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

 

(나의 생각)

select c.contact_name as customer_name, coalesce(o.order_id,0) as order_id, o.order_date, concat(e.first_name, ' ', e.last_name) as employee_name, s.company_name as shipper_name
from customers c 
    left join orders o on c.customer_id = o.customer_id
    join employees e on o.employee_id = e.employee_id
    join shippers s on o.ship_via = s.shipper_id
where c.city = 'Madrid'
order by c.customer_id, o.order_id;

 

주문 정보가 없더라도 고객정보는 보존이 돼야 하므로 customers 테이블을 LEFT (Outer) 테이블로 하여 orders 테이블을 left join 했다.

그런데 orders 테이블의 employee 정보나 shipper 정보가 employees 테이블과 shippers 테이블에 항상 존재하므로 employees, shippers 테이블을 그냥 inner join으로 걸어주었는데 이게 잘못된 판단이었다.

주문 정보가 없는 고객의 경우 orders 테이블의 모든 데이터가 NULL 값으로 나온다.

 

즉, employees 테이블과의 join 키 값이 되는 employee_id도 NULL이기 때문에 inner join을 할 경우 해당 고객의 데이터는 제외된다.

shippers 테이블도 마찬가지로 join 키 값이 되는 ship_via가 NULL이기 때문에 inner join을 할 경우 해당 고객의 데이터가 제외된다.

따라서 주문 정보가 없는 고객정보도 보존되기 위해서는 employees, shippers 테이블도 모두 left join을 걸어주어야 한다.

 

(정답)

select c.contact_name as customer_name, coalesce(o.order_id,0) as order_id, o.order_date, e.first_name || ' ' || e.last_name as employee_name, s.company_name as shipper_name
from customers c 
    left join orders o on c.customer_id = o.customer_id
    left join employees e on o.employee_id = e.employee_id
    left join shippers s on o.ship_via = s.shipper_id
where c.city = 'Madrid'
order by c.customer_id, o.order_id;

 

주문 정보가 없는 고객정보가 보존되면서 원하는 결과가 나왔다.

 

COALESCE(인자1, 인자2)

postgreSQL, MySQL, Oracle 공통 함수

인자1의 값이 NULL이 아니면 인자1로, 인자1의 값이 NULL인 경우에는 인자2로 나온다.

 

(참고)

IFNULL(인자1, 인자2)

MySQL 함수

COALESCE와 마찬가지로 인자1의 값이 NULL이 아니면 인자1로, 인자1의 값이 NULL인 경우에는 인자2로 나온다.

 

NVL(인자1, 인자2)

Oracle 함수

COALESCE와 마찬가지로 인자1의 값이 NULL이 아니면 인자1로, 인자1의 값이 NULL인 경우에는 인자2로 나온다.

 

NVL(인자1, 인자2, 인자3)

Oracle 함수

인자1의 값이 NULL이 아니면 인자2로, 인자1의 값이 NULL이면 인자3으로 나온다.

 

COALESCE(인자1, 인자2, 인자3)

인자1의 값이 NULL이면 인자2로, 인자2의 값도 NULL이면 인자3으로 나온다.

 

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

select *
from orders o 
    left join order_items oi on o.order_id = oi.order_id
where oi.order_id isnull;

 

실제 업무에서 부모 테이블에 데이터가 있는데 자식 테이블에 데이터가 없는 경우가 있는지 정합성을 체크하기 위해 위와 같이 left outer join을 쓰는 경우가 많다.

 

2-3. Full Outer 조인 실습

실습예제

1. 소속 직원이 없는 부서 정보를 보존하고, 소속 부서가 없는 직원 정보도 보존하여 양쪽 모두의 집합이 누락되지 않도록 데이터 추출하기

select d.deptno, d.dname, et.empno, et.ename
from dept d 
    full outer join emp_test et on d.deptno = et.deptno
order by d.deptno, et.empno;

 

2-4. Non Equi 조인과 Cross 조인의 이해

Equi Join

조인 시 연결하는 키 값이 서로 같은 경우 (즉, =로 연결하는 경우)

여태까지 했던 조인 실습 모두 Equi Join에 속한다.

 

Non Equi Join

두 테이블을 연결할 때 꼭 조인 키 값이 일치하는 경우가 아니더라도 조건에 부합한다면 얼마든지 연결이 가능하다.

즉, 키 값으로 연결 시 =이 아닌 다른 연산자(between, >, >=, <, <=)를 사용하는 조인을 말한다.

 

(예시) Between Join

- 직원정보와 급여등급 정보 가져오기

 

select e.empno, e.ename, e.sal, s.grade
from emp e 
    join salgrade s on e.sal between s.losal and s.hisal
order by e.empno;

 

Cross Join (Cartesian Product Join)

조인 컬럼없이 두 테이블 간 가능한 모든 연결을 결합하는 조인 방식

조인 결과 집합은 두 테이블의 데이터 건수를 곱한 결과만큼 나온다.

단, where 조건으로 걸러질 경우 곱셈 결과 데이터 건수와 일치하지 않을 수도 있다.

 

(예시)

 

select a.*, b.*
from table a
    cross join table b;

 

2-5. Non Equi 조인과 Cross 조인 실습

실습예제

1. 직원 급여의 이력정보를 나타내며, 해당 급여를 가졌던 시작 시점에서의 부서번호도 함께 가져올것

select * 
from hr.emp_salary_hist a
    join hr.emp_dept_hist b on a.empno = b.empno and a.fromdate between b.fromdate and b.todate;

 

아래와 같이 급여 시작 시점 조건을 join 조건이 아닌 where절에 두어도 결과는 동일하다.

select * 
from hr.emp_salary_hist a
    join hr.emp_dept_hist b on a.empno = b.empno
where a.fromdate between b.fromdate and b.todate;

 

(TODO) 두 쿼리의 성능은?