Subquery

Subquery Fundamentals

A subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. In this example a subquery is used as a column expression named MaxUnitPrice in a SELECT statement.

SELECT Ord.OrderID, Ord.OrderDate,
       (SELECT MAX(OrdDet.UnitPrice)
        FROM Northwind.dbo.[Order Details] AS OrdDet
        WHERE Ord.OrderID = OrdDet.OrderID) AS MaxUnitPrice
FROM Northwind.dbo.Orders AS Ord

A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. In Transact-SQL, there is usually no performance difference between a statement that includes a subquery and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases, a join approach would yield better results. This is an example showing both a subquery SELECT and a join SELECT that return the same result set:

/* SELECT statement built using a subquery. */
SELECT ProductName
FROM Northwind.dbo.Products
WHERE UnitPrice =
      (SELECT UnitPrice
       FROM Northwind.dbo.Products
       WHERE ProductName = 'Sir Rodney''s Scones')

/* SELECT statement built using a join that returns
   the same result set. */
SELECT Prd1.ProductName
FROM Northwind.dbo.Products AS Prd1
     JOIN Northwind.dbo.Products AS Prd2
       ON (Prd1.UnitPrice = Prd2.UnitPrice)
WHERE Prd2.ProductName = 'Sir Rodney''s Scones'

A subquery nested in the outer SELECT statement has the following components:

The SELECT query of a subquery is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified.

A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query. Individual queries may not support nesting up to 32 levels. A subquery can appear anywhere an expression can be used, if it returns a single value.

If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output (the select list of the outer query).

Statements that include a subquery usually take one of these formats:

In some Transact-SQL statements, the subquery can be evaluated as if it were an independent query. Conceptually, the subquery results are substituted into the outer query (although this is not necessarily how Microsoft® SQL Server™ actually processes Transact-SQL statements with subqueries).

There are three basic types of subqueries. Those that:

Subquery Rules

A subquery is subject to a number of restrictions:

Qualifying Column Names in Subqueries

In the following example, the pub_id column in the WHERE clause of the outer query is implicitly qualified by the table name in the outer query's FROM clause, publishers. 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.

USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id NOT IN
   (SELECT pub_id
   FROM titles
   WHERE type = 'business'

The general rule is that column names in a statement are implicitly qualified by the table referenced in the FROM clause at the same level.

Here's what the query looks like with these implicit assumptions specified:

USE pubs
SELECT pub_name
FROM publishers
WHERE publishers.pub_id NOT IN
   (SELECT titles.pub_id
   FROM titles
   WHERE type = 'business')

It is never wrong to state the table name explicitly, and it is always possible to override implicit assumptions about table names with explicit qualifications.

Subquery Types

Subqueries can be specified in many places:

Subqueries with Aliases

Many statements in which the subquery and the outer query refer to the same table can be stated as self-joins (joining a table to itself). For example, you can find authors who live in the same city as Livia Karsen by using a subquery:

USE pubs
SELECT au_lname, au_fname, city
FROM authors
WHERE city IN
   (SELECT city
   FROM authors
   WHERE au_fname = 'Livia'
      AND au_lname = 'Karsen')

Here is the result set:

au_lname                                 au_fname             city       
---------------------------------------- -------------------- ---------- 
Green                                    Marjorie             Oakland    
Straight                                 Dean                 Oakland    
Stringer                                 Dirk                 Oakland    
MacFeather                               Stearns              Oakland    
Karsen                                   Livia                Oakland    

(5 row(s) affected)

Or you can use a self-join:

USE pubs
SELECT au1.au_lname, au1.au_fname, au1.city
FROM authors AS au1 INNER JOIN authors AS au2 ON au1.city = au2.city
   AND au2.au_lname = 'Karsen'
   AND au2.au_fname = 'Livia'

Table aliases are required because the table being joined to itself appears in two different roles. Aliases can also be used in nested queries that refer to the same table in an inner and outer query.

USE pubs
SELECT au1.au_lname, au1.au_fname, au1.city
FROM authors AS au1
WHERE au1.city in
   (SELECT au2.city
   FROM authors AS au2
   WHERE au2.au_fname = 'Livia'
      AND au2.au_lname = 'Karsen')

Explicit aliases make it clear that reference to authors in the subquery does not mean the same thing as the reference in the outer query.

Subqueries with IN

The result of a subquery introduced with IN (or with NOT IN) is a list of zero or more values. After the subquery returns results, the outer query makes use of them.

This query finds the names of the publishers who have published business books.

USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id IN
   (SELECT pub_id
   FROM titles
   WHERE type = 'business')

Here is the result set:

pub_name                                 
---------------------------------------- 
Algodata Infosystems                     
New Moon Books                           

(2 row(s) affected)

This statement is evaluated in two steps. First, the inner query returns the identification numbers of the publishers that have published business books (1389 and 0736). Second, these values are substituted into the outer query, which finds the names that go with the identification numbers in publishers.

USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id in ('1389', '0736')

One difference in using a join rather than a subquery for this and similar problems is that the join lets you show columns from more than one table in the result. For example, if you want to include the titles of the business books in the result, you must use a join version.

USE pubs
SELECT pub_name, title
FROM publishers INNER JOIN titles ON publishers.pub_id = titles.pub_id
   AND type = 'business'

Here is the result set:

pub_name               title                                             
---------------------- ------------------------------------------------- 
Algodata Infosystems   The Busy Executive's Database Guide               
Algodata Infosystems   Cooking with Computers: Surreptitious Balance    
                     Sheets                             
New Moon Books         You Can Combat Computer Stress!                   
Algodata Infosystems   Straight Talk About Computers                     

(4 row(s) affected)

This query shows the join produces four rows, not two as in the preceding subquery.

Here is another example of a query that can be formulated with either a subquery or a join. This query finds the names of all second authors who live in California and who receive less than 30 percent of the royalties for a book.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE state = 'CA'
   AND au_id IN
      (SELECT au_id
      FROM titleauthor
      WHERE royaltyper < 30
         AND au_ord = 2)

Here is the result set:

au_lname                                 au_fname             
---------------------------------------- -------------------- 
MacFeather                               Stearns              

(1 row(s) affected)

The inner query is evaluated, producing the ID numbers of the three authors who meet the subquery qualifications. The outer query is then evaluated. Notice that you can include more than one condition in the WHERE clause of both the inner and the outer query.

Using a join, the same query is expressed like this:

USE pubs
SELECT au_lname, au_fname
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
WHERE state = 'CA'
   AND royaltyper < 30
   AND au_ord = 2

A join can always be expressed as a subquery. A subquery can often, but not always, be expressed as a join. This is because joins are symmetric: you can join table A to B in either order and get the same answer. The same is not true if a subquery is involved.

Subqueries with NOT IN

Subqueries introduced with the keyword NOT IN also return a list of zero or more values.

This query finds the names of the publishers who have not published business books.

USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id NOT IN
   (SELECT pub_id
   FROM titles
   WHERE type = 'business')

The query is exactly the same as the one in Subqueries with IN, except that NOT IN is substituted for IN. However, this statement cannot be converted to a join. The analogous not-equal join has a different meaning: It finds the names of publishers who have published some book that is not a business book. For information about interpreting the meaning of joins not based on equality, see Joining Three or More Tables.

Subqueries in UPDATE, DELETE, and INSERT Statements

Subqueries can be nested in UPDATE, DELETE, and INSERT statements, as well as in SELECT statements.

The following query doubles the price of all books published by New Moon Books. The query 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 Moon Books')

Here's an equivalent UPDATE statement using a join:

UPDATE titles
SET price = price * 2
FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id
   AND pub_name = 'New Moon Books'

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

DELETE sales
WHERE title_id IN
   (SELECT title_id
   FROM titles
   WHERE type = 'business')

Here's an equivalent DELETE statement using a join:

DELETE sales
FROM sales INNER JOIN titles ON sales.title_id = titles.title_id
   AND type = 'business'

Subqueries with Comparison Operators

Subqueries can be introduced with one of the comparison operators (=, < >, >, > =, <, ! >, ! <, or < =).

A subquery introduced with an unmodified comparison operator (a comparison operator not followed by ANY or ALL) must return a single value rather than a list of values, like subqueries introduced with IN. If such a subquery returns more than one value, Microsoft® SQL Server™ displays an error message.

To use a subquery introduced with an unmodified comparison operator, you must be familiar enough with your data and with the nature of the problem to know that the subquery will return exactly one value.

For example, if you assume each publisher is located in only one city, and you want to find the names of authors who live in the city in which Algodata Infosystems is located, you can write a statement with a subquery introduced with the simple = comparison operator.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city =
   (SELECT city
   FROM publishers
   WHERE pub_name = 'Algodata Infosystems')

Here is the result set:

au_lname      au_fname
--------      --------
Carson      Cheryl
Bennet      Abraham

(2 row(s) affected)

If, however, Algodata Infosystems was located in multiple cities, then an error message would result. Instead of the = comparison operator, an IN formulation could be used (= ANY also works).

Subqueries introduced with unmodified comparison operators often include aggregate functions, because these return a single value. For example, this statement finds the names of all books priced higher than the current minimum price.

USE pubs
SELECT DISTINCT title
FROM titles
WHERE price >
   (SELECT MIN(price)
   FROM titles)

Here is the result set:

title
----------------------------------------------------------
But Is It User Friendly?
Computer Phobic and Non-Phobic Individuals: Behavior Variations
Cooking with Computers: Surreptitious Balance Sheets
Emotional Security: A New Algorithm
Fifty Years in Buckingham Palace Kitchens
Is Anger the Enemy?
Life Without Fear
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
Prolonged Data Deprivation: Four Case Studies
Secrets of Silicon Valley
Silicon Valley Gastronomic Treats
Straight Talk About Computers
Sushi, Anyone?
The Busy Executive's Database Guide

(14 row(s) affected)

Because subqueries introduced with unmodified comparison operators must return a single value, they cannot include GROUP BY or HAVING clauses unless you know the GROUP BY or HAVING clause itself returns a single value. For example, this query finds the books priced higher than the lowest priced book that has a type 'trad_cook'.

USE pubs
SELECT DISTINCT title
FROM titles
WHERE price >
   (SELECT MIN(price)
   FROM titles
   GROUP BY type
   HAVING type = 'trad_cook')

Here is the result set:

title                                                                   
------------------------------------------------------------------------
But Is It User Friendly?                                                
Computer Phobic AND Non-Phobic Individuals: Behavior Variations         
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean         
Prolonged Data Deprivation: Four Case Studies                           
Secrets of Silicon Valley                                               
Silicon Valley Gastronomic Treats                                       
Straight Talk About Computers                                           
Sushi, Anyone?                                                          
The Busy Executive's Database Guide                                     

(9 row(s) affected)

Comparison Operators Modified by ANY, SOME, or ALL

Comparison operators that introduce a subquery can be modified by the keywords ALL or ANY. SOME is an SQL-92 standard equivalent for ANY.

Subqueries introduced with a modified comparison operator return a list of zero or more values and can include a GROUP BY or HAVING clause. These subqueries can be restated with EXISTS.

Using the > comparison operator as an example, >ALL means greater than every value--in other words, greater than the maximum value. For example, >ALL (1, 2, 3) means greater than 3. >ANY means greater than at least one value, that is, greater than the minimum. So >ANY (1, 2, 3) means greater than 1.

For a row in a subquery with >ALL to satisfy the condition specified in the outer query, the value in the column introducing the subquery must be greater than each value in the list of values returned by the subquery.

Similarly, >ANY means that for a row to satisfy the condition specified in the outer query, the value in the column that introduces the subquery must be greater than at least one of the values in the list of values returned by the subquery.

Note  This example can be run many different ways, as long as the inner query returns only one value.

USE pubs
-- Option 1 using MAX in the inner query
SELECT title
FROM titles
HAVING MAX(advance) > ALL
WHERE advance > ALL
   (
    SELECT MAX(advance)
    FROM publishers INNER JOIN titles ON 
      titles.pub_id = publishers.pub_id
    WHERE pub_name = 'Algodata Infosystems'
   )

-- Option 2 using GROUP BY and HAVING and no ALL 
USE pubs
SELECT title
FROM titles
GROUP BY title
HAVING MAX(advance) > 
   (
    SELECT MAX(advance)
    FROM publishers INNER JOIN titles ON 
      titles.pub_id = publishers.pub_id
    WHERE pub_name = 'Algodata Infosystems'
   )

The following query provides an example of a subquery introduced with a comparison operator modified by ANY. It finds the titles that received an advance larger than the minimum advance amount paid by Algodata Infosystems.

USE pubs
SELECT title
FROM titles
WHERE advance > ANY
   (SELECT advance
   FROM publishers INNER JOIN titles
   ON titles.pub_id = publishers.pub_id
      AND pub_name = 'Algodata Infosystems')

Here is the result set:

title
---------------------------------------------------------------
You Can Combat Computer Stress!
The Gourmet Microwave
But Is It User Friendly?
Secrets of Silicon Valley
Computer Phobic and Non-Phobic Individuals: Behavior Variations
Life Without Fear
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
Sushi, Anyone?

(8 row(s) affected)

For each title, the inner query finds a list of advance amounts paid by Algodata. The outer query looks at all values in the list and determines whether the title currently being considered has commanded an advance larger than any of those amounts. In other words, it finds titles with advances as large or larger than the lowest value paid by Algodata.

If the subquery does not return any values, the entire query fails to return any values.

The =ANY operator is equivalent to IN. For example, to find authors who live in the same city as a publisher, you can use either IN or =ANY.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city IN
   (SELECT city
   FROM publishers)

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
   (SELECT city
   FROM publishers)

Here is the result set for either query:

au_lname         au_fname
--------         ---------
Carson         Cheryl
Bennet         Abraham

(2 row(s) affected)

The < >ANY operator, however, differs from NOT IN: < >ANY means not = a, or not = b, or not = c. NOT IN means not = a, and not = b, and not = c. <>ALL means the same as NOT IN.

For example, this query finds the authors who live in a city in which no publisher is located.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city <> ANY
   (SELECT city
   FROM publishers)

Here is the result set:

au_lname                                 au_fname             
---------------------------------------- -------------------- 
White                                    Johnson              
Green                                    Marjorie             
Carson                                   Cheryl               
O'Leary                                  Michael              
Straight                                 Dean                 
Smith                                    Meander              
Bennet                                   Abraham              
Della Buena                              Ann                  
Gringlesby                               Burt                 
Locksley                                 Charlene             
Greene                                   Morningstar          
Blotchet-Halls                           Reginald             
Yokomoto                                 Akiko                
del Covello                              Innes                
DeFrance                                 Michel               
Stringer                                 Dirk                 
MacFeather                               Stearns              
Karsen                                   Livia                
Panteley                                 Sylvia               
Hunter                                   Sheryl               
McBadden                                 Heather              
Ringer                                   Anne                 
Ringer                                   Albert               

(23 row(s) affected)

The results include all 23 authors because every author lives in a city in which one or more of the publishers is not located. The inner query finds all the cities in which publishers are located, and then, for each city, the outer query finds the authors who don't live there.

However, when you use NOT IN in this query, the results include all the authors except Cheryl Carson and Abraham Bennet, who live in Berkeley, where Algodata Infosystems is located.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city NOT IN
   (SELECT city
   FROM publishers)

Here is the result set:

au_lname                                 au_fname             
---------------------------------------- -------------------- 
White                                    Johnson              
Green                                    Marjorie             
O'Leary                                  Michael              
Straight                                 Dean                 
Smith                                    Meander              
Della Buena                              Ann                  
Gringlesby                               Burt                 
Locksley                                 Charlene             
Greene                                   Morningstar          
Blotchet-Halls                           Reginald             
Yokomoto                                 Akiko                
del Covello                              Innes                
DeFrance                                 Michel               
Stringer                                 Dirk                 
MacFeather                               Stearns              
Karsen                                   Livia                
Panteley                                 Sylvia               
Hunter                                   Sheryl               
McBadden                                 Heather              
Ringer                                   Anne                 
Ringer                                   Albert               

(21 row(s) affected)

You can get the same results with the < >ALL operator, which is equivalent to NOT IN.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city <> ALL
   (SELECT city
   FROM publishers)

Subqueries with EXISTS

When a subquery is introduced with the keyword EXISTS, it functions as an existence test. The WHERE clause of the outer query tests for the existence of rows returned by the subquery. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.

A subquery introduced with EXISTS has the following syntax:

WHERE [NOT] EXISTS (subquery)

This query finds the names of all publishers who publish business books:

USE pubs
SELECT pub_name
FROM publishers
WHERE EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
      AND type = 'business')

