🕹️ 실습 링크 바로가기
위의 사이트에서 실습이 안 될 시 👉 클릭해주세요!
1. 비상관 서브쿼리
SELECT
CategoryID, CategoryName, Description,
(SELECT ProductName FROM Products WHERE ProductID = 1)
FROM Categories;
SELECT * FROM Products
WHERE Price < (
SELECT AVG(Price) FROM Products
);
SELECT
CategoryID, CategoryName, Description
FROM Categories
WHERE
CategoryID =
(SELECT CategoryID FROM Products
WHERE ProductName = 'Chais');
SELECT
CategoryID, CategoryName, Description
FROM Categories
WHERE
CategoryID IN
(SELECT CategoryID FROM Products
WHERE Price > 50);
| 연산자 | 의미 |
|---|---|
| ~ ALL | 서브쿼리의 모든 결과에 대해 ~하다 |
| ~ ANY | 서브쿼리의 하나 이상의 결과에 대해 ~하다 |
SELECT * FROM Products
WHERE Price > ALL (
SELECT Price FROM Products
WHERE CategoryID = 2
);
SELECT
CategoryID, CategoryName, Description
FROM Categories
WHERE
CategoryID = ANY
(SELECT CategoryID FROM Products
WHERE Price > 50);
2. 상관 서브쿼리
SELECT
ProductID, ProductName,
(
SELECT CategoryName FROM Categories C
WHERE C.CategoryID = P.CategoryID
) AS CategoryName
FROM Products P;
SELECT
SupplierName, Country, City,
(
SELECT COUNT(*) FROM Customers C
WHERE C.Country = S.Country
) AS CustomersInTheCountry,
(
SELECT COUNT(*) FROM Customers C
WHERE C.Country = S.Country
AND C.City = S.City
) AS CustomersInTheCity
FROM Suppliers S;
SELECT
CategoryID, CategoryName,
(
SELECT MAX(Price) FROM Products P
WHERE P.CategoryID = C.CategoryID
) AS MaximumPrice,
(
SELECT AVG(Price) FROM Products P
WHERE P.CategoryID = C.CategoryID
) AS AveragePrice
FROM Categories C;
SELECT
ProductID, ProductName, CategoryID, Price
-- ,(SELECT AVG(Price) FROM Products P2
-- WHERE P2.CategoryID = P1.CategoryID)
FROM Products P1
WHERE Price < (
SELECT AVG(Price) FROM Products P2
WHERE P2.CategoryID = P1.CategoryID
);
EXISTS / NOT EXISTS 연산자
SELECT
CategoryID, CategoryName
-- ,(SELECT MAX(P.Price) FROM Products P
-- WHERE P.CategoryID = C.CategoryID
-- ) AS MaxPrice
FROM Categories C
WHERE EXISTS (
SELECT * FROM Products P
WHERE P.CategoryID = C.CategoryID
AND P.Price > 80
);