Scalar and multi-row subqueries

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Compare a value with one-row and multi-row subquery results.

Run the example

SELECT ProductID, ProductName, ListPrice
FROM dbo.Products
WHERE ListPrice > (SELECT AVG(ListPrice) FROM dbo.Products)
ORDER BY ProductID;

Read it step by step

1. The inner AVG returns one scalar price from all 15 products.
2. The outer WHERE compares each ListPrice with that single value.
3. ORDER BY ProductID makes the matching rows repeatable. IN can compare against a multi-row subquery instead.

Check the result

The average is about 74.37; the result contains products priced above it.

Practice

Return products whose CategoryID is among the categories named Computing or Office.

SELECT ProductID, ProductName
FROM dbo.Products
WHERE CategoryID IN (
    SELECT CategoryID FROM dbo.Categories
    WHERE CategoryName IN (N'Computing', N'Office')
)
ORDER BY ProductID;