필요한 데이터가 서로 다른 테이블에 있을 때 Join을 이용하여 두 테이블을 합쳐줄 수 있다.
Join 기능은 두 가지로 나뉘는데,
Left join: 공통 컬럼을 기준으로 하나의 데이블에 없더라도 모두 조회
Inner join: 공통 컬럼을 기준으로 두 테이블 모두에 있는 컬럼만 조회
SQL로 직접 해보자
Food_orders에 있는 정보와 payments에 있는 정보를 합쳐서 보고 싶다.
Food_orders table
Payments table
두 테이블 모두 order_id라는 공통 컬럼을 가진다. 이걸로 묶어보자!
select *
from food_orders left join payments on food_orders.order_id=payments.order_id명령문은 ‘From 테이블 1 left/inner join 테이블2 on 테이블1.공통 컬럼=테이블2.공통 컬럼’이다.
공통 컬럼인 order_id를 기준으로 두 테이블이 합쳐진 것을 확인할 수 있다.
음식 주문 테이블과 고객 테이블을 customer_id를 기준으로 left join해보자
select food_orders.order_id,
food_orders.customer_id,
food_orders.restaurant_name,
food_orders.price,
customers.name,
customers.age,
customers.gender
from food_orders left join customers on food_orders.customer_id=customers.customer_id주의할 점은 전체 데이터를 출력하는 게 아니라 위와 같이 두 테이블에서 일부 컬럼만 가져오는 경우 select에 어떤 컬럼을 어떤 테이블에서 가져오는지를 적어줘야 한다.
‘테이블.컬럼’의 형태로 적어준다.
select f.order_id,
f.customer_id,
f.restaurant_name,
f.price,
c.name,
c.age,
c.gender
from food_orders f left join customers c on f.customer_id=c.customer_id테이블 뒤에 별명을 붙여서 더 간단하게 코드를 작성할 수도 있다.
Food_orders는 f, customers는 c로 별명을 붙여서 select와 on 뒤에 테이블 전체 이름 대신 별명을 입력해서 간단하게 작성했다.
한국 음식의 주문별 결제 수단과 수수료율 조회하기
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'
#한국 음식점만 조회하므로 where절로 조건을 입력한다.음식 주문과 고객 정보 테이블을 묶어서 고객 이름과, 나이, 성별, 가게 이름을 출력
고객 이름을 기준으로 테이블을 정렬하고 중복되는 고객은 제거한다.
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에러 발생,,
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.namedistinct를 사용하는 건 맞지만 괄호 사용하지 않고 그냥 쓴다!
다음과 같이 중복된 고객을 제외한 데이터가 출력되었다.
주문 가격과 수수료율을 곱하여 수수료 구하기
수수료율이 있는 컬럼만 출력한다
select f.order_id,
f.restaurant_name,
f.price,
p.vat,
f.price*p.vat '수수료'
from food_orders f inner join payments p on f.order_id=p.order_idpayments 테이블에 수수료율이 있으므로 payments에 있는 데이터는 모두 포함되어야 한다.
그러므로 inner join을 이용한다.
수수료는 수수료율*가격이므로 select에 수식을 입력해서 함께 출력한다.
50세 이상 고객에게 할인을 적용하여 음식 종류별 원래 음식 합계와 할인된 가격의 합계를 구하고자 한다.
SELECT cuisine_type,
sum(price) price,
sum(discounted_price) discounted_price
from
(
select f.cuisine_type,
f.price,
(c.age-50)*0.005 discount_rate,
f.price*(c.age-50)*discount_rate discounted_price
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.age>=50
) a
group by 1
order by sum(discounted_price) desc왜 에러가 뜰까,,,혼자서는 도저히 해결이 안 돼서 튜터 선생님께 찾아갔다..
서브쿼리 안의 명령문의 경우 서브쿼리를 나간 후에 정의되어 메인쿼리에서 사용되는 것이기 때문에 서브쿼리 안의 ‘discount_rate’로 명명한 것은 서브쿼리를 나가야만 사용할 수 있다.
SELECT cuisine_type,
sum(price) price,
sum(discounted_price) discounted_price
from
(
select f.cuisine_type,
f.price,
(c.age-50)*0.005 discount_rate,
f.price*(c.age-50)*0.005 discounted_price
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.age>=50
) a
group by 1
order by sum(discounted_price) descf.price(c.age-50)discount_rate
'discounted_price'을 원래 수식 '(c.age-50)*0.005'으로 바꿔서 넣었더니 해결됐다!!
SELECT cuisine_type,
sum(price) price,
sum(price*discount_rate) discounted_price
from
(
select f.cuisine_type,
f.price,
(c.age-50)*0.005 discount_rate
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.age>=50
) a
group by 1
order by sum(price*discount_rate) desc선생님이 작성하신 코드
이렇게 메인쿼리에서 가격에 할인율을 곱하여 할인된 가격을 모두 합친 금액을 구할 수도~
Chapter 4 실습문제
식당과 고객정보를 합쳐서 식당에 따라 평균 가격과 나이를 구해서 그룹화해라
select restaurant_name,
case when avg_price<=5000 then 'price_group1'
when avg_price<=10000 then 'price_group2'
when avg_price<=30000 then 'price_group3'
else 'price_group4' end 'price_group',
case when avg_age<=29 then 'age_group1'
when avg_age<=39 then 'age_group2'
when avg_age<=49 then 'age_group3'
else 'age_group4' end 'age_group'
from
(
SELECT restaurant_name,
avg(price) avg_price,
avg(age) avg_age
from food_orders f inner join customers c on f.customer_id=c.customer_id
group by restaurant_name
order by restaurant_name
) a 다음과 같이 출력할 수 있었다.
에러가 나서 해결하는 데 시간이 꽤나 걸렸다..
1. case절에서 결과값을 입력할 때 영문이라도 ‘’안에 입력하기
2. 하나의 case 안에서는 쉼표를 찍지 않고, end 컬럼명이 끝난 후에 쉼표를 찍기(컬럼명은 ‘’안에 써도 되고 안 써도 된다)
역시 에러가 난다고 무작정 정답을 보고 내 답을 고치기보다는 내가 작성한 코드가 왜 틀린지 확인해보고 해결이 안되면 튜터선생님께 여쭤보는 게 직빵,,,오늘 틀려서 배운 건 절대 안 까먹을 것 같다,,^0^
