SQL 실무 기초 (4) - Subquery, Join

이번 주차에는 Subquery와 Join에 대해 공부했으며, 이 연산은 이런 상황에서 어울린다.

 

1. 많은 연산을 한번에 수행해야 할 경우, 너무 길어지는 Query를 나눌 수 있는 방법이 없을까?

2. 연산한 결과를 다른 연산에도 사용해야 하는데, 같은 코드를 반복해서 적지 않아도 되는 방법이 있을까?

3. 출력해야 하는 데이터가 여러 테이블에 나누어져 있는 경우 어떻게 데이터를 서로 다른 테이블에서 가져와 사용할 수 있을까?

 

그러면, 먼저 Subquery에 대해 알아보자!

 

 

Subquery는 말 그대로 쿼리 안에 다른 쿼리가 있는 것을 뜻한다.

모든 Query별로 연산이 한번씩만 필요하다면 얼마나 좋을까, 하지만 인생은 결코 그리 간단하지 않다..

 

예를 들어 놀이공원에서 티켓 값을 계산해야 하는 상황을 생각해보자. 

티켓 값은 시즌 별로 달라질 수밖에 없고, 연령대별로 값을 다르게 받는다. 그리고 입장 시간에 따라서도 티켓 값은 변한다! 이렇게 다양한 상황에서 다르게 적용되는 티켓 값을 고려하려면, 그만큼 조건문이 많이 사용될 수밖에 없다.

 

먼저 빠르게 기본 구조를 살펴보자!

select column1, special_column

from

( /* subquery */

select column1, column2 special_column

from table1

) a

 

여기에서 ( ) 안에 있는 것이 Subquery인데, 내용을 보면 table1 에서 column1, column2를 가져오고 그것을 바깥 쿼리에서 사용하고 있다. 이렇게 쿼리 안에 다른 쿼리를 적어 줘서, 복잡한 계산을 한 과정을 거쳐서 수행하기 때문에 복잡도를 조금 낮출 수 있다는 장점이 있다.

 

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

 

Subquery가 적용된 구문을 읽을 땐 가장 안쪽의 query부터 읽는 것이 올바르다. 

안쪽 query를 보면, 주문 ID, 가게 이름, 초과 시간(음식 준비 시간 - 25분)을 계산한다.

그리고 바깥 query를 보면 그래서 그 초과 시간이 0 이상인지를 확인해서 TRUE면 초과 시간을 출력하고, FALSE면 0을 출력한다.

 

두번째 예제를 보자. 음식 타입별로 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점 수 별 수수료들을 산정해야 하는 상황이 있다고 가정하자.

 

그리고 음식점 수가 5개 이상이고 주문수가 30개 이상일 때: 수수료 0.05%

음식점수 5개 이상, 주문수 30개 미만: 수수료 0.08%

음식점수 5개 미만, 주문수 30개 이상: 수수료 1%

음식점수 5개 미만, 주문수 30개 미만: 수수료 2% 일때, Subquery를 사용해서 이 복잡한 연산을 어떻게 수행할까?

 

먼저 < 총 주문 수량 >과 < 음식점 수 >를 한 Part로 보고, 뒤의 수수료를 계산하는 Part를 다른 부분으로 보면, 이 작업을 두 작업으로 나누어서 진행할 수 있을 것이다.

만들어 보자!

 

select cuisine_type,

sum(quantity) total_quantity,

count(distinct restaurant_name) count_res

from food_orders

group by 1

 

이렇게 하면, 첫번째 작업에 필요한 데이터들이 일차적으로 정리된 것을 볼 수 있다.

이제 이 새로운 데이터 테이블을 이용해서 수수료를 계산하는 구문을 CASE문을 사용해서 만들어 보면...

select cuisine_type,

total_quantity,

count_res,

case when count_res>=5 and total_quantity>=30 then 0.0005

when count_res>=5 and total_quantity<30 then 0.008

when count_res<5 and total_quantity>=30 then 0.01

when count_res<5 and total_quantity<30 then 0.02 end ratio_of_add

 

이렇게 될 것이고, 마지막으로 이 구문과 앞서 만든 구문을 합치면, 

select cuisine_type,

total_quantity,

count_res,

case when count_res>=5 and total_quantity>=30 then 0.0005

when count_res>=5 and total_quantity<30 then 0.008

when count_res<5 and total_quantity>=30 then 0.01

