[MySQL] 서브쿼리 (Subquery)

---

서브쿼리

 

예제 1)

# 미국에 위치한 사무실에서 근무하는 직원들 조회
SELECT 
	lastName, firstName
FROM
	employees
WHERE
	officeCode IN (
		SELECT 
			officeCode
		FROM
			offices
		WHERE
			country = 'USA'
	);

# 1.서브쿼리 → 미국에 위치한 모든 사무실 코드 반환
# 2.외부쿼리 → 서브쿼리에서 반환한 사무실에서 근무하는 직원의 성과 이름 조회

lastName |firstName|officeCode|
---------+---------+----------+
Murphy   |Diane    |1         |
Patterson|Mary     |1         |
Firrelli |Jeff     |1         |
Bow      |Anthony  |1         |
Jennings |Leslie   |1         |
Thompson |Leslie   |1         |
Firrelli |Julie    |2         |
Patterson|Steve    |2         |
Tseng    |Foon Yue |3         |
Vanauf   |George   |3         |

 

예제 2)

# 결제금액이 가장 높은 고객 조회
SELECT 
    customerNumber, 
    checkNumber, 
    amount
FROM
    payments
WHERE
    amount = (SELECT MAX(amount) FROM payments);

customerNumber|checkNumber|amount   |
--------------+-----------+---------+
           141|JE105477   |120166.58|

 

# 평균 결제금액보다 더 많은 금액을 결제한 고객 조회
SELECT 
    customerNumber, 
    checkNumber, 
    amount
FROM
    payments
WHERE
    amount > (SELECT AVG(amount) FROM payments);

customerNumber|checkNumber|amount   |
--------------+-----------+---------+
           112|HQ55022    | 32641.98|
           112|ND748579   | 33347.88|
           114|GG31455    | 45864.03|
           114|MA765515   | 82261.22|
           114|NR27552    | 44894.74|
           119|LN373447   | 47924.19|
           119|NG94694    | 49523.67|
           121|DB889831   | 50218.95|
           121|MA302151   | 34638.14|

# 1.서브쿼리 → 평균 결제금액 반환
# 2.외부쿼리 → 평균 결제금액보다 더 많은 금액을 결제한 고객 조회

 

예제 3)

# 주문을 하지 않은 고객 조회
SELECT 
	customerName
FROM
	customers
WHERE
	customerNumber NOT IN (
		SELECT DISTINCT customerNumber
		FROM orders
	);

# 1.서브쿼리 → 주문 테이블에서 중복제거해서 고객번호 조회 (1번이라도 주문한 사람 조회)
# 2.외부쿼리 → 1번도 주문하지 않은 사람 조회

customerName                  |
------------------------------+
Havel & Zbyszek Co            |
American Souvenirs Inc        |
Porto Imports Co.             |
Asian Shopping Network, Co    |
Natürlich Autos               |
ANG Resellers                 |
Messner Shopping Network      |
Franken Gifts, Co             |
BG&E Collectables             |
Schuyler Imports              |
Der Hund Imports              |
Cramer Spezialitäten, Ltd     |

 

예제 4) 서브쿼리의 결과집합을 테이블처럼 사용가능

# 주문번호별 최소/최대/평균 항목개수 조회
SELECT 
	MAX(items), 
	MIN(items), 
	FLOOR(AVG(items))
FROM
	(
		SELECT 
			orderNumber, 
			COUNT(orderNumber) AS items
		FROM
			orderdetails
		GROUP BY 
			orderNumber
   	) AS lineItems;

# 1. 서브쿼리 → 주문번호별 항목개수를 반환
# 2. 외부쿼리 → 주문별 항목개수의 최대값, 최소값, 평균값 조회

MAX(items)|MIN(items)|FLOOR(AVG(items))|
----------+----------+-----------------+
        18|         1|                9|

 

예제 5)

# 주문번호별 총금액이 60000 이상인 주문 조회 
SELECT 
	o.orderNumber, 
	SUM(od.priceEach * od.quantityOrdered) AS amount
FROM
	orders o
JOIN
	orderdetails od USING (orderNumber)
GROUP BY 
	o.orderNumber
HAVING 
	SUM(od.priceEach * od.quantityOrdered) > 60000;

orderNumber|amount  |
-----------+--------+
      10165|67392.85|
      10287|61402.00|
      10310|61234.67|

# 주문번호별 총금액이 60000 이상인 주문을 한 고객 조회 
SELECT 
	customerNumber, 
	customerName
FROM
	customers c
WHERE
	EXISTS 
	(
		SELECT 
			o.orderNumber, 
			SUM(od.priceEach * od.quantityOrdered)
		FROM
			orders o
			JOIN orderdetails od USING (orderNumber)
		WHERE
			o.customerNumber = c.customerNumber		# 상관 서브쿼리
		GROUP BY 
			o.orderNumber
		HAVING 
			SUM(od.priceEach * od.quantityOrdered) > 60000
	);

customerNumber|customerName           |
--------------+-----------------------+
           148|Dragon Souveniers, Ltd.|
           259|Toms Spezialitäten, Ltd|
           298|Vida Sport, Ltd        |