結合で散らばったデータを繋ぐ

목표

내부 조인, 왼쪽 외부 조인, 안티 조인, 자기 조인을 상황에 맞게 골라 쓰고, ON 절과 WHERE 절의 차이를 설명할 수 있게 됩니다.

왜 중요한가

조인에서 결정해야 할 것은 사실 하나뿐입니다. 짝이 없는 행을 남길 것인가 버릴 것인가. 이 결정을 명확히 하지 않으면 리포트 숫자가 조용히 틀립니다.

특히 위험한 지점은 왼쪽 외부 조인 뒤의 WHERE 절입니다. 오른쪽 테이블의 컬럼에 조건을 걸면, 짝이 없어 NULL 이 채워진 행들이 그 조건에서 미지가 되어 통째로 사라집니다. 즉 외부 조인을 썼는데 결과는 내부 조인이 되는 것입니다. 오류도 경고도 없이 조용히 일어나므로, 이 실습에서 두 경우를 나란히 만들어 행 수를 세어 보는 경험이 중요합니다.

또 하나는 팬아웃입니다. 주문 하나에 상세가 셋이면 조인 결과는 세 행이 되고, 그 위에서 주문 금액을 합하면 세 배가 됩니다. 조인이 행 수를 바꾼다는 사실을 늘 의식해야 합니다.

단계

1. 주문과 고객을 이어 order_id, customer_name, total_amount 를 담는 뷰 v_order_customer 를 만듭니다.
2. 모든 고객과 그 고객의 주문 건수를 담는 뷰 v_customer_orders 를 만듭니다. 컬럼은 id, name, order_count 이며 주문이 없는 고객은 0 이어야 하고 고객 수는 400명 그대로여야 합니다.
3. 한 번도 주문하지 않은 고객의 id, name 을 담는 뷰 v_never_ordered 를 만듭니다.
4. 주문 상세와 상품을 이어 order_id, product_name, quantity, unit_price 를 담는 뷰 v_item_detail 을 만듭니다.
5. country 가 KR 인 고객의 status 가 paid 인 주문의 상세를 담는 뷰 v_kr_paid_items 를 만듭니다. 컬럼은 order_id, product_id 로 시작해야 합니다.
6. 모든 고객과 그 고객의 결제 완료 주문 건수를 담는 뷰 v_left_paid 를 만듭니다. 컬럼은 id, paid_count 이며 고객 수는 400명 그대로여야 합니다.
7. 같은 도시에 사는 tier 가 vip 인 고객 쌍을 담는 뷰 v_vip_pairs 를 만듭니다. 컬럼은 a_id, b_id, city 이며 같은 쌍이 두 번 나오면 안 됩니다.
8. status 가 paid, shipped, delivered 중 하나인데 완료된 결제가 없는 주문을 담는 뷰 v_unpaid_orders 를 만듭니다.

참고

• 조인 결과의 행 수를 먼저 세어 보는 습관이 버그를 줄입니다.
• count(*) 와 count(컬럼) 의 차이를 2번에서 직접 확인하세요.
• 흔한 실수 1: 6번에서 AND o.status = 'paid' 를 WHERE 로 옮기면 고객 수가 줄어듭니다.
• 흔한 실수 2: 8번에서 결제 행의 존재만 확인하면 실패한 결제가 있는 주문을 놓칩니다.

주문에 고객 이름 붙이기

주문과 고객을 이어 order_id, customer_name, total_amount 를 담는 뷰 v_order_customer 를 만듭니다.

두 테이블을 잇는 컬럼을 ON 절에 씁니다. 조인 조건을 빠뜨리면 행이 곱으로 늘어납니다.

주문 없는 고객까지 남기기

모든 고객과 그 고객의 주문 건수를 담는 뷰 v_customer_orders 를 만듭니다. 컬럼은 id, name, order_count 이며 주문이 없는 고객은 0 이어야 하고 고객 수는 400명 그대로여야 합니다.

왼쪽 테이블을 모두 남기는 조인을 씁니다. 건수를 셀 때 count(*) 를 쓰면 짝 없는 행도 1이 됩니다.

한 번도 주문하지 않은 고객 찾기

한 번도 주문하지 않은 고객의 id, name 을 담는 뷰 v_never_ordered 를 만듭니다.

존재하지 않음을 확인하는 방법이 여러 가지입니다. NOT EXISTS 가 NULL 에 안전합니다.

세 테이블 잇기

주문 상세와 상품을 이어 order_id, product_name, quantity, unit_price 를 담는 뷰 v_item_detail 을 만듭니다.

조인은 여러 번 이어 붙일 수 있습니다. 각 조인마다 조건을 빠뜨리지 않았는지 확인하세요.

조인 결과에 조건 걸기

country 가 KR 인 고객의 status 가 paid 인 주문의 상세를 담는 뷰 v_kr_paid_items 를 만듭니다. 컬럼은 order_id, product_id 로 시작해야 합니다.

고객 테이블의 조건과 주문 테이블의 조건이 각각 어디에 붙어야 하는지 생각해 보세요.

ON 절과 WHERE 절의 차이 확인하기

모든 고객과 그 고객의 결제 완료 주문 건수를 담는 뷰 v_left_paid 를 만듭니다. 컬럼은 id, paid_count 이며 고객 수는 400명 그대로여야 합니다.

외부 조인에서 오른쪽 테이블 조건을 WHERE 로 옮기면 짝 없는 행이 사라집니다. 전체 고객 수가 유지되는지 세어 보세요.

같은 테이블을 자기 자신과 잇기

같은 도시에 사는 tier 가 vip 인 고객 쌍을 담는 뷰 v_vip_pairs 를 만듭니다. 컬럼은 a_id, b_id, city 이며 같은 쌍이 두 번 나오면 안 됩니다.

같은 테이블에 서로 다른 별칭을 붙입니다. 같은 쌍이 두 번 나오지 않게 하려면 id 사이에 부등호 조건을 겁니다.

결제가 확인되지 않은 주문 찾기

status 가 paid, shipped, delivered 중 하나인데 완료된 결제가 없는 주문을 담는 뷰 v_unpaid_orders 를 만듭니다.

결제 행이 아예 없는 경우와 있지만 실패한 경우를 모두 포함해야 합니다. 존재 조건 안에 상태 조건까지 넣어야 합니다.