Transact-SQL User's Guide

Home

Chapter 5 Subqueries: Using Queries Within
Other Queries

Transact-SQL User's Guide

Chapter 5 Subqueries: Using Queries Within Other Queries

How subqueries work

Subqueries, also called inner queries, appear within a where or having clause of another SQL statement or in the select list of a statement. You can use subqueries to handle query requests that are expressed as the results of other queries. A statement that includes a subquery operates on rows from one table, based on its evaluation of the subquery's select list, which can refer either to the same table as the outer query, or to a different table. In Transact-SQL, a subquery can also be used almost anywhere an expression is allowed, if the subquery returns a single value. A case expression can also include a subquery.

For example, this subquery lists the names of all authors whose royalty split is more than $75:

select au_fname, au_lname
from authors 
where au_id in 
   (select au_id 
    from titleauthor 
    where royaltyper > 75)

select statements that contain one or more subqueries are sometimes called nested queries or nested select statements.

You can formulate as joins many SQL statements that include a subquery. Other questions can be posed only with subqueries. Some people find subqueries easier to understand. Other SQL users avoid subqueries whenever possible. You can choose whichever formulation you prefer.

The result of a subquery that returns no values is NULL. If a subquery returns NULL, the query failed.

Subquery syntax

Always enclose the select statement of a subquery in parentheses. The select syntax for a subquery is somewhat restricted:

(select [all | distinct] subquery_select_list 
     [from [[database.]owner.]{table_name |view_name}
       [({index indexation | prefetch size | [lru | mru]})]} 
                [holdlock | noholdlock] [shared]
            [,[[database.]owner.]{table_name | view_name}
        [{index indexation | prefetch size | [lru | mru]})]}
                [holdlock | noholdlock] [shared]]...] 
     [where search_conditions] 
     [group by aggregate_free_expression [, 
                aggregate_free_expression]...] 
     [having search_conditions])

Subquery restrictions

A subquery is subject to the following restrictions:

The subquery_select_list can consist of only one column name, except in the exists subquery, where an (*) is usually used in place of the single column name. Do not specify more than one column name. Qualify column names with table or view names if there is ambiguity about the table or view to which they belong.
Subqueries can be nested inside the where or having clause of an outer select, insert, update, or delete statement, inside another subquery, or in a select list. Alternatively, you can write many statements that contain subqueries as joins; Adaptive Server processes such statements as joins.
In Transact-SQL, a subquery can appear almost anywhere an expression can be used, if it returns a single value.
You cannot use subqueries in an order by, group by, or compute by list.
You cannot include a for browse clause or a union in a subquery.
The select list of an inner subquery introduced with a comparison operator can include only one expression or column name, and the subquery must return a single value. The column you name in the where clause of the outer statement must be join-compatible with the column you name in the subquery select list.
text and image datatypes are not allowed in subqueries.
Subqueries cannot manipulate their results internally, that is, a subquery cannot include the order by clause, the compute clause, or the into keyword.
Correlated (repeating) subqueries are not allowed in the select clause of an updatable cursor defined by declare cursor.
There is a limit of 16 nesting levels.
The maximum number of subqueries on each side of a union is 16.
The where clause of a subquery can contain an aggregate function only if the subquery is in a having clause of an outer query and the aggregate value is a column from a table in the from clause of the outer query.
The result expression from a subquery is subject to the same limits as for any expression. The maximum length of an expression is 16K. For more information, see "Expressions, Identifiers, and Wildcard Characters" on page 219 of the Adaptive Server Reference Manual.

Example of using a subquery

Suppose you want to find the books that have the same price as Straight Talk About Computers. First, you find the price of Straight Talk:

select price 
from titles 
where title = "Straight Talk About Computers"

price 
------------- 
       $19.99 
 
(1 row affected)

You use the results of the first query in a second query to find all the books that cost the same as Straight Talk:

select title, price 
from titles 
where price = $19.99

title                                         price 
------------------------------------------    -----
The Busy Executive's Database Guide           19.99 
Straight Talk About Computers                 19.99 
Silicon Valley Gastronomic Treats             19.99 
Prolonged Data Deprivation: Four Case Studies 19.99 
  
(4 rows affected)

You can use a subquery to receive the same results in only one step:

select title, price 
from titles 
where price = 
   (select price 
    from titles 
    where title = "Straight Talk About Computers")

title                                         price
---------------------------------------       -----
The Busy Executive's Database Guide           19.99 
Straight Talk About Computers                 19.99 
Silicon Valley Gastronomic Treats             19.99 
Prolonged Data Deprivation: Four Case Studies 19.99 
 
