여러 번의 연산을 한 번의 SQL 문으로 수행하기 (Subquery)

• 연산이 한 번에 끝나면 좋은데 아닐 때가 많다. 문자 변경한 다음에 숫자 연산 하고 그 결과로 또 연산을 해야할 때, 긴 쿼리문 보다는 조금 더 효율적이고 알아보기 쉽게 사용할때 쓰는것이 서브쿼리이다.
• Sub 라는 명칭에서 알 수 있듯이, Query 안에 sub 로 들어간 구문이라고 보면 된다.

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)

-예시1) 아래와 같이 Subquery 문 안을 수정해서, 음식 주문시간이 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) 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기

• (할인조건
  수량이 5개 이하 → 10%
  수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%
  이 외에는 일괄 1%)

  select restaurant_name,
      case when sum_of_quantity<=5 then 0.1
           when sum_of_quantity>15 and sum_of_price>=300000 then 0.005
           else 0.01 end ratio_of_add
  from 
  (
  select restaurant_name,
      sum(quantity) sum_of_quantity,
      sum(price) sum_of_price
  from food_orders
  group by 1
  ) a

  

필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 (JOIN)

• 필요한 데이터가 꼭 하나의 테이블에 모여있지는 않다. 이럴 때는 여러 테이블에서 데이터를 불러오는 방법을 알아보자
• JOIN 은 기본적으로 엑셀의 Vlookup 과 유사하다. 아래와 같은 경우를 생각해보자
  
• 주문 정보에서 고객 이메일을 알기 위해서는, 고객 정보에서 동일한 고객 ID 의 이메일을 가져와야 한다.
• 이 때 중요한 것은, 두 테이블이 공통으로 갖고 있는 컬럼이다. 위의 예시에서는 ‘고객 ID’가 공통됨
  
• 즉, 공통 컬럼을 기준으로 두 테이블을 합쳐서, 각각 테이블에서 필요한 데이터를 조회할 수 있도록 만들어주는 것
• 조인에는 크게 LEFT JOIN 과 INNER JOIN 으로 나눠진다.
  
• LEFT JOIN : 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우를 의미한다. 예시로 보면 아래와 같다.
  
• INNER JOIN : 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회한다.
  즉, 위의 예시에서 주문번호 14의 경우, 고객에 대한 정보가 고객 정보에 없으므로 조회 시 제외된다.
  
• 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.공통컬럼명

• 예시로 JOIN 을 이용하여 두 개의 테이블에서 데이터를 조회해 주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left join 으로 묶어보자

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

• 아래와 같이 LEFT JOIN 사용하자 NULL값이 있음에도 모두 포함되어 나오는 모습
  

• 이번엔 반대로 INNER JOIN을 이용해 주문 가격과 수수료율을 곱하여 주문별 수수료 구해보자

select a.order_id,
       a.restaurant_name,
       a.price,
       b.vat,
       a.price*b.vat "수수료율"
from food_orders a inner join payments b on a.order_id=b.order_id