when count_res<5 and total_quantity<30 then 0.02 end ratio_of_add

from

(

select cuisine_type,

sum(quantity) total_quantity,

count(distinct restaurant_name) count_res

from food_orders

group by 1

) a

 

이런 모습이 될 것이다. 

 

=================================

 

두번째로, Join을 사용해서 필요한 데이터가 서로 다른 테이블에 나누어져 있을 때 한 테이블로 합쳐서 데이터를 추출하는 방법을 살펴보자.

 

먼저, Join에는 두가지 종류가 있는데,

Left Join과 Inner Join은 확실하게 구분하는 방법이 있다. 

먼저 Join을 사용하는 이유는 결국 테이블을 하나로 합치기 위함인데, 테이블을 하나로 합치기 위해서는 반드시 하나 이상의 Column 값을 공유해야만 한다. 

이 때 공유하는 컬럼을 기준으로 하나의 테이블에 값이 없더라도 모두 조회되는 경우를 의미한다! 예시로 보면 다음과 같다.

이렇게 공유 컬럼인 고객 ID를 기준으로, 식당 D는 전화번호, 이메일 연령 값이 없는데도 표가 합쳐져 나오는 것을 볼 수 있다.

 

반대로 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.공통컬럼명

 

FROM 뒤에 합칠 테이블을 테이블 1, 테이블 2로 나누어서 집어 넣은 것이며, 이 각각의 테이블에게 별명을 지어줄 수도 있다.  그리고 left join이던 inner join이던 사용한 뒤에는 공유 컬럼이 무엇인지 ON 다음으로 적어주게 된다.

 

그리고 예시를 살펴보자!

 

한국 음식의 주문별 결제 수단과 수수료율을 조회해야 할 상황이 있다고 가정하자.

조회에야 할 컬럼은 주문 번호, 식당 이름, 주문 가격, 결제 수단, 그리고 수수료율이다.

결제 정보가 없는 경우도 포함해서 조회해야 한다.

 

조회 컬럼을 잘 보면, 주문 번호, 식당 이름, 주문 가격은 food_orders 테이블에 있고, 결제 수단, 수수료율은 payments 테이블에 있다. 이럴 때 join을 사용하는데, 이 상황에서는 결제 정보가 없는 경우에도 포함해서 조회해야 하므로 사용할 join은 Left Join이다.

 

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'

 

오른쪽 아래를 보면, 초당골 에서 12180원하는 주문의 경우 결제 정보와 수수료율이 나와 있지 않음에도 조회된 것을 확인할 수 있다.

 

두번째 예제를 살펴보자!

 

고객의 주문 식당을 조회해야 하는 상황이다.

내가 필요한 정보는 고객 이름과 연령, 성별 그리고 주문 식당이다.

고객명으로 정렬해야 하며 중복이 없도록 조회해야 한다.

 

고객 이름과 연령, 성별은 customer 테이블에 있는 정보고, 주문 식당은 food_order 에 있는 정보다. 

여기에서도 left join을 사용하는데 문제가 없으므로, left join을 사용한다.

공유하는 컬럼은 customer_id가 되므로, FROM 문을 적을 때 이렇게 작성한다.

 

from food_orders f left join customers c on f.customer_id=c.customer_id

 

그리고 조회해야 할 컬럼을 다시 적으면 c.name, c.age, c.gender, f.restaurant_name이 된다.

여기서 중복을 피해야 하므로, 맨 앞에 DISTINCT 키워드를 추가한다.

마지막으로 고객명으로 정렬하기 위해 ORDER BY c.name을 추가한다.

 

select distinct c.name,

c.age,

c.gender,

f.restaurant_name

from food_orders f left join customers c on f.customer_id=c.customer_id

order by c.name

 

하지만 지금 NULL값이 너무 많아 지저분하므로, 코드 하나를 더 추가한다.

WHERE c.name is not NULL

 

select distinct c.name,

c.age,

c.gender,

f.restaurant_name

from food_orders f left join customers c on f.customer_id=c.customer_id

where c.name is not null // null 값 제거

order by c.name

 

성공적이다! 이것으로 4주차 내용은 마무리되었다.

 

5주차에서는 오류 처리와 SQL을 엑셀에서 자주 사용하는 형태로 살짝 바꾸는 방법, 그리고 추가적으로 다양한 SQL 심화 문법을 익혀보자.