In Transact-SQL, a subquery can be substituted anywhere an expression can be used in SELECT, UPDATE, INSERT, and DELETE statements, except in an ORDER BY list.
The following example illustrates how you might use this enhancement. This query finds the price of a popular computer book, the average price of all books, and the difference between the price of the book and the average price of all books.
USE pubs
SELECT title, price,
(SELECT AVG(price) FROM titles) AS average,
price-(SELECT AVG(price) FROM titles) AS difference
FROM titles
WHERE type='popular_comp'
Here is the result set:
title price average difference
------------------------ -------------- -------------- ----------------
But Is It User Friendly? 22.95 14.77 8.18
Secrets of Silicon Valley 20.00 14.77 5.23
Net Etiquette (null) 14.77 (null)
(3 row(s) affected)