Here is the result set:

pub_name
--------------------
New Moon Books
Algodata Infosystems

(2 row(s) affected)

To determine the results of this query, consider each publisher's name in turn. Does this value cause the subquery to return at least one row? In other words, does it cause the existence test to evaluate to TRUE?

In this case, the first publisher name is Algodata Infosystems, with identification number 1389. Are there any rows in the titles table in which pub_id is 1389 and type is business? If so, Algodata Infosystems should be one of the values selected. The same process is repeated for each of the other publisher names.

Notice that subqueries introduced with EXISTS are a bit different from other subqueries in these ways:

The EXISTS keyword is important because often there is no alternative, nonsubquery formulation. Although some queries formulated with EXISTS cannot be expressed any other way, all queries that use IN or a comparison operator modified by ANY or ALL can be expressed with EXISTS.

Examples of queries using EXISTS and equivalent alternatives follow.

Here are two ways to find authors who live in the same city as a publisher:

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city =ANY
   (SELECT city
   FROM publishers)
-- Or
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE exists
   (SELECT *
   FROM publishers
   WHERE authors.city = publishers.city)

Here is the result set for either query:

au_lname         au_fname
--------         --------
Carson         Cheryl
Bennet         Abraham

(2 row(s) affected)

These two queries find titles of books published by any publisher located in a city that begins with the letter B:

