In Transact-SQL, you can substitute a subquery almost anywhere you can use an expression in a select, update, insert, or delete statement. For example, a subquery can compare with a column from the inner table of an outer join.
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)
