What is Use scalar or multi-valued subqueries

A scalar subquery is an inner SELECT statement within an outer query, written to return a single value. Scalar subqueries might be used anywhere in an outer T-SQL statement where a single-valued expression is permitted—such as in a SELECT clause, a WHERE clause, a HAVING clause, or even a FROM clause. They can also be used in data modification statements, such as UPDATE or DELETE.

Multi-valued subqueries, as the name suggests, can return more than one row. However they still return a single column.

Scalar subqueries

Suppose you want to retrieve the details of the last order that was placed, on the assumption that it is the one with the highest SalesOrderID value.

To find the highest SalesOrderID value, you might use the following query:

SQLCopy

SELECT MAX(SalesOrderID)
FROM Sales.SalesOrderHeader

This query returns a single value that indicates the highest value for an OrderID in the SalesOrderHeader table.

To get the details for this order, you might need to filter the SalesOrderDetails table based on whatever value is returned by the query above. You can accomplish this task by nesting the query to retrieve the maximum SalesOrderID within the WHERE clause of a query that retrieves the order details.

SQLCopy

SELECT SalesOrderID, ProductID, OrderQty
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 
   (SELECT MAX(SalesOrderID)
    FROM Sales.SalesOrderHeader);

To write a scalar subquery, consider the following guidelines:

• To denote a query as a subquery, enclose it in parentheses.
• Multiple levels of subqueries are supported in Transact-SQL. In this module, we’ll only consider two-level queries (one inner query within one outer query), but up to 32 levels are supported.
• If the subquery returns no rows (an empty set), the result of the subquery is a NULL. If it is possible in your scenario for no rows to be returned, you should ensure your outer query can gracefully handle a NULL, in addition to other expected results.
• The inner query should generally return a single column. Selecting multiple columns in a subquery is almost always an error. The only exception is if the subquery is introduced with the EXISTS keyword.

A scalar subquery can be used anywhere in a query where a value is expected, including the SELECT list. For example, we could extend the query that retrieved details for the most recent order to include the average quantity of items that is ordered, so we can compare the quantity ordered in the most recent order with the average for all orders.

SQLCopy

SELECT SalesOrderID, ProductID, OrderQty,
    (SELECT AVG(OrderQty)
     FROM SalesLT.SalesOrderDetail) AS AvgQty
FROM SalesLT.SalesOrderDetail
WHERE SalesOrderID = 
    (SELECT MAX(SalesOrderID)
     FROM SalesLT.SalesOrderHeader);