SELECT ProductName, UnitPrice,
UnitPrice - (SELECT AVG(UnitPrice) FROM dbo.Products) AS DiffFromAvg
FROM dbo.Products;
12.2 Multi-valued Subquery (IN, ANY, ALL)
SELECT * FROM dbo.Customers
WHERE CustomerID IN (SELECT CustomerID FROM dbo.Orders WHERE Status = 'Delivered');
SELECT * FROM dbo.Products WHERE UnitPrice > ALL (SELECT UnitPrice FROM dbo.Products WHERE CategoryID = 2);
SELECT * FROM dbo.Products WHERE UnitPrice > ANY (SELECT UnitPrice FROM dbo.Products WHERE CategoryID = 2);
12.3 Correlated Subquery
-- Products priced above their category average
SELECT p.ProductName, p.UnitPrice, p.CategoryID
FROM dbo.Products p
WHERE p.UnitPrice > (SELECT AVG(p2.UnitPrice) FROM dbo.Products p2 WHERE p2.CategoryID = p.CategoryID);
12.4 EXISTS / NOT EXISTS
SELECT c.* FROM dbo.Customers c
WHERE EXISTS (SELECT 1 FROM dbo.Orders o WHERE o.CustomerID = c.CustomerID);
-- NOT EXISTS is NULL-safe (unlike NOT IN)
SELECT c.* FROM dbo.Customers c
WHERE NOT EXISTS (SELECT 1 FROM dbo.Orders o WHERE o.CustomerID = c.CustomerID);
12.5 Derived Tables
SELECT t.CustomerID, t.TotalSpent
FROM (SELECT o.CustomerID, SUM(oi.LineTotal) AS TotalSpent
FROM dbo.Orders o JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
GROUP BY o.CustomerID) AS t
WHERE t.TotalSpent > 10000;
12.6 Subqueries in DML
UPDATE dbo.Products SET IsActive = 0
WHERE ProductID NOT IN (SELECT ProductID FROM dbo.OrderItems WHERE ProductID IS NOT NULL);