USE pubs
SELECT title
FROM titles
WHERE pub_id IN
   (SELECT pub_id
   FROM publishers
   WHERE city LIKE 'B%')
-- Or
USE pubs
SELECT title
FROM titles
WHERE EXISTS
   (SELECT *
   FROM publishers
   WHERE pub_id = titles.pub_id
      AND city LIKE 'B%')

Here is the result set for either query:

title
----------------------------------------------------
The Busy Executive's Database Guide
Cooking with Computers: Surreptitious Balance Sheets
You Can Combat Computer Stress!
Straight Talk About Computers
But Is It User Friendly?
Secrets of Silicon Valley
Net Etiquette
Is Anger the Enemy?
Life Without Fear
Prolonged Data Deprivation: Four Case Studies
Emotional Security: A New Algorithm

(11 row(s) affected)

Subqueries with NOT EXISTS

NOT EXISTS works like EXISTS, except the WHERE clause in which it is used is satisfied if no rows are returned by the subquery.

For example, to find the names of publishers who do not publish business books:

USE pubs
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
      AND type = 'business')

Here is the result set:

pub_name                                 
---------------------------------------- 
Binnet & Hardley                         
Five Lakes Publishing                    
Ramona Publishers                        
GGG&G                                    
Scootney Books                           
Lucerne Publishing                       

