SELECT 문 안에 포함되는 SELECT 문
질의문과 갱신문 둘 다 사용가능
메인쿼리에서 서브쿼리를 참조할 수 없음
단일값, 다중값, 다중행으로 나뉨
비연관, 연관 서브쿼리로 구분 가능
SELECT name 상품명, MSRP '권장 소비자가격'
FROM products
WHERE MSRP >=(
SELECT AVG(MSRP) * 2
FROM products
)
ORDER BY MSRP;
SELECT officeCode, city
FROM offices
WHERE officeCode = ANY (
SELECT officeCode
FROM employees
WHERE lastName = 'Patterson'
)
ORDER BY officeCode;
SELECT productLine 상품라인, name 상품명, MSRP 소비자가격
FROM products
WHERE (productLine, MSRP) IN (
SELECT productLine, MIN(MSRP)
FROM products
GROUP BY productLine
)
ORDER BY productLine, name;
SELECT productLine 상품라인, name 상품명, MSRP 소비자가격
FROM products X
WHERE MSRP = (
SELECT MIN(MSRP)
FROM products Y
WHERE Y.productLine = X.productLine
)
ORDER BY productLine, name;
SELECT name
FROM customers C
WHERE customerId = ANY (
SELECT customerId
FROM orders O
WHERE O.customerId = C.customerId AND
O.status IN (‘Cancelled', 'On Hold')
);
SELECT name
FROM customers C
WHERE EXISTS (
SELECT *
FROM orders O
WHERE O.customerId = C.customerId AND
YEAR(orderDate)=2003 AND MONTH(orderDate)=1
);