[SQL]서브쿼리(Subquery)의 특징과 분류

서브쿼리 복습↓

https://dydatablog.tistory.com/19

[SQL]Subquery로 여러 번의 연산을 한 번에

서브쿼리의 특징: 

1) Result Types 반환 타입:

•  Scalar(값)
•  Row(행)
•  Table(테이블)

 

2) Dependanct 의존:

•  Non-Correlated (비상관)
•  Correlated(상관)

 

3) Location | Clauses 사용되는 자리:

•  SELECT
•  FROM
•  JOIN
•  WHERE
  • Comparsion operators : IN, ANY, ALL, EXIST
  • Logical operators : >, <, =, !=, >=, <=

---

1) Result Types에 따른 분류

•  Scalar(값)

SELECT customer_id,	--3. customer_id와 max_payment를 출력
       (SELECT MAX(payment_value) FROM payments) AS max_payment --1.(서브쿼리)payments테이블에서 payment_value의 최댓값을 'max_patment'로 정의해라
FROM customers; --2.customers 테이블에서

•  Row(행)

-- 다중 행 서브쿼리 : 서브쿼리가 1개의 컬럼에 여러 행을 반환 >> 마치 배열(리스트)처럼 동작한다.
SELECT customer_id --3.customer_id를 반환해라
FROM customers	--2.customers에서
WHERE customer_id IN	--3.customers_id가 1.의 데이터 안에 있다면 	
(
    SELECT customer_id
    FROM orders
    WHERE order_status = 'delivered'
); --1.(서브쿼리) orders테이블에서 order_status가 'delivered'인 데이터의 customer_id를 추출해라

 

• Table(테이블)

-- 다중 열 서브쿼리 : 서브쿼리가 여러 열과 여러 행으로 구성된 결과를 반환 >> 가상 테이블처럼 동작
SELECT customer_id, total_payment --4. customer_id, total_payment를 구해라
FROM --3. 1.에서 만든 테이블에서
(
    SELECT customer_id, SUM(payment_value) AS total_payment
    FROM payments
    GROUP BY customer_id --1.(서브쿼리)payments테이블을 customer_id로 그룹화하고, customer_id, SUM(payment_value)을 구해라.
) AS payment_summary; --2.이를 payment_summary로 정의해라

 

2) Dependanct 의존:

•  Non-Correlated (비상관)

-- 비상관 서브쿼리(Independent Subquery) : 서브쿼리가 외부 쿼리와 독립적으로 1번만 실행된다. 
-- 내부 쿼리 결과가 외부 쿼리에서 사용됨

SELECT customer_id 	--4.customer_id를 반환해라
FROM customers	-- 2.customers에서
WHERE age > 	--3.1.서브쿼리 결과값(age의 평균)이 age보다 작으면
(
    SELECT AVG(age)
    FROM customers
);  --1.(서브쿼리) customers에서 age의 평균을 구해라

•  Correlated(상관)

상관 서브쿼리의 특징 :

1) 내부 쿼리(Subquery)가 외부 쿼리(Outer Query)의 데이터를 참조

2) 외부 쿼리의 각 행에 대해 반복적으로 실행된다. → 각 행별로 동적으로 조건을 비교가능!

3) 반복 실행으로 인해 성능이 저하된다. → JOIN 혹은 WINDOW함수로 변환

 

서브쿼리 예시1)

orders 테이블

order_id	customer_id	order_amount
1	101	50
2	101	100
3	102	150
4	102	200
5	103	300

 

-- 상관 서브쿼리의 예1
SELECT order_id, customer_id, order_amount	--7. order_id, customer_id, order_amount를 반환해라
FROM orders o1				--2.orders를 o1로 정의 *서브쿼리에서 참조 / 5. orders에서
WHERE order_amount > 			--6. 4.의 결과값이 order_amount보다 작다면
(
    SELECT AVG(order_amount)		--4.(서브쿼리)order_amount의 평균을 반환 >> 서브쿼리의 결과값
    FROM orders o2			--1.(서브쿼리)orders를 o2로 정의
    WHERE o1.customer_id = o2.customer_id--3.(외브쿼리)o1.customer_id와 (서부커리)o2.customer_id가 값이 같다면
); 

-- 상관 서브쿼리의 예1: JOIN으로 변환
SELECT o1.order_id, o1.customer_id, o1.order_amount	--9. 다음을 반환해라.
FROM orders o1			--5.orders에서
JOIN 				--7.JOIN해라(기본값이 INNER JOIN)
(
    SELECT customer_id, AVG(order_amount) AS avg_order_amount
    				--3.(서브쿼리)customer_id와 order_amount의 평균값을 반환
    FROM orders			--1.(서브쿼리)orders테이블을
    GROUP BY customer_id	--2.(서브쿼리)customer_id로 그룹화한 데이터 중에
) avg_orders 			--4.서브쿼리 결과값을 avg_orders라고 지정
ON o1.customer_id = avg_orders.customer_id		--6.o1.customer_id = avg_orders.customer_id으로
WHERE o1.order_amount > avg_orders.avg_order_amount;	--8.-와 같은 조건이라면

 

쿼리 실행결과

order_id	customer_id	order_amount
2	101	100
4	102	200

 

---

서브쿼리 예시2)

customers 테이블

customer_id	customer_name	registration_date
101	Alice	2023-01-01
102	Bob	2023-02-01
103	Charlie	2023-03-01
104	David	2023-04-01

 

-- 상관 서브쿼리의 예2
SELECT customer_id, 
       (SELECT COUNT(*) 
        FROM orders 
        WHERE orders.customer_id = customers.customer_id) AS order_count
FROM customers;

-- 상관 서브쿼리의 예2: JOIN으로 변환
SELECT c.customer_id, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

-- 상관 서브쿼리의 예2: 윈도우 함수로 변환!
SELECT customer_id, COUNT(order_id) OVER(PARTITION BY customer_id) AS order_count
FROM orders;

 

 

쿼리 실행결과 - 상관 서브쿼리의 예2

customer_id	order_count
101	2
102	2
103	1
104	0

 

 

3) Location | Clauses 사용되는 자리:

•  SELECT

-- 각 고객의 총 주문수 반환
SELECT customer_id,
       (SELECT COUNT(*) 
       FROM orders 
       WHERE orders.customer_id = customers.customer_id) AS order_count
FROM customers;

-- 각 고객의 최근 주문ID 반환
SELECT customer_id,
       (SELECT MAX(order_id) 
        FROM orders 
        WHERE orders.customer_id = customers.customer_id) AS latest_order
FROM customers;

•  FROM / JOIN

SELECT customer_id, SUM(payment_value) AS total_payment
FROM (
    SELECT customer_id, payment_value
    FROM payments
    WHERE payment_date >= '2023-01-01'
) AS recent_payments
GROUP BY customer_id;

•  WHERE
  • Comparsion operators : IN, ANY, ALL, EXIST
    • ANY: 서브쿼리 결과 중 하나라도 조건을 만족하면 참
    • ALL: 서브쿼리 결과의 모든 값에 대해 조건을 만족해야 참
    • IN : 서브쿼리 결과 값 중 하나와 일치하면 참
    • EXISTS :서브쿼리의 결과가 존재하면 참(한 행이라도 존재하면 OK)
  • Logical operators : >, <, =, !=, >=, <=

SELECT customer_id
FROM customers
WHERE age > ( -- 고객의 나이가 모든 고객의 평균 나이보다 큰 경우
    SELECT AVG(age)
    FROM customers
);