(6 row(s) affected)

This query finds the titles for which there have been no sales.

USE pubs
SELECT title
FROM titles
WHERE NOT EXISTS
   (SELECT title_id
   FROM sales
   WHERE title_id = titles.title_id)

Here is the result set:

title
----------------------------------
The Psychology of Computer Cooking
Net Etiquette

(2 row(s) affected)

Using EXISTS and NOT EXISTS to Find Intersection and Difference

Subqueries introduced with EXISTS and NOT EXISTS can be used for two set-theory operations: intersection and difference. The intersection of two sets contains all elements that belong to both of the original sets. The difference contains elements that belong only to the first of the two sets.

The intersection of authors and publishers over the city column is the set of cities in which both an author and a publisher are located.

USE pubs
SELECT DISTINCT city
FROM authors
WHERE EXISTS
   (SELECT *
   FROM publishers
   WHERE authors.city = publishers.city)

Here is the result set:

city
--------
Berkeley

(1 row(s) affected)

Of course, this query could be written as a simple join.

USE pubs
SELECT DISTINCT authors.city
FROM authors INNER JOIN publishers
ON authors.city = publishers.city

The difference between authors and publishers over the city column is the set of cities where an author lives but no publisher is located, that is, all the cities except Berkeley.

USE pubs
SELECT DISTINCT city
FROM authors
WHERE NOT EXISTS
   (SELECT *
   FROM publishers
   WHERE authors.city = publishers.city)

