Practical SQL: Subqueries in FROM and SELECT Clauses
- Subquery Basics
- Subqueries in the FROM Clause
- Subqueries in the SELECT Clause
In addition to subqueries in the WHERE and HAVING clauses, the ANSI standard allows a subquery in the FROM clause, and some relational database management systems (RDBMSs) permit a subquery in the SELECT clause. This article, derived from The Practical SQL Handbook, 4th edition (Addison Wesley), covers FROM and SELECT subqueries.
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Before looking at FROM and SELECT, let's review subquery basics.
Subquery Basics
Subqueries are queries nested inside other queries, marked off with parentheses, and sometimes referred to as "inner" queries within "outer" queries. Most often, you see subqueries in WHERE or HAVING clauses.
WHERE Clause
For example, you can use a subquery to calculate a value and then compare that value to an expression in the WHERE clause of the outer query. The following query finds books with prices greater than the average price. If you ran the subquery alone, you'd see that the average price is $27.64. (Unless otherwise noted, queries are run on Microsoft SQL Server 7.0.)
select title, price from titles where price > (select avg(price) from titles) order by price title price ------------------------------------------------------------- ---------- The Busy Executive's Database Guide 29.99 Prolonged Data Deprivation: Four Case Studies 29.99 Silicon Valley Gastronomic Treats 29.99 Sushi, Anyone? 29.99 Straight Talk About Computers 29.99 Secrets of Silicon Valley 40.00 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 40.95 Computer Phobic and Non-Phobic Individuals: Behavior Variations 41.59 But Is It User Friendly? 42.95 (9 row(s) affected)
HAVING Clause
Subqueries in HAVING clauses limit group result rows. If you are interested in the types of books supported by multiple publishers, you might write a query like this, figuring the number of publishers associated with each type of book (the AS assigns a display label to the COUNT expression):
select type, count(pub_id) as pubs_by_type from titles where type is not null group by type type pubs_by_type ------------ ----------- business 4 mod_cook 2 popular_comp 3 psychology 5 trad_cook 3 (5 row(s) affected)
To see only the most popular types (the ones shared by more than three publishers, say), limit the display with a HAVING clause.
select type, count(pub_id) as pubs_by_type from titles where type is not null group by type having count(type)> 3 type pubs_by_type ------------ ------------ business 4 psychology 5 (2 row(s) affected)