Subquery

서브쿼리를 사용하는 경우는 다음과 같다.

• 여러 번의 연산을 수행해야 할 때
• 조건문에 연산 결과를 사용해야 할 때
• 조건에 Query 결과를 사용하고 싶을 때

쿼리 안에 서브로 다른 쿼리가 들어간다.

select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
) a

select column1, column2
from table1
where column1 = (select col1 from table2)

사용 예제

-- 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기
select restaurant_name, price_per_plate * ratio_of_add "수수료"
from (
	select restaurant_name,
	       case 	when price_per_plate < 5000 then 0.005
					when price_per_plate < 20000 then 0.01
					when price_per_plate < 30000 then 0.02
					else 0.03 end "ratio_of_add",
			price_per_plate
	from (
		select restaurant_name, avg(price / quantity) "price_per_plate"
		from food_orders
		group by 1
	) a
) b;

음식점별로 그룹화해서 평균단가를 구하는 서브쿼리를 작성한다. 해당 쿼리를 활용해 각 음식점의 수수료를 계산한다.

-- 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기 
select restaurant_name, total_order_quantity, total_price,
	   case 	when total_order_quantity <= 5 then 0.1
	   			when total_order_quantity > 15 and total_price >= 300000 then 0.005
	   			else 0.01 end "수수료 할인율"
from (
	select restaurant_name, sum(quantity) "total_order_quantity", sum(price) "total_price"
	from food_orders
	group by 1
) a;

음식점별로 그룹화해서 주문수량과 가격의 총합을 구하는 서브쿼리를 작성한다. 이 데이터를 기반으로 각 음식점의 수수료를 계산한다.

JOIN

필요한 데이터가 하나의 테이블에 모여있지 않을 수 있다. 이 때 2개 이상의 테이블을 엮어서 데이터를 불러올 수 있다. JOIN은 엑셀의 VLOOKUP과 유사하다.

종류

두 테이블을 조인하는 방법은 종류가 많다.
그 중 LEFT JOIN은 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회한다.
INNER JOIN은 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회한다.

-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명

-- INNER JOIN
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

사용 예제

-- 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
select fo.order_id, fo.restaurant_name, fo.price, p.pay_type, p.vat
from food_orders fo
left join payments p on fo.order_id = p.order_id
where fo.cuisine_type = "Korean";

food_orders, payments 두 개의 테이블을 order_id 컬럼을 기준으로 left join 한다. left join이기 때문에 주문 정보는 있어도 고객 정보가 없을 수 있다.

-- 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
select sub.cuisine_type, sub.price, sum(sub.price) "원래 가격", sum(sub.price - discount) "할인 적용 가격", sum(sub.discount) "할인 가격"
from (
	select fo.cuisine_type, fo.price, c.age, fo.price * ((c.age - 50) * 0.005) "discount"
	from food_orders fo
	left join customers c on fo.customer_id = c.customer_id
	where c.age >= 50
) sub
group by 1
order by 5 desc;

서브쿼리와 join을 함께 사용한 예시다. 서브쿼리에서 JOIN을 수행하고 50세 이상의 고객이 주문한 경우에는 할인을 적용한다. 이렇게 할인된 가격과 할인이 적용된 가격의 합을 음식점별로 구하는 스크립트다.