This query could also be written as:

USE pubs
SELECT DISTINCT city
FROM authors
WHERE city NOT IN
   (SELECT city 
   FROM publishers)

Subqueries Used in Place of an Expression

In Transact-SQL, a subquery can be substituted anywhere an expression can be used in SELECT, UPDATE, INSERT, and DELETE statements, except in an ORDER BY list.

The following example illustrates how you might use this enhancement. This query finds the price of a popular computer book, the average price of all books, and the difference between the price of the book and the average price of all books.

USE pubs
SELECT title, price,
(SELECT AVG(price) FROM titles) AS average,
price-(SELECT AVG(price) FROM titles) AS difference
FROM titles
WHERE type='popular_comp'

Here is the result set:

title                     price          average        difference       
------------------------  -------------- -------------- ---------------- 
But Is It User Friendly?  22.95          14.77          8.18             
Secrets of Silicon Valley 20.00          14.77          5.23             
Net Etiquette             (null)         14.77          (null)           

(3 row(s) affected)

Multiple Levels of Nesting

A subquery can itself include one or more subqueries. Any number of subqueries can be nested in a statement.

This query finds the names of authors who have participated in writing at least one popular computer book.

USE pubs
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'))

Here is the result set:

au_lname                                 au_fname             
---------------------------------------- -------------------- 
Carson                                   Cheryl               
Dull                                     Ann                  
Locksley                                 Charlene             
Hunter                                   Sheryl               

(4 row(s) affected)

The innermost query returns the title ID numbers PC1035, PC8888, and PC9999. The query at the next higher level is evaluated with these title IDs and returns the author ID numbers. Finally, the outer query uses the author IDs to find the names of the authors.

You can also express this query as a join:

USE pubs
SELECT au_lname, au_fname
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
   JOIN titles ON titleauthor.title_id = titles.title_id
WHERE type = 'popular_comp'

Correlated Subqueries

Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

This query finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE 100 IN
   (SELECT royaltyper
   FROM titleauthor
   WHERE titleauthor.au_ID = authors.au_id)

Here is the result set:

au_lname                                 au_fname             
---------------------------------------- -------------------- 
White                                    Johnson              
Green                                    Marjorie             
Carson                                   Cheryl               
Straight                                 Dean                 
Locksley                                 Charlene             
Blotchet-Halls                           Reginald             
del Castillo                             Innes                
Panteley                                 Sylvia               
Ringer                                   Albert               

