SQL : 서브쿼리(Sub-query), 상관 서브쿼리

🍀 1. 서브쿼리 (Select절, from절, where절)

---

특정 경우에 가장의 테이블을 만들어서, 실제에 있는 테이블과 JOIN을 한다던지. 가상의 테이블을 만들어서 조회를 한 것을 가지고 다른 통계적인 것을 만든다던지.
실제 가지고 있는 테이블에서 한 번 조회한 테이블을 활용해야 하는 경우가 많이 생기는 데 이를 이용함.

• Scalar Subquery : 스칼라 서브쿼리는 단일값을 반환
  일반적으로 select, where, having절에서 사용
  모든 서브쿼리는 반드시 괄호 () 안에 포함

SELECT productName, buyPrice
FROM products
WHERE buyPrice > (SELECT AVG(buyPrice) FROM products);

• 전체 평균 가격 (buyPrice)보다 비싼 상품을 조회
• (where구문안의 select절 53.49) 서브 쿼리의 결과가 단일 값 (=스칼라 서브쿼리)

---

Table Subquery : 테이블 서브쿼리는 테이블처럼 사용할 수 있는 행과 열을 반환
일반적으로 from절에서 사용
from절에 사용되는 서브쿼리는 별칭 필수!

SELECT customerNumber, order_count
FROM (SELECT customerNumber, COUNT(orderNumber) AS order_count
		FROM orders GROUP BY customerNumber) AS subquery
WHERE order_count >= 5;

• 주문 개수가 5개 이상인 고객만 조회
• 서브 쿼리의 결과가 테이블

# from절 안에서 사용된 서브쿼리 실행해보자
SELECT customerNumber, COUNT(orderNumber) AS order_count 
fROM orders GROUP BY customerNumber;

• 이 코드에서 한 문장만 써도 위 코드와 같게 됨 (서브쿼리 안써도 됨)
• HAVING order_count >= 5 ( select count(orderNumber) AS order_count)

실습

❔ 문제 1: 최대 주문 개수를 가진 고객의 ID를 조회하라.

SELECT customerNumber 
FROM orders 
GROUP BY customerNumber 
HAVING COUNT(orderNumber) = (SELECT MAX(order_count) 
                             FROM (SELECT customerNumber, COUNT(orderNumber) AS order_count 
                                   FROM orders GROUP BY customerNumber) AS subquery);

• SELECT customerNumber, COUNT(orderNumber) AS order_count FROM orders GROUP BY customerNumber; 서브쿼리 안 서브쿼리

SELECT customerNumber
FROM (
SELECT customerNumber, COUNT(orderNumber) AS order_count
FROM orders
GROUP BY customerNumber
ORDER BY order_count DESC
LIMIT 1 ) AS temp_table1 ;

• 위와 같은 결과

❔ 문제 2: products와 orderdetails테이블을 이용하여, 각 제품별로 총 주문 금액(quantityOrdered * priceEach)을 계산하고, 그 결과를 기반으로 상위 5개의 제품만 조회하라.

SELECT productCode, productName, totalAmount
FROM (
	SELECT p.productCode, p.productName, SUM(o.quantityOrdered * o.priceEach) AS totalAmount
	FROM products p
	JOIN orderdetails o ON p.productCode = o.productCode
	GROUP BY p.productCode, p.productName
) AS inlineView
ORDER BY totalAmount DESC
LIMIT 5
;

SELECT p.productCode, p.productName, SUM(o.quantityOrdered * o.priceEach) AS totalAmount
FROM products p
JOIN orderdetails o ON p.productCode = o.productCode
GROUP BY p.productCode, p.productName
ORDER BY totalAmount DESC
LIMIT 5;

• 굳이 서브쿼리를 안쓰고 아래 코드로 사용해도 됌

🍀 2. 상관 서브쿼리

---

상관 서브쿼리(Correlated Subquery)는 일반 서브쿼리와는 달리 메인 쿼리의 각 행을 참조하여 수행.
메인 쿼리의 각 행마다 한 번씩 실행되며, 메인 쿼리의 현재 행과 연관된 결과를 반환.

• 반복 실행 : 상관 서브쿼리는 메인 쿼리의 각 행에 대해 별도로 실행. 따라서 메인 쿼리에 100개의 행이 있다면 상관 서브쿼리도 100번 실행
• 참조 : 상관 서브쿼리는 메인 쿼리의 열을 참조할 수 있음. 참조를 통해 서브쿼리는 메인 쿼리의 현재 행에 따라 다른 값을 반환할 수 있음.

SELECT c.customerName, o.orderDate
FROM customers c, orders o
WHERE c.customerNumber = o.customerNumber
AND o.orderDate =
	(SELECT MAX(orderDate) FROM orders WHERE customerNumber = c.customerNumber);

• 각 고객별로 가장 최근의 주문일을 조회하기
• WHERE customerNumber = c.customerNumber : 서브 쿼리 내에서 c의 customerNumber를 참조를 함
• (특징!) 서브쿼리만 따로 빼와서 실행 시 실행이 되지 않음.

SELECT c.customerName, o.orderDate
FROM customers c, orders o
WHERE c.customerNumber = o.customerNumber;

SELECT c.customerName, o.orderDate
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber;

• 이 두 코드는 서로 동일.
• FROM 안에 두 개의 테이블 : JOIN을 사용하는 다른 예시라고 볼 수 있음.

실습

❔ 문제 3: 2003년에 주문한 모든 고객의 이름을 조회하라.

SELECT customerName
FROM customers
WHERE customerNumber IN (SELECT customerNumber FROM orders WHERE YEAR(orderDate) = 2003);

• 서브쿼리 실행 시
  
• orders 테이블에서 orderDate가 2003년인 customerNumber를 IN 안에 넣어줌
• IN 안에 서브쿼리를 넣는 방법은 아주 좋은 방법임!