[SQL] 4. SUBQUERY, JOIN

sql쿼리문을 작성하다 보면 연산을 여러번 해야 되거나 이러한 결과를 다른 연산이나 조건문에 사용하고 싶을때 이를 계속 반복해서 적기에는 비효율적이게 된다. 그리고 쿼리를 작성하는 과정에서 필요한 데이터가 여러 테이블에 나눠져 있을때 이를 한번에 조회하고 싶은 상황이 발생할 수 있다. 오늘은 위와 같은 상황에서 사용할 수 있는 함수들에 대해서 배워볼 것이다.

1. SUBQUERY

여러번의 연산을 한 번의 SQL문으로 수행하기 위해 사용하는 함수이다. 또한 조건문에 연산 결과를 사용해야 할때나 조건에 쿼리 결과를 사용하고 싶을 때 등 여러 방면에서 사용하는 함수이다.

기본형태

쿼리 안에 서브(sub)로 들어간 구문으로 이해하면 쉽다.

# 1
select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) 결과명

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

예제

1) 음식의 단가를 구하시오

select price/quantity
from
(
select price, quantity
from food_orders
)a

이때 주의할 점은 from 절에 서브쿼리를 사용하였을 경우 꼭 alias(별명)을 지정해주어야 한다. 그렇지 않을 경우 아래와 같은 에러가 발생한다.
정말 많이 실수하는 유형이니 참고하자(특히 나...)

SQL Error [1248][42000]: Every derived table must have its own alias

2) 음식 준비 시간이 25분을 넘어가면 그 초과 시간만큼을 나타내주는 결과를 생성하시오

select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time
from 
(
select order_id, restaurant_name, food_preparation_time-25 over_time
from food_orders
) a

2. JOIN

필요한 데이터가 꼭 하나의 테이블에 존재하란 법은 없다. 여러 테이블에 원하는 데이터가 나뉘어져 있을 경우가 존재한다. 이때 사용하는 것이 JOIN 함수이다. 이때 두 테이블을 조인할때 두 테이블에 공통적으로 존재하는 속성이 존재해야 한다.

기본 형태

조인의 형태에 따라 left join, right join, inner join, outer join 등의 다양한 조인이 존재한다. 각각 상황에 맞는 조인을 사용하면 된다. 각각 조인에 따른 쿼리문 작성 방법은 아래와 같이 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.공통컬럼명

예제

1) 한국 음식의 주문id, 식당명, 가격, 결제 타입, 수수료를 나타내시오
-> 주문id와 식당명, 가격은 food_orders 테이블에, 결제타입과 수수료는 payments 테이블에 존재한다
-> 이 두 테이블에 공통적으로 존재하는 컬럼은 order_id로, 이를 기준으로 조인한다

select f.order_id, f.restaurant_name, f.price, p.pay_type, p.vat
from food_orders f left join payments p on f.order_id = p.order_id 
where cuisine_type = 'Korean'

숙제

식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation

• 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
• 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상

select restaurant_name, 
	   case when avg_age >=50 then '50세이상'
			when avg_age >=40 then '40대'
			when avg_age >=30 then '30대'
			else '20대 이하' end age_group,
	   case when avg_price > 30000 then '30000초과'
		   when avg_price > 10000 then '~30000' 
		   when avg_price > 5000 then '~10000'
		   else '~5000' end price_group
						
from 
(
select f.restaurant_name, avg(f.price) avg_price, avg(c.age) avg_age
from food_orders f inner join customers c on f.customer_id = c.customer_id 
group by 1
) a
order by 1