9.1 서브쿼리란

9.1.1 서브쿼리의 개념

서브쿼리란 쿼리 안에 포함된 또 다른 쿼리로, 안쪽 서브쿼리의 실행 결과를 받아 바깥쪽 메인쿼리가 실행된다.

-- sub_query DB 생성 및 진입
CREATE DATABASE sub_query;
USE sub_query;

-- students 테이블 생성
CREATE TABLE students (
	id INTEGER AUTO_INCREMENT, 	-- 아이디(자동으로 1씩 증가)
	name VARCHAR(30), 			-- 이름
	score INTEGER, 				-- 성적
	PRIMARY KEY (id) 			-- 기본키 지정: id
);

-- students 데이터 삽입
INSERT INTO students (name, score)
VALUES
	('엘리스', 85),
	('밥', 78),
	('찰리', 92),
	('데이브', 65),
	('이브', 88);
    
-- 평균 점수보다 더 높은 점수를 받은 학생 조회
SELECT *
FROM students
WHERE score > (
	-- 평균 점수 계산
    SELECT AVG(score)
    FROM students
);

9.1.2 서브쿼리의 특징

1. 중첩 구조다
2. 메인쿼리와는 독립적으로 실행된다
3. 다양한 위치에서 사용 가능하다
4. 단일 값 또는 다중 값을 반환한다
5. 조건 필터링 결과 또는 데이터 집계 결과를 반환한다

9.2 다양한 위치에서의 서브쿼리

9.2.1 SELECT 절에서의 서브쿼리

-- market DB 진입
USE market;

-- SELECT 절에서의 서브쿼리 실습
SELECT payment_type AS '결제 유형',
	   amount AS '결제 금액',
       amount - (SELECT AVG(amount) FROM payments) AS '평균 결제 금액과의 차이'
FROM payments;

9.2.2 FROM 절에서의 서브쿼리

-- FROM 절에서의 서브쿼리 실습
SELECT AVG(sub.total_count) AS '1회 주문 시 평균 상품 개수'
FROM (
	SELECT order_id, SUM(count) AS total_count
	FROM order_details
	GROUP BY order_id
) AS sub;

9.2.3 JOIN 절에서의 서브쿼리

-- JOIN 절에서의 서브쿼리 실습
SELECT name AS '상품명', total_count AS '주문 개수'
FROM products
JOIN (
	  SELECT product_id, SUM(count) AS total_count
      FROM order_details
      GROUP BY product_id
) AS sub ON products.id = sub.product_id;

9.2.4 WHERE 절에서의 서브쿼리

-- WHERE 절에서의 서브쿼리 실습
SELECT name AS '상품명', price AS '가격'
FROM products
WHERE price > (SELECT AVG(price) FROM products);

9.2.5 HAVING 절에서의 서브쿼리

-- HAVING 절에서의 서브쿼리 실습
SELECT name AS '상품명', SUM(price*count) AS '매출'
FROM products
JOIN order_details ON products.id = order_details.product_id
GROUP BY name
HAVING SUM(price*count) > (
	SELECT SUM(price*count)
    FROM products
	JOIN order_details ON products.id = order_details.product_id
					   AND name = '크림 치즈'
);

9.3 IN, ANY, ALL, EXISTS

9.3.1 IN 연산자

IN 연산자는 'IN (서브쿼리)' 형태로 다중 행의 단일 칼럼(N x 1)을 반환하는 서브쿼리를 입력받을 수 있다.

-- IN 연산자 사용 예
SELECT *
FROM order_details
WHERE product_id IN (
	SELECT id
    FROM products
    WHERE name IN ('우유 식빵', '크림 치즈')
);

-- 조인과 IN 연산자 실습
SELECT DISTINCT u.id, nickname
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_details od ON o.id = od.order_id
JOIN products p ON od.product_id = p.id
WHERE name IN ('우유 식빵', '크림 치즈');

9.3.2 ANY 연산자

-- ANY 연산자 실습
SELECT name 이름, price 가격
FROM products
WHERE price < ANY (
	SELECT price
    FROM products
    WHERE name IN ('우유 식빵', '플레인 베이글')
);

9.3.3 ALL 연산자

-- ANY 연산자 실습
SELECT name 이름, price 가격
FROM products
WHERE price < ANY (
	SELECT price
    FROM products
    WHERE name IN ('우유 식빵', '플레인 베이글')
);

9.3.4 EXISTS 연산자

-- EXISTS 연산자 실습
SELECT *
FROM users u
WHERE EXISTS (
	SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

-- NOT EXISTS 연산자 실습
SELECT *
FROM users u
WHERE NOT EXISTS (
	SELECT 1
    FROM orders o
    JOIN payments p ON o.id = p.order_id
    WHERE o.user_id = u.id AND payment_type = 'COCOA PAY'
);

9.4 셀프체크

8.3절에서 만든 market DB를 토대로 다음 1~4를 수행하는 쿼리를 작성하세요.

-- market DB 진입
USE market;

-- 1. 전체 사용자의 1인당 평균 결제 금액 조회
SELECT SUM(amount) / (SELECT COUNT(*) FROM users) '1인당 평균 결제 금액'
FROM payments;

-- 2. 전체 사용자의 1인당 평균 결제 금액 조회
SELECT AVG(sub.total_amount) '1인당 평균 결제 금액'
FROM (
	SELECT u.id AS user_id, SUM(amount) AS total_amount
    FROM users u
    JOIN orders o ON u.id = o.user_id
    JOIN payments p ON o.id = p.order_id
    GROUP BY u.id
) sub;

-- 3. 앞의 두 쿼리를 실행하면 동일한 결과가 나온다. 그 이유를 설명
-- 모든 사용자가 결제에 참여했기 때문

-- 4. 최근에 배송받은 사용자의 총 결제 금액 조회
SELECT SUM(amount)
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN payments p ON o.id = p.order_id
WHERE u.id = (
	SELECT user_id
    FROM orders
    WHERE status = '배송 완료'
    ORDER BY created_at DESC
    LIMIT 1
);