Using a Subquery in a T-SQL Statement

Sometimes the criteria for
determining which set of records will be affected by a SELECT, UPDATE, DELETE
and/or INSERT statement cannot be obtained by hard coding the selection
criteria. Occasionally there is a need to use the results of a SELECT
statement to help determine which records are returned or are affected by a
T-SQL statement. When a SELECT statement is used inside another statement, the
inside SELECT statement is known as a subquery. Subqueries can help to
dynamically control the records affected by an UPDATE, DELETE or INSERT
statement, as well as to help determine the records that will be returned by a
SELECT statement. This article will show different examples of how to use a
subquery to help clarify the records affected or selected by a T-SQL statement.

What is a Subquery?

A subquery is a SELECT statement
that is nested within another T-SQL statement. A subquery SELECT statement if
executed independently of the T-SQL statement, in which it is nested, will
return a result set. Meaning a subquery SELECT statement can standalone and is
not depended on the statement in which it is nested. A subquery SELECT
statement can return any number of values, and can be found in, the column list
of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL
statement. A Subquery can also be used as a parameter to a function call.
Basically a subquery can be used anywhere an expression can be used.

Use of a Subquery in the Column List of a SELECT Statement

Suppose you would like to
see the last OrderID and the OrderDate for the last order that was shipped to Paris.
Along with that information, say you would also like to see the OrderDate for
the last order shipped regardless of the ShipCity. In addition to this, you
would also like to calculate the difference in days between the two different OrderDates.
Here is my T-SQL SELECT statement to accomplish this:


select top 1 OrderId,convert(char(10),
OrderDate,121) Last_Paris_Order,
(select convert(char(10),max(OrderDate),121) from
Northwind.dbo.Orders) Last_OrderDate,
datediff(dd,OrderDate,
(select Max(OrderDate)from Northwind.dbo.Orders)) Day_Diff
from Northwind.dbo.Orders
where ShipCity = ‘Paris’
order by OrderDate desc

The above code contains two
subqueries. The first subquery gets the OrderDate for the last order shipped
regardless of ShipCity, and the second subquery calculates the number of days
between the two different OrderDates. Here I used the first subquery to return
a column value in the final result set. The second subquery was used as a
parameter in a function call. This subquery passed the “max(OrderDate)” date
to the DATEDIFF function.

Use of a Subquery in the WHERE clause

A subquery can be used to control
the records returned from a SELECT by controlling which records pass the
conditions of a WHERE clause. In this case the results of the subquery would be
used on one side of a WHERE clause condition. Here is an example:


select distinct country from Northwind.dbo.Customers
where country not in (select distinct country from Northwind.dbo.Suppliers)

Here I have returned a list
of countries where customers live, but there is no supplier located in that
country. I suppose if you where trying to provide better delivery time to
customers, then you might target these countries to look for additional
suppliers.

Suppose a company would like
to do some targeted marketing. This targeted marketing would contact customers
in the country with the fewest number of orders. It is hoped that this targeted
marketing will increase the overall sales in the targeted country. Here is an
example that uses a subquery to return the customer contact information for the
country with the fewest number of orders:


select Country,CompanyName, ContactName, ContactTitle, Phone
from Northwind.dbo.Customers
where country =
(select top 1 country
from Northwind.dbo.Customers C
join
Northwind.dbo.Orders O
on C.CustomerId = O.CustomerID
group by country
order by count(*))

Here I have written a
subquery that joins the Customer and Orders Tables to determine the total
number of orders for each country. The subquery uses the “TOP 1” clause to
return the country with the fewest number of orders. The country with the
fewest number of orders is then used in the WHERE clause to determine which
Customer Information will be displayed.

Use of a Subquery in the FROM clause

The FROM clause normally
identifies the tables used in the T-SQL statement. You can think of each of
the tables identified in the FROM clause as a set of records. Well, a subquery
is just a set of records, and therefore can be used in the FROM clause just
like a table. Here is an example where a subquery is used in the FROM clause
of a SELECT statement:


select au_lname, au_fname, title from
(select au_lname, au_fname, au_id from pubs.dbo.authors
where state = ‘CA’) as a
join
pubs.dbo.titleauthor ta on a.au_id=ta.au_id
join
pubs.dbo.titles t on ta.title_id = t.title_id

Here I have used a subquery
to select only the author record information, if the author’s record has a
state column equal to “CA.” I have named the set returned from this subquery
with a table alias of “a.” I can then use this alias elsewhere in the T-SQL statement
to refer to the columns from the subquery by prefixing them with an “a”, as I
did in the “ON” clause of the “JOIN” criteria. Sometimes using a subquery in
the FROM clause reduces the size of the set that needs to be joined. Reducing
the number of records that have to be joined enhances the performance of
joining rows, and therefore speeds up the overall execution of a query.

Here is an example where I
used a subquery in the FROM clause of an UPDATE statement:


set nocount on
create table x(i int identity,
a char(1))
insert into x values (‘a’)
insert into x values (‘b’)
insert into x values (‘c’)
insert into x values (‘d’)
select * from x

update x
set a = b.a
from (select max(a) as a from x) b
where i > 2

select * from x
drop table x

Here I created a table named
“x,” that has four rows. Then I proceeded to update the rows where “i” was
greater than 2 with the max value in column “a”. I used a subquery in the FROM
clause of the UPDATE statement to identity the max value of column “a.”

Use of a Subquery in the HAVING clause

In the following example, I
used a subquery to find the number of books a publisher has published where the
publisher is not located in the state of California. To accomplish this I used a subquery in a HAVING
clause. Here is my code:


select pub_name, count(*) bookcnt
from pubs.dbo.titles t
join
pubs.dbo.publishers p
on t.pub_id = p.pub_id
group by pub_name
having p.pub_name in
(select pub_name from pubs.dbo.publishers where state <> ‘CA’)

Here my subquery returns the
pub_name values for all publishers that have a state value not equal to “CA.”
The HAVING condition then checks to see if the pub_name is in the set returned
by my subquery.

Conclusion

The subquery is a valuable
concept to understand. Creative use of a subquery allows the desired results
to be returned in a single T-SQL statement, and helps avoid the use of
temporary tables and cursors to solve complicated selection criteria. In
addition, depending on the query, a subquery might improve performance by
reducing the number of records that SQL Server needs to process. In my next article,
I will expand on the idea of subqueries by looking at correlated subqueries.

»


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles