Home > Articles > Data > SQL

Practical SQL: Subqueries in FROM and SELECT Clauses

  • Print
  • + Share This

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.

From the author of

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)          
  • + Share This
  • 🔖 Save To Your Account