subquery, JOIN

SUBQUERY

여러번의 연산을 해야할 때

조건문이 연산결과를 사용해야할 때

조건에 쿼리결과를 사용해야할 때 사용한다고 한다.

FROM 절 뒤에 괄호로 SELECT FROM을 한번더 감싸는 형식으로 쓴다.

 

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

 

음식 준비시간이 25분이 넘어가면 오버타임으로 설정하고 싶을때

서브쿼리없이 FOOD_PREPARATION_TIME-25만 적게 되면 

준비시간이 25분 미만인 가게들은 마이너스분이라는 시간이 나와버리니

서브쿼리로 OVER_TIME 이라는 별명을 줘 조건을 지정해주고,

위 본 QUERY에서는  IF문을 사용해 밑의 서브쿼리의 OVER_TIME을 가져와 조건을 설정해주며

음식 준비 시간이 25분 미만인 가게는 0을 나오게 해 마이너스분을 없앤것을 확인할 수 있다.

 

SELECT 
	RESTAURANT_NAME,
	SUM_PRICE,
	SUM_QUANTITY,
	CASE 
		WHEN SUM_QUANTITY <= 5 THEN 0.1
		WHEN SUM_QUANTITY > 15 AND SUM_PRICE >= 300000 THEN 0.005
		ELSE 0.01
	END AS DISCOUNT_RATE
FROM
(
	SELECT 
		RESTAURANT_NAME,
		SUM(PRICE) AS SUM_PRICE,
		SUM(QUANTITY) AS SUM_QUANTITY
	FROM food_orders
	GROUP BY 1
) A

서브쿼리부터 보면 주문한 가격의 총합 SUM PRICE,

주문한 음식 종류의 총합 SUM_QUANTITY가 존재하며

그걸 RESTAURANT_NAME 그룹으로 묶어 보여 달라.

 

본 쿼리에서 서브쿼리에서 만든 SUM_PRICE, SUM_QUANTITY를 활용해 

종류가 5개 이하일때 할인률 10퍼센트

종류가 15개 초과에 총 가격이 30만원이상이면 할인률 0.5퍼센트

나머진 1퍼센트로 했다.

 

 

 

 

JOIN

필요한 데이터가 서로 다른 테이블에 있을 때 사용한다.

FROM 테이블명 JOIN 엮을 다른 테이블명 ON 테이블명.컬럼 = 엮을 다른 테이블명.컬럼

여기서 테이블명과 엮을 다른 테이블명은 별칭을 만들어 짧게 사용 가능하다.

 

• LEFT JOIN

공통 컬럼을 기준으로 하나의 테이블에 값이 없더라도 모두 조회되는 경우이다.

A테이블과 B테이블이 있다면 A와 A, B의 공통되는 값을 조회하는 것이다.

ex) A = 1, 2, 3 / B = 2, 4, 5 라면 1, 2, 3이 조회된다.

 

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

 

 

• INNER JOIN

공통 컬럼을 기준으로 두 테이블에 모두 존재하는 값만 조회된다.

A와 B의 교집합이다.

ex) A = 1, 2, 3 / B = 2, 4, 5 라면 2가 조회된다.

 

 

SELECT 
	FO.order_id,
	FO.restaurant_name,
	FO.price,
	P.vat,
	FO.price * P.vat VAT2
FROM food_orders FO INNER JOIN payments P ON FO.order_id = P.order_id