DB | [SQL] subquery와 join

1. Subquery

1) subquer란?

subquery는 쿼리 안에 포함되어 있는 또 다른 쿼리인데, Subquery가 필요한 경우는 다음과 같다.

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

아래에 보이는 바와 같이 괄호 안에 select와 from구문이 또 선언되어 있는데, 이것을 subquery라고 한다는 것이다.

select column1, special_column
from
(
select column1, column2 special_column
from table1
) a

아래에는 where구문 안에 subquery가 있는 형태이다.

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

2) Subquery의 예문 풀어보기

음식 타입별 총 주문수량과 음식점 수를 연산하고, 주문 수량과 음식점 수량 별 수수료율을 산정해보자. 이때,

- 음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.05%
- 음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.08%
- 음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
- 음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%

이다.

1. 어떤 테이블에서 데이터를 뽑을까. '음식타입별'이라고 하였으니 from구문에 food_order를 조회해본다. 그리고 아래 사진과 같이 조회된 '음식타입', '주문수량', '음식점 이름'을 뜻하는 'cuisine_type'과 'quantity', 'restaurant_name' 컬럼을 선택한다.
   
2. 그리고 'sum'으로 음식점 타입별 총주문수량과 'count'로 음식점 타입별 총음식점 수를 연산하고 각각 이름을 'total_quantity', 'count_res'라고 지어준다. 음식점 타입별로 그룹화하여 수량을 보여주기 위해서 group by구문을 사용한다.
   
3. 위의 수수료율을 산정하기 위해서 먼저 select~group by를 괄호()로 묶어 subquery로 만들고 괄호 밖에 다시 select와 from을 선언하여 case when 구문에 주문 수량과 음식점 수량에 따라 수수료율을 할당한다.
   

2. join

1) join이란?

join은 서로 다른 테이블의 공통된 칼럼을 이용해 한 테이블로 엮어주는 구문이다. join 하는 방법에는 left join과 inner join이 있는데 left join은 공통 컬럼을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회하는 방법이다. 반면에 inner 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.공통컬럼명

2) join 예문 풀어보기

식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 해보자. 이때,

- 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
- 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
- 두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬

이다.

1. 먼저 '주문 금액의 평균'과 주문자의 '평균 연령'을 각각 어떤 테이블에서 가져올지 결정한다. 주문 금액은 'food_orders' 테이블에서 가져올 수 있으며, 평균 연령은 'customers' 테이블에서 가져올 수 있다. 두 테이블 모두 데이터가 있는 경우만 조회하기 위해 inner join을 사용한다. 그리고 두 테이블의 공통 칼럼인 'customers_id'로 묶어 준다. 이 때 간소화를 위해 각 테이블의 명칭을 'f'와 'c'로 한다.

2. 그리고 '식당별'로 조회하기 위해 select에는 'f.restaurant_name'과 가격 및 연령대의 평균을 의미하는 'avg(f.price)', 'avg(c.age)을 추가하고, 간소화를 위해 평균값들의 명칭을 각각 'price', 'age'라고 한다. 그리고 식당별로 평균값을 구하기 위해 'group by f.restaurant_name(또는 1)' 라고 한다.
   

3. 위 쿼리를 서브쿼리로 지정한 다음, select에 case구문을 사용하여 가격대와 연령대에 따라서 그룹화한다.