[내일배움캠프] SQL - 서브쿼리, LEFT JOIN, INNER JOIN

SQL을 다루면서 모르면 안될 서브쿼리와 조인들 중 LEFT JOIN과 INNER JOIN에 다뤄보았다.

 

· 서브쿼리

여러번의 연산을 한 번의 SQL 문으로 수행할 때 사용한다.

 

활용하지 않아도 되는 경우도 있지만, 잘 활용한다면 깔끔하게 코드를 작성할 수 있다.

 

- 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기

(수수료 구간 -

~5000원 미만 0.05%

~20000원 미만 1%

~30000원 미만 2%

30000원 초과 3%)

SELECT restaurant_name, (fee * avg_price) "수수료"
FROM 
(
SELECT restaurant_name, avg_price,
CASE WHEN avg_price < 5000 THEN 0.005
     WHEN avg_price BETWEEN 5000 AND 19999 THEN 0.01
     WHEN avg_price BETWEEN 20000 AND 29999 THEN 0.02
     ELSE 0.03 END "fee"
FROM 
(
SELECT restaurant_name, AVG(price/quantity) "avg_price"
FROM food_orders
GROUP BY restaurant_name
) a
) b;

 

보다시피 코드가 좀 길다.

 

일단 평균단가를 뽑아내기 위해 AVG(price/quantity) 를 작성한 서브쿼리문을 먼저 만들고,

 

수수료 구간을 CASE 문을 활용하여 작성했다.

 

위에 보면 서브쿼리문을 두 번 작성했는데, 그냥 깔끔하게 뽑고 싶은 데이터를 명시하기 위해 맨 위에 한 번 더 적었다.

 

한 번만 써도 무방.

 

주의할 것은 서브쿼리 문이 끝나고 서브쿼리로 추출된 테이블의 이름을 명시해줘야 한다.

 

위의 코드의 경우 a, b로 대충 명시해줬다. 크게 쓸 일이 없어서 이름을 디테일하게 정의하지 않았다.

 

결과 화면.

 

 

 

-  음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기

 

(음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.5% 음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.8% 음식점수 5개 미만, 주문수 30개 이상 → 수수료 1% 음식점수 5개 미만, 주문수 30개 미만 → 수수료 2%)

SELECT cuisine_type,
CASE WHEN restaurant_num >= 5 AND restaurant_sum >= 30 THEN 0.005
     WHEN restaurant_num >= 5 AND restaurant_sum < 30 THEN 0.008
     WHEN restaurant_num < 5 AND restaurant_sum >= 30 THEN 0.01
     WHEN restaurant_num < 5 AND restaurant_sum < 30 THEN 0.02
     END "fee"
FROM 
(
SELECT cuisine_type, 
       count(DISTINCT restaurant_name) "restaurant_num", 
       SUM(quantity) "restaurant_sum"
FROM food_orders
GROUP BY cuisine_type
) a;

 

아까 코드와 비슷하게 일단 총 주문수량과 음식점 수를 연산한 이후, 서브쿼리로 만들어 주고

 

CASE를 활용하여 다중 조건들을 연산하여 fee라는 컬럼으로 생성해줬다.

 

결과 화면.

 

 

-  음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기

 

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

SELECT restaurant_name,
       total_quantity,
       price_sum,
       CASE WHEN total_quantity <= 5 THEN 0.1
            WHEN total_quantity > 15 AND price_sum  > 300000 THEN 0.005
            ELSE 0.01 END "sale"
FROM
(
SELECT SUM(quantity) "total_quantity", SUM(price) "price_sum", restaurant_name
FROM food_orders
GROUP BY restaurant_name
) a;

 

총 주문수량과 총 주문금액의 합을 서브쿼리로 넘겨주고,

 

이 총 주문수량을 기반으로 CASE에서 다중 조건들을 연산해주었다.

 

결과 화면.

 

---

 

· JOIN - INNER JOIN, LEFT JOIN

