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)
