Subqueries: scalar, list and correlated

A subquery is a SELECT in brackets, used inside another statement. SQL Server runs it and uses its result as a value, a list or a table. Which products cost more than the average?

1SELECT Name, Price2FROM dbo.Products3WHERE Price > (SELECT AVG(Price) FROM dbo.Products)4ORDER BY Price DESC;

Name                 Price
-------------------  -----
Cordless drill       89.00
Masonry paint 10 L   54.00
Plywood sheet 18 mm  42.00

You could not write WHERE Price > AVG(Price): WHERE cannot use aggregates. The subquery works out the average first (22.254166) as a separate query, and the outer query compares each row with that number.

Scalar subqueries

A subquery that returns one value can go wherever a value can, including the SELECT list:

1SELECT Name, Price,2       (SELECT MAX(Price) FROM dbo.Products) AS Dearest,3       CAST(Price * 100 / (SELECT MAX(Price) FROM dbo.Products) AS decimal(5,1)) AS PctOfDearest4FROM dbo.Products5WHERE CategoryId = 1;

Name              Price  Dearest  PctOfDearest
----------------  -----  -------  ------------
Claw hammer       18.50    89.00          20.8
Cordless drill    89.00    89.00         100.0
Tape measure 8 m   9.75    89.00          11.0

This is also the answer to the last lesson’s puzzle, “which product is the dearest?”: find the maximum, then the row that has it.

1SELECT Name, Price2FROM dbo.Products3WHERE Price = (SELECT MAX(Price) FROM dbo.Products);

Name            Price
--------------  -----
Cordless drill  89.00

If a subquery used with = returns more than one row, the statement fails when it runs:

1SELECT Name2FROM dbo.Products3WHERE CategoryId = (SELECT CategoryId FROM dbo.Categories WHERE Name LIKE N'T%');

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

“Tools” and “Timber” both start with T. A scalar subquery that returns no rows is not an error: it gives NULL, and a comparison with NULL is never true, so the outer query quietly returns nothing.

Lists with IN

A subquery that returns one column of many rows can feed IN. Which customers have bought a Tools product?

1SELECT FirstName, LastName2FROM dbo.Customers3WHERE CustomerId IN (4    SELECT o.CustomerId5    FROM dbo.Orders AS o6    JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId7    JOIN dbo.Products   AS p  ON p.ProductId = oi.ProductId8    WHERE p.CategoryId = 19)10ORDER BY FirstName;

FirstName  LastName
---------  --------
Asha       Patel
Farid      Khan
Zoë        Müller

Asha bought three tools, but she appears once. IN only asks “is this value in the list?”, so duplicates in the list do not repeat rows. A join to the same tables would have returned Asha three times.

Derived tables: a subquery in FROM

A subquery in FROM is treated as a table for the rest of the query. It must have an alias, and every column must have a name. This finds the average order value, which needs two levels of aggregation: first the total of each order, then the average of those totals:

1SELECT COUNT(*)             AS Orders,2       AVG(t.OrderTotal)    AS AvgOrderValue,3       MAX(t.OrderTotal)    AS BiggestOrder4FROM (5    SELECT OrderId, SUM(Quantity * UnitPrice) AS OrderTotal6    FROM dbo.OrderItems7    GROUP BY OrderId8) AS t;

Orders  AvgOrderValue  BiggestOrder
------  -------------  ------------
    10     133.325000        524.00

Leave out the column name and SQL Server stops you:

1SELECT AVG(t.OrderTotal)2FROM (3    SELECT OrderId, SUM(Quantity * UnitPrice)4    FROM dbo.OrderItems5    GROUP BY OrderId6) AS t;

Msg 8155, Level 16, State 2, Line 6
No column name was specified for column 2 of 't'.

Msg 207, Level 16, State 1, Line 1
Invalid column name 'OrderTotal'.

Correlated subqueries

The subqueries so far could run on their own. A correlated subquery refers to a column of the outer query, so it has a different answer for each outer row. Conceptually it runs once per row. Which products cost more than the average of their own category?

1SELECT p.Name, p.CategoryId, p.Price2FROM dbo.Products AS p3WHERE p.Price > (4    SELECT AVG(p2.Price)5    FROM dbo.Products AS p26    WHERE p2.CategoryId = p.CategoryId7)8ORDER BY p.CategoryId;

Name                 CategoryId  Price
-------------------  ----------  -----
Cordless drill                1  89.00
Cement 25 kg                  2   7.20
Plywood sheet 18 mm           3  42.00
Wood screws (200)             4  11.90
Masonry paint 10 L            5  54.00

SELECT AVG(p2.Price)
FROM dbo.Products AS p2
WHERE p2.CategoryId = 1  -- p.CategoryId

The two aliases are essential. p is the product being tested and p2 ranges over the products in its category. Without them, CategoryId = CategoryId would compare p2 with itself and always be true.

A correlated subquery in SELECT adds a calculated value for each row, here each customer’s number of orders and latest order date:

1SELECT c.FirstName,2       (SELECT COUNT(*)       FROM dbo.Orders AS o WHERE o.CustomerId = c.CustomerId) AS Orders,3       (SELECT MAX(OrderDate) FROM dbo.Orders AS o WHERE o.CustomerId = c.CustomerId) AS LastOrder4FROM dbo.Customers AS c5ORDER BY c.CustomerId;

FirstName  Orders  LastOrder
---------  ------  ----------
Asha            2  2024-03-01
Ben             2  2024-06-01
Chloe           1  2024-04-20
Dev             2  2024-05-05
Ella            0  NULL
Farid           2  2024-06-12
Grace           0  NULL
Zoë             1  2024-05-18

Customers with no orders get 0 and NULL, with no LEFT JOIN needed: the subquery simply finds no rows for them.

1SELECT t.OrderId, t.OrderTotal2FROM (3    SELECT OrderId, SUM(Quantity * UnitPrice) AS OrderTotal4    FROM dbo.OrderItems5    GROUP BY OrderId6) AS t7WHERE t.OrderTotal > (8    SELECT AVG(x.OrderTotal)9    FROM (10        SELECT SUM(Quantity * UnitPrice) AS OrderTotal11        FROM dbo.OrderItems12        GROUP BY OrderId13    ) AS x14)15ORDER BY t.OrderTotal DESC;

OrderId  OrderTotal
-------  ----------
      4      524.00
      7      199.50