(9 row(s) affected)

Unlike most of the subqueries shown earlier, the subquery in this statement cannot be resolved independently of the main query. It needs a value for authors.au_id, but this value is a variable. It changes as Microsoft® SQL Server™ examines different rows of the authors table.

That is exactly how this query is evaluated: SQL Server considers each row of the authors table for inclusion in the results by substituting the value in each row into the inner query. For example, if SQL Server first examines the row for Cheryl Carson, the variable authors.au_id takes the value 238-95-7766, which SQL Server substitutes into the inner query.

USE pubs
SELECT royaltyper
FROM titleauthor
WHERE au_id = '238-95-7766'

The result is 100, so the outer query evaluates to:

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE 100 IN (100)

Because this is true, the row for Cheryl Carson is included in the results. Go through the same procedure with the row for Abraham Bennet; you'll see that this row is not included in the results.

Correlated Subqueries with Aliases

Correlated subqueries can be used in operations such as selecting data from a table referenced in the outer query. In this case a table alias (also called a correlation name) must be used to specify unambiguously which table reference to use. For example, you can use a correlated subquery to find the types of books published by more than one publisher. Aliases are required to distinguish the two different roles in which the titles table appears.

USE pubs
SELECT DISTINCT t1.type
FROM titles t1
WHERE t1.type IN
   (SELECT t2.type
   FROM titles t2
   WHERE t1.pub_id <> t2.pub_id)

Here is the result set:

type
----------
business
psychology

(2 row(s) affected)

The preceding nested query is equivalent to this self-join:

USE pubs
SELECT DISTINCT t1.type
FROM titles t1 INNER JOIN titles t2 ON t1.type = t2.type 
   AND t1.pub_id <> t2.pub_id

Correlated Subqueries with Comparison Operators

Use a correlated subquery with a comparison operator to find sales where the quantity is less than the average quantity for sales of that title.

USE pubs
SELECT s1.ord_num, s1.title_id, s1.qty
FROM sales s1
WHERE s1.qty <
   (SELECT AVG(s2.qty)
   FROM sales s2
   WHERE s2.title_id = s1.title_id)

Here is the result set:

ord_num              title_id qty    
-------------------- -------- ------ 
6871                 BU1032   5      
722a                 PS2091   3      
D4482                PS2091   10     
N914008              PS2091   20     
423LL922             MC3021   15     

(5 row(s) affected)

The outer query selects the rows of sales (that is, of s1) one by one. The subquery calculates the average quantity for each sale being considered for selection in the outer query. For each possible value of s1, Microsoft® SQL Server™ evaluates the subquery and puts the record being considered in the results if the quantity is less than the calculated average.

Sometimes a correlated subquery mimics a GROUP BY clause. This example finds all titles that have a price greater than the average for books of its type.

USE pubs
SELECT t1.type, t1.title
FROM titles t1
WHERE t1.price >
   (SELECT AVG(t2.price)
   FROM titles t2
   WHERE t1.type = t2.type)

Here is the result set:

type         title                                                       
------------ ----------------------------------------------------------- 
business     The Busy Executive's Database Guide                         
business     Straight Talk About Computers                               
mod_cook     Silicon Valley Gastronomic Treats                           
popular_comp But Is It User Friendly?                                    
psychology   Computer Phobic AND Non-Phobic Individuals: Behavior 
            Variations                  
psychology   Prolonged Data Deprivation: Four Case Studies               
trad_cook    Onions, Leeks, and Garlic: Cooking Secrets of the 
            Mediterranean                  

(7 row(s) affected)

For each possible value of t1, SQL Server evaluates the subquery and includes the row in the results if the price value of that row is greater than the calculated average. It is not necessary to group by type explicitly, because the rows for which average price is calculated are restricted by the WHERE clause in the subquery.

