In Transact-SQL, a subquery can be substituted anywhere an expression can be used in SELECT, UPDATE, INSERT, and DELETE statements. A subquery cannot be used in an ORDER BY list.
The following example illustrates how you might use this enhancement. This statement finds the price of a book, the average price of all books, and the difference between the price of a book and the average price of all books:
SELECT title, price, average=(SELECT AVG(price) FROM titles), difference=price-(SELECT AVG(price) FROM titles) FROM titles WHERE type="popular_comp"
title |
price |
average |
difference |
------------------------ |
--------- |
---------- |
------------ |
But Is It User Friendly? |
22.95 |
23.94 |
-0.99 |
Secrets of Silicon Valley |
20.00 |
23.94 |
-3.94 |
Net Etiquette |
(null) |
23.94 |
(null) |
(3 row(s) affected)