[Question] 서브쿼리와 JOIN-완

몇일간의 고민이 해결되었다!! 도와주신 튜터님 감사합니다

Q. 서브쿼리_자기참조는 언제 사용하는가?

A. 그룹함수와 집계함수를 사용할 때 집계한 값과 데이터를 조회하기 위해서!

 

<문제: 코드스니펫 49번>

https://school.programmers.co.kr/learn/courses/30/lessons/131116

예시2. FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요

💡SQL 실행 순서는 From -> Where -> Group by -> Having -> Select -> Order by 

방법 1.

SELECT P.CATEGORY, MAX(P.PRICE) AS MAX_PRICE,        (SELECT PRODUCT_NAME FROM FOOD_PRODUCTWHERE CATEGORY = P.CATEGORY  ORDER BY PRICE DESC LIMIT 1)  AS PRODUCT_NAME   FROM FOOD_PRODUCT P  WHERE CATEGORY IN ('과자', '국', '김치', '식용유')  GROUP BY CATEGORY  ORDER BY MAX_PRICE DESC

➡️ 4.상품분류, 식품분류별 최대 가격을 조회 (➡️3. 상품의 이름을 조회 ➡️1. FOOD_PRODUCT테이블에서 ➡️ 2.(식품분류가 '과자', '국', '김치', '식용유'에 포함된 카테고리)와 동일할 경우 ➡️4. 단 가격이 높은 순으로 하나만(상품이름 조회)) ➡️1.FOOD_PRODUCT테이블에서 ➡️2.식품분류가 '과자', '국', '김치', '식용유'하나라도 포함될 경우 ➡️3. 식품분류별로 그룹화하여 ➡️5. 최대가격순으로

방법 2.

select a.category, a.price, a.product_name  from food_product a  join           ( SELECT CATEGORY, MAX(PRICE) MAX_PRICE             FROM FOOD_PRODUCT   WHERE CATEGORY IN ("과자", "국", "김치", "식용유")              GROUP BY CATEGORY) b  on a.price = b.max_price  and a.category = b.category   order by price desc

➡️카테고리, 가격, 상품이름 조회 ➡️(4. 식품분류와 분류별 최대가격 조회 ➡️1. FOOD_PRODUCT 테이블에서 ➡️2.식품분류가 '과자', '국', '김치', '식용유' 하나라도 포함 될 경우 ➡️3. 식품분류별로 그룹화하여) ➡️5. join,식품분류가 '과자', '국', '김치', '식용유' 하나라도 포함 될 경우의 식품분류와 해당 상품의 최대가격을 동일한 데이터 테이블에서 ➡️6.최대가격 순으로

<정답: 방법2> select a.category, a.price, a.product_name  from food_product a  join (              SELECT CATEGORY,              MAX(PRICE) MAX_PRICE             FROM FOOD_PRODUCT   WHERE CATEGORY IN ("과자", "국", "김치", "식용유")              GROUP BY CATEGORY) b  on. a.price = b.max_price and a.category = b.category
서브쿼리의 조건(과자/국/김치/식용류가 포함된 식품분류의 최대가격)과 동일한 상품분류,가격,이름 조회
<오답> select a.category, a.price, a.product_name  from food_product a  join (              SELECT CATEGORY,              MAX(PRICE) MAX_PRICE             FROM FOOD_PRODUCT   WHERE CATEGORY IN ("과자", "국", "김치", "식용유")              GROUP BY CATEGORY) b  on. a.price = b.max_price
식품분류별 최대가격을 조회했지만 과자/국/김치/식용류 외의 불필요한데이터가 추가되어있다. but inner join시에 테이블 a의 값이 "과자", "국", "김치", "식용유"의 가격만 있는데, 왜 밥의 데이터가추가되었을까? -> 과자의 최대값 1950원이 밥과 동일한 가격이라서 같이 출력되었다
<오답> '과자', '국', '김치', '식용유'가 들어있는 카테고리의  최대값을 구하자 SELECT P.CATEGORY,        MAX(P.PRICE) ,product_name   FROM FOOD_PRODUCT P  WHERE CATEGORY IN ('과자', '국', '김치', '식용유')  GROUP BY CATEGORY  ORDER BY 2 DESC
최대가격은 정답이랑 같은데  상품이름이 왜 다를까? -> max(price)랑 product_name이 불일치함! (8950원의 식용유이름은 맛있는 마조유임!) ---> 그룹함수를 사용했기 때문에 max(price)는 카테고리내에 가장 비싼 값이 나오지만  product_name은

 

 

<SQL (ECOMMERCE) 5번 >

예시1. order_details 테이블에는 다양한 주문들의 상세 정보가 저장되어 있습니다. 이 테이블의 각 행은 주문 ID(order_id), 카테고리(Category), 그리고 주문 수량(Quantity)을 포함합니다. 이 테이블을 사용하여 각 카테고리별 평균 주문 수량을 초과하는 모든 주문들을 찾아보세요

 

💡SQL 실행 순서는 From -> Where -> Group by -> Having -> Select -> Order by 

SELECT order_id, Category , Quantity FROM order_details a WHERE quantity > (SELECT avg(Quantity)                                   FROM order_details                                   where Category = a.Category) order by 2,3

➡️ 4. 주문ID,카테고리, 수량 조회 ➡️ 1. A테이블에서 ➡️ 2. (order_details 테이블에서  카테고리가 A테이블과 동일한 카테고리만  왜, GROUP BY랑 동일한 기능을 한것처럼 보이나? 카테고리가 A테이블과 동일한 데이터 끼리만 (예: 옷-> 가구 등) 출력되어 전체 평균한 것이 그룹화 한것처럼 나타난다. 대상으로 수량의 평균을 내라) ➡️ 3. 해당 값보다 수량이 크다면

 

---

썸네일: https://media.giphy.com/media/smaaKxR6ZrQYQxHFfA/giphy.gif