[데이터분석 부트캠프] SQL #5. JOIN, NULL 제외

* JOIN

{A: 1 2 3 4 5}

{B: 2 3 4 5 6}

- A LEFT JOIN B = 1 2 3 4 5

- A RIGHT JOIN B = 2 3 4 5 6 

- A INNER JOIN B = 2 3 4 5

 

Ex. 한국 음식의 주문별 결제 수단과 수수료율을 조회하기

      (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)

      *결제 정보가 없는 경우도 포함하여 조회

select a.order_id, a.restaurant_name, a.price, b.pay_type, b.vat

from food_orders a left join payments b

on a.order_id=b.order_id

where cuisine_type='Korean'

 

* NULL 제외

Select restaurant_name,

            avg(rating) average_of_rating,

            avg(if(rating<>'Not given', rating, null)) average_of_rating2

from food_orders

group by 1

 

select a.order_id, a.customer_id, a.restaurant_name, a.price, b.name, b.age, b.gender

from food_orders a left join customers b

on a.customer_id=b.customer_id

where b.customer_id is not null

 

* NULL 대체

select  a.order_id,

            a.customer_id,

            a.restaurant_name,

            a.price,

            b.name,

            b.age,

             coalesce(b.age, 20) "null 제거",

            b.gender

from food_orders a left join customers b

on a.customer_id=b.customer_id

where b.age is null