Subqueries Instead of Expressions

In Transact-SQL, you can substitute a subquery almost anywhere you can use an expression in a select, update, insert, or delete statement.

You cannot use a subquery in an order by list, or as an expression in the values list in an insert statement.

The following statement shows how to find the titles and types of books that have been written by authors living in California and that are also published there:

select title, type 
from titles 
where title in 
   (select title 
    from titles, titleauthor, authors 
    where titles.title_id = titleauthor.title_id 
    and titleauthor.au_id = authors.au_id 
    and authors.state = "CA") 
and title in 
   (select title 
    from titles, publishers 
    where titles.pub_id = publishers.pub_id 
    and publishers.state = "CA") 
title                                 type
-----------------------------------   ----------
The Busy Executive’s Database Guide   business
Cooking with Computers: 
     Surreptitious Balance Sheets     business
Straight Talk About Computers         business
But Is It User Friendly?              popular_comp
Secrets of Silicon Valley             popular_comp
Net Etiquette                         popular_comp
(6 rows affected)

The following statement selects the book titles that have had more than 5000 copies sold, lists their prices, and the price of the most expensive book:

select title, price, 
    (select max(price) from titles)
    from titles
    where total_sales > 5000
title                                price
-----------------------------------  -----  ------ 
You Can Combat Computer Stress!       2.99   22.95
The Gourmet Microwave                 2.99   22.95
But Is It User Friendly?             22.95   22.95
Fifty Years in Buckingham Palace 
    Kitchens                         11.95   22.95
(4 rows affected)