Subqueries Used in Place of an Expression

Subqueries Used in Place of an Expression

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)