Correlated Subqueries in a HAVING Clause

A correlated subquery can also be used in the HAVING clause of an outer query. This construction can be used to find the types of books for which the maximum advance is more than twice the average within a given group.

In this case, the subquery is evaluated once for each group defined in the outer query (once for each type of book).

USE pubs
SELECT t1.type
FROM titles t1
GROUP BY t1.type
HAVING MAX(t1.advance) >=ALL
   (SELECT 2 * AVG(t2.advance)
   FROM titles t2
   WHERE t1.type = t2.type)

Here is the result set:

type
--------
mod_cook

(1 row(s) affected)

Conditional Data Processing Using CASE

The CASE function is used to evaluate several conditions and return a single value for each condition. A common use of the CASE function is to replace codes or abbreviations with more readable values. The following query uses the CASE function to rename book categories so that they are more understandable.

USE pubs
SELECT
   CASE type
      WHEN 'popular_comp' THEN 'Popular Computing'
      WHEN 'mod_cook' THEN 'Modern Cooking'
      WHEN 'business' THEN 'Business'
      WHEN 'psychology' THEN 'Psychology'
      WHEN 'trad_cook' THEN 'Traditional Cooking'
      ELSE 'Not yet categorized'
   END AS Category, 
CONVERT(varchar(30), title) AS "Shortened Title", 
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY 1

Here is the result set:

category            shortened title                Price   
------------------- ------------------------------ ------- 
Business            Cooking with Computers: Surrep 11.95   
Business            Straight Talk About Computers  19.99   
Business            The Busy Executive's Database  19.99   
Business            You Can Combat Computer Stress 2.99    
Modern Cooking      Silicon Valley Gastronomic Tre 19.99   
Modern Cooking      The Gourmet Microwave          2.99    
Popular Computing   But Is It User Friendly?       22.95   
Popular Computing   Secrets of Silicon Valley      20.00   
Psychology          Computer Phobic AND Non-Phobic 21.59   
Psychology          Emotional Security: A New Algo 7.99    
Psychology          Is Anger the Enemy?            10.95   
Psychology          Life Without Fear              7.00    
Psychology          Prolonged Data Deprivation: Fo 19.99   
Traditional Cooking Fifty Years in Buckingham Pala 11.95   
Traditional Cooking Onions, Leeks, and Garlic: Coo 20.95   
Traditional Cooking Sushi, Anyone?                 14.99   

(16 row(s) affected)

Another use of CASE is to categorize data. The following query uses the CASE function to categorize prices.

SELECT
   CASE 
      WHEN price IS NULL THEN 'Not yet priced'
      WHEN price < 10 THEN 'Very Reasonable Title'
      WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
      ELSE 'Expensive book!'
   END AS "Price Category",
CONVERT(varchar(20), title) AS "Shortened Title"
FROM pubs.dbo.titles
ORDER BY price

Here is the result set:

Price Category        Shortened Title      
--------------------- -------------------- 
Not yet priced        The Psychology of Co 
Not yet priced        Net Etiquette        
Very Reasonable Title You Can Combat Compu 
Very Reasonable Title The Gourmet Microwav 
Very Reasonable Title Life Without Fear    
Very Reasonable Title Emotional Security:  
Coffee Table Title    Is Anger the Enemy?  
Coffee Table Title    Cooking with Compute 
Coffee Table Title    Fifty Years in Bucki 
Coffee Table Title    Sushi, Anyone?       
Coffee Table Title    The Busy Executive's 
Coffee Table Title    Straight Talk About  
Coffee Table Title    Silicon Valley Gastr 
Coffee Table Title    Prolonged Data Depri 
Expensive book!       Secrets of Silicon V 
Expensive book!       Onions, Leeks, and G 
Expensive book!       Computer Phobic AND  
Expensive book!       But Is It User Frien 

(18 row(s) affected)


Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21>  <22>  <23> 
Link to this page: //www.vb-net.com/sql/subquery.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>