원하는 데이터가 다른 테이블에 있을 때 외래키로 다른 테이블의 기본키를 참조해서 JOIN을 활용해 데이터를 불러올 수 있다.

 

키에 관련된 내용은 다루지 않고, JOIN에 대해서만 다뤄보겠다.

 

- LEFT JOIN

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 c.customer_id = f.customer_id;

 

LEFT JOIN은 FROM 절에서 a_table a LEFT JOIN b_table b ON a.foreign_key = b.primary_key 와 같이 진행한다.

 

'LEFT' JOIN 이니까 LEFT JOIN 의 왼쪽에 쓴 테이블이 기준이 되고, 기준 테이블에 오른쪽에 쓴 테이블을 붙인다.

 

이때 LEFT JOIN은 붙이는 테이블이 기준 테이블의 데이터에 없어도 NULL로 대체하여 붙인다.

 

LEFT JOIN은 값이 없어도 일단 붙이고 본다.

 

 

그럼 INNER JOIN은 어떨까?

 

- INNER JOIN

SELECT f.order_id,
       f.restaurant_name,
       f.price,
       p.vat,
       f.price*p.vat "fee"
FROM food_orders f INNER JOIN payments p ON f.order_id = p.order_id;

 

NULL 값 없이 꽉꽉 채워져있다.

 

 

 

INNER JOIN과 LEFT JOIN의 차이이다.

 

INNER JOIN은 교집합 되는 부분만, 즉 A 테이블에 없는 데이터는 조회되지 않는다.

 

실제로 코드를 돌려봐도 NULL 값을 찾아볼 수 없다.

 

 

- 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기 - 평균 음식 주문 금액 기준 : 5,000 이하 / ~10,000 / ~30,000 / 30,000 초과 - 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상

 

SELECT f.restaurant_name,
       CASE WHEN AVG(c.age) < 30 THEN "~20대"
            WHEN AVG(c.age) < 40 THEN "30대"
            WHEN AVG(c.age) < 50 THEN "40대"
            WHEN AVG(c.age) >= 50 THEN "50대 이상"
            END age_group,
       AVG(f.price) avg_price,
       CASE WHEN AVG(f.price) <= 5000 THEN "price_group1"
            WHEN AVG(f.price) <= 10000 THEN "price_group2"
            WHEN AVG(f.price) <= 30000 THEN "price_group3"
            WHEN AVG(f.price) > 30000 THEN "price_group4"
            END price_group
FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
GROUP BY restaurant_name
ORDER BY f.restaurant_name ASC;

 

서브쿼리를 사용해서 age와 price의 평균을 따로 빼서 넣어도 무방하지만

 

뭔가 하다보니 귀찮아서 서브쿼리 없이 작성했다.

 

CASE를 두 번 써서 평균 연령과 평균 음식 주문 금액의 다중조건을 해결했고 식당명을 기준으로 그룹 정렬 및 오름차순 정렬을 수행했다.

 

---

- 예제

- 테이블에서 모든 학생의 이름(name)과 트랙(track)을 선택하는 쿼리를 작성

SELECT name, track
FROM sparta_students

 

 

- 테이블에서 Unity 트랙 소속이 아닌 학생들을 선택하는 쿼리를 작성

SELECT *
FROM sparta_students
WHERE track != 'UNITY'

 

 

- 테이블에서 입학년도(enrollment_year)가 2021년인 학생과 2023년인 학생을 선택하는 쿼리를 작성

SELECT *
FROM sparta_students
WHERE enrollment_year = 2023
OR enrollment_year = 2021

 

 

- 테이블에서 Node.js 트랙 소속이고 학점이 ‘A’인 학생의 입학년도를 선택하는 쿼리를 작성

 

SELECT *
FROM sparta_students
WHERE track = 'Node.js'
AND grade = 'A'

 

 

 

참고로 예제는 이번 주차에 공부한 부분과 무관하다..

 

그냥 개인적으로 풀어보는 문제라고 생각해주십숑