(4 rows affected)

Qualifying column names

Column names in a statement are implicitly qualified by the table referenced in the from clause at the same level. In the following example, the table name publishers implicitly qualifies the pub_id column in the where clause of the outer query. The reference to pub_id in the select list of the subquery is qualified by the subquery's from clause--that is, by the titles table:

select pub_name 
from publishers 
where pub_id in 
   (select pub_id 
    from titles 
    where type = "business")

This is what the query looks like with the implicit assumptions spelled out:

select pub_name 
from publishers 
where publishers.pub_id in 
   (select titles.pub_id 
    from titles 
    where type = "business")

It is never wrong to state the table name explicitly, and you can override implicit assumptions about table names by using explicit qualifications.

Subqueries with correlation names

As discussed in Chapter 4, "Joins: Retrieving Data from Several Tables," table correlation names are required in self-joins because the table being joined to itself appears in two different roles. You can also use correlation names in nested queries that refer to the same table in both an inner query and an outer query.

For example, you can find authors who live in the same city as Livia Karsen by using this subquery:

select au1.au_lname, au1.au_fname, au1.city 
from authors au1 
where au1.city in 
   (select au2.city 
    from authors au2 
    where au2.au_fname = "Livia" 
    and au2.au_lname = "Karsen")

au_lname     au_fname     city 
-----------  ---------    ------- 
Green        Marjorie     Oakland 
Straight     Dick         Oakland 
Stringer     Dirk         Oakland 
MacFeather   Stearns      Oakland 
Karsen       Livia        Oakland 
 
(5 rows affected)

Explicit correlation names make it clear that the reference to authors in the subquery is not the same as the reference to authors in the outer query.

Without explicit correlation, the subquery is:

select au_lname, au_fname, city 
from authors 
where city in 
   (select city 
    from authors 
    where au_fname = "Livia" 
    and au_lname = "Karsen")

Alternatively, you can state the above query, as well as other statements in which the subquery and the outer query refer to the same table, as self-joins:

select au1.au_lname, au1.au_fname, au1.city 
from authors au1, authors au2 
where au1.city = au2.city 
and au2.au_lname = "Karsen" 
and au2.au_fname = "Livia"

A subquery restated as a join may not return the results in the same order; additionally, the join may require the distinct keyword to eliminate duplicates.

Multiple levels of nesting

A subquery can include one or more subqueries. You can nest up to 16 subqueries in a statement.

For example: "Find the names of authors who have participated in writing at least one popular computing book:"

select au_lname, au_fname 
from authors 
where au_id in 
   (select au_id 
    from titleauthor 
    where title_id in 
       (select title_id 
        from titles 
        where type = "popular_comp") )

au_lname               au_fname 
---------------------- ------------ 
Carson                 Cheryl 
Dull                   Ann 
Locksley               Chastity 
Hunter                 Sheryl 
 
(4 rows affected)

The outermost query selects all author names. The next query finds the authors' IDs, and the innermost query returns the title ID numbers PC1035, PC8888, and PC9999.

You can also express this query as a join:

select au_lname, au_fname 
from authors, titles, titleauthor 
where authors.au_id = titleauthor.au_id 
and titles.title_id = titleauthor.title_id 
and type = "popular_comp"

Subqueries in update, delete, and insert statements

You can nest subqueries in update, delete, and insert statements as well as in select statements.

Running the sample queries in this section changes the pubs2 database. Ask a System Administrator to help you get a clean copy of the sample database.

The following query doubles the price of all books published by New Age Books. The statement updates the titles table; its subquery references the publishers table.

update titles 
set price = price * 2 
where pub_id in 
   (select pub_id 
    from publishers 
    where pub_name = "New Age Books")

An equivalent update statement using a join is:

update titles 
set price = price * 2 
from titles, publishers 
where titles.pub_id = publishers.pub_id 
and pub_name = "New Age Books"

You can remove all records of sales of business books with this nested select statement:

delete salesdetail 
where title_id in 
   (select title_id 
    from titles 
    where type = "business")

An equivalent delete statement using a join is:

delete salesdetail 
from salesdetail, titles 
where salesdetail.title_id = titles.title_id 
and type = "business"

Subqueries in conditional statements

You can use subqueries in conditional statements. The preceding subquery that removed all records of sales of business books can be rewritten, as shown in the next example, to check for the records before deleting them:

if exists (select title_id 
    from titles 
    where type = "business")
begin
    delete salesdetail 
    where title_id in 
      (select title_id 
       from titles 
       where type = "business") 
end

Using 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. 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)


	Expand Search