[스파르타코딩클럽] 엑셀보다 쉽고 빠른 SQL - 4주차(Subquery, Join)

배운 내용을 작성하고 있습니다. 혹여 해당 포스팅에서 잘못된 부분이 있을 경우, 알려주시면 빠르게 수정 조치하도록 하겠습니다. 감사합니다.

 

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

1) Subquery 가 필요한 경우

• 여러번의 연산을 수행해야 할 때
  • 수수료를 부과할 수 있는 시간을 구하고
  • 구해진 시간에 주문 금액별로 가중치를 주고
  • 가중치를 적용한 결과로 최종 예상 배달비를 계산할 때
• 조건문에 연산 결과를 사용해야 할 때
  • 음식 타입별 평균 음식 주문금액 따라 음식비 상/중/하 를 나누고 싶을 때
• 조건에 Query 결과를 사용하고 싶을 때
  • 30대 이상이 주문한 결과만 조회하고 싶을 때

2) 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)

3) [실습] Subquery 문을 이용하여 연산문 적어보기

• 주문 테이블에서 주문 번호, 음식점명, 음식 준비시간을 가져오기
  • select 기본문
  • 가져올 컬럼 적기
  • subquery 문으로 추가

select order_id, restaurant_name, food_preparation_time
from 
(
select order_id, restaurant_name, food_preparation_time
from food_orders
) a

• 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. 필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 (JOIN)

1) JOIN 이 필요한 경우

• 주문 가격은 주문테이블에 있지만, 어떤 수단으로 결제를 했는지는 결제테이블에 있어요
• 주문을 한 사람을 확인하려면, 주문 테이블과 고객 테이블에서 각각 정보를 가져와서 엑셀에서 합쳐줘야 해요
• 주문 건별 수수료를 계산하려면 수수료율이 필요한데, 결제 테이블에 있어서 어떻게 연산할 수 있을지 모르겠어요

2) JOIN 의 기본 원리와 종류

• JOIN 은 기본적으로 엑셀의 Vlookup 과 유사합니다 아래와 같은 경우를 생각해보세요

• 주문 정보에서 고객 이메일을 알기 위해서는, 고객 정보에서 동일한 고객 ID 의 이메일을 가져와야 합니다
• JOIN 은 동일한 원리를 가집니다. 각각 주문 정보와 고객 정보가 테이블이라고 할 때, 고객 ID 를 기준으로 필요한 값을 가져와 주는 것입니다.
• 이 때 중요한 것은, 두 테이블이 공통으로 갖고 있는 컬럼이 됩니다. 위의 예시에서는 ‘고객 ID’가 되겠죠

• 공통 컬럼을 기준으로 묶은 형태는 아래와 같이 됩니다. 즉, 공통 컬럼을 기준으로 두 테이블을 합쳐서, 각각 테이블에서 필요한 데이터를 조회할 수 있도록 만들어주는 것입니다.

• 조인은 하는 방법

• 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.공통컬럼명

• 공통컬럼은 묶어주기 위한 ‘공통 값’ 이기 때문에 두 테이블의 컬럼명은 달라도 괜찮습니다. 예를 들어 주문정보에는 ‘고객ID’, 고객정보에는 ‘고객아이디’ 라고 컬럼명이 되어있다면, 테이블1.고객ID=테이블2.고객아이디 와 같이 묶어줄 수 있습니다.

3) [실습] JOIN 을 이용하여 두 개의 테이블에서 데이터를 조회해보기

• 주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left join 으로 묶어보기 (조회 컬럼 : order_id, customer_id, restaurant_name, price, name, age, gender)

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

 

정리

👉 Subquery

1. Query 결과를 Query 에 다시 활용하는 것
2. 기본 형식

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

👉 JOIN

1. 두 개 이상의 테이블을 결합하여 사용하는 것
2. JOIN 의 형태에 따라 Left join, Inner join 등이 있음
3. 기본 형식

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