C H A P T E R 4
Subqueries
SQL supports writing queries within queries, or whose result set is returned to the caller and is known as the nesting queries. The outermost query is a query outer query. The inner query is a
query whose result is used by the outer query and is known as a subquery. The inner query acts in
place of an expression that is based on constants or variables and is evaluated at run time. Unlike the
results of expressions that use constants, the result of a subquery can change, because of changes in
the queried tables. When you use subqueries, you avoid the need for separate steps in your solutions
that store intermediate query results in variables.
A subquery can be either self-contained or correlated. A self-contained subquery has no dependency on the outer query that it belongs to, whereas a correlated subquery does. A subquery can
be single-valued, multivalued, or table-valued. That is, a subquery can return a single value (a scalar
value), multiple values, or a whole table result.
This chapter focuses on subqueries that return a single value (scalar subqueries) and subqueries
that return multiple values (multivalued subqueries). I’ll cover subqueries that return whole tables
(table subqueries) later in the book in Chapter 5, “Table Expressions.”
Both self-contained and correlated subqueries can return a scalar or multiple values. I’ll first
describe self-contained subqueries and demonstrate both scalar and multivalued examples, and explicitly identify those as scalar or multivalued subqueries. Then I’ll describe correlated subqueries, but
I won’t explicitly identify them as scalar or multivalued, assuming that you will already understand the
difference.
Again, exercises at the end of the chapter can help you practice what you’ve learned.
Self-Contained Subqueries
Every subquery has an outer query that it belongs to. Self-contained subqueries are subqueries that
are independent of the outer query that they belong to. Self-contained subqueries are very convenient to debug, because you can always highlight the subquery code, run it, and ensure that it does
what it’s supposed to do. Logically, it’s as if the subquery code is evaluated only once before the outer
query is evaluated, and then the outer query uses the result of the subquery. The following sections
take a look at some concrete examples of self-contained subqueries.
www.it-ebooks.info
130 Microsoft SQL Server 2012 T-SQL Fundamentals
Self-Contained Scalar Subquery examples
A scalar subquery is a subquery that returns a single value—regardless of whether it is self-contained.
Such a subquery can appear anywhere in the outer query where a single-valued expression can appear (such as WHERE or SELECT).
For example, suppose that you need to query the Orders table in the TSQL2012 database and
return information about the order that has the maximum order ID in the table. You could accomplish
the task by using a variable. The code could retrieve the maximum order ID from the Orders table
and store the result in a variable. Then the code could query the Orders table and filter the order
where the order ID is equal to the value stored in the variable. The following code demonstrates this
technique.
USE TSQL2012;
DECLARE @maxid AS INT = (SELECT MAX(orderid)
FROM Sales.Orders);
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderid = @maxid;
This query returns the following output.
orderid orderdate empid custid
------------ --------------------------- ------------ -----------
11077 2008-05-06 00:00:00.000 1 65
You can substitute the technique that uses a variable with an embedded subquery. You achieve
this by substituting the reference to the variable with a scalar self-contained subquery that returns the
maximum order ID. This way, your solution has a single query instead of the two-step process.
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderid = (SELECT MAX(O.orderid)
FROM Sales.Orders AS O);
For a scalar subquery to be valid, it must return no more than one value. If a scalar subquery can
return more than one value, it might fail at run time. The following query happens to run without
failure.
SELECT orderid
FROM Sales.Orders
WHERE empid =
(SELECT E.empid
FROM HR.Employees AS E
WHERE E.lastname LIKE N'B%');
The purpose of this query is to return the order IDs of orders placed by any employee whose last
name starts with the letter B. The subquery returns employee IDs of all employees whose last names
start with the letter B, and the outer query returns order IDs of orders where the employee ID is
equal to the result of the subquery. Because an equality operator expects single-valued expressions
www.it-ebooks.info
CHAPTER 4 Subqueries 131
from both sides, the subquery is considered scalar. Because the subquery can potentially return
more than one value, the choices of using an equality operator and a scalar subquery here are
wrong. If the subquery returns more than one value, the query fails.
This query happens to run without failure because currently the Employees table contains only one
employee whose last name starts with B (Sven Buck with employee ID 5). This query returns the following output, shown here in abbreviated form.
orderid
-----------
10248
10254
10269
10297
10320
...
10874
10899
10922
10954
11043
(42 row(s) affected)
Of course, if the subquery returns more than one value, the query fails. For example, try running
the query with employees whose last names start with D.
SELECT orderid
FROM Sales.Orders
WHERE empid =
(SELECT E.empid
FROM HR.Employees AS E
WHERE E.lastname LIKE N'D%');
Apparently, two employees have a last name starting with D (Sara Davis and Zoya Dolgopyatova).
Therefore, the query fails at run time with the following error.
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <,
<= , >, >= or when the subquery is used as an expression.
If a scalar subquery returns no value, it returns a NULL. Recall that a comparison with a NULL
yields UNKNOWN and that query filters do not return a row for which the filter expression evaluates
to UNKNOWN. For example, the Employees table currently has no employees whose last names start
with A; therefore, the following query returns an empty set.
SELECT orderid
FROM Sales.Orders
WHERE empid =
(SELECT E.empid
FROM HR.Employees AS E
WHERE E.lastname LIKE N'A%');
www.it-ebooks.info
132 Microsoft SQL Server 2012 T-SQL Fundamentals
Self-Contained Multivalued Subquery examples
A multivalued subquery is a subquery that returns multiple values as a single column, regardless of
whether the subquery is self-contained. Some predicates, such as the IN predicate, operate on a multivalued subquery.
note There are other predicates that operate on a multivalued subquery; those are SOME,
ANY, and ALL. They are very rarely used and therefore not covered in this book.
The form of the IN predicate is:
IN ()
The predicate evaluates to TRUE if scalar_expression is equal to any of the values returned by the
subquery. Recall the last request discussed in the previous section—returning order IDs of orders that
were handled by employees with a last name starting with a certain letter. Because more than one
employee can have a last name starting with the same letter, this request should be handled with the
IN predicate and a multivalued subquery, and not with an equality operator and a scalar subquery.
For example, the following query returns order IDs of orders placed by employees with a last name
starting with D.
SELECT orderid
FROM Sales.Orders
WHERE empid IN
(SELECT E.empid
FROM HR.Employees AS E
WHERE E.lastname LIKE N'D%');
Because it uses the IN predicate, this query is valid with any number of values returned—none,
one, or more. This query returns the following output, shown here in abbreviated form.
orderid
-----------
10258
10270
10275
10285
10292
...
10978
11016
11017
11022
11058
(166 row(s) affected)
www.it-ebooks.info
CHAPTER 4 Subqueries 133
You might wonder why you wouldn’t implement this task by using a join instead of subqueries,
like this.
SELECT O.orderid
FROM HR.Employees AS E
JOIN Sales.Orders AS O
ON E.empid = O.empid
WHERE E.lastname LIKE N'D%';
Similarly, you are likely to stumble into many other querying problems that you can solve with
either subqueries or joins. In my experience, there’s no reliable rule of thumb that says that a subquery is better than a join. In some cases, the database engine interprets both types of queries the
same way. Sometimes joins perform better than subqueries, and sometimes the opposite is true. My
approach is to first write the solution query for the specified task in an intuitive form, and if performance is not satisfactory, one of my tuning approaches is to try query revisions. Such query revisions
might include using joins instead of subqueries or using subqueries instead of joins.
As another example of using multivalued subqueries, suppose that you need to write a query that
returns orders placed by customers from the United States. You can write a query against the Orders
table that returns orders where the customer ID is in the set of customer IDs of customers from the
United States. You can implement the last part in a self-contained, multivalued subquery. Here’s the
complete solution query.
SELECT custid, orderid, orderdate, empid
FROM Sales.Orders
WHERE custid IN
(SELECT C.custid
FROM Sales.Customers AS C
WHERE C.country = N'USA');
This query returns the following output, shown here in abbreviated form.
custid orderid orderdate empid
----------- ----------- --------------------------- -----------
65 10262 2006-07-22 00:00:00.000 8
89 10269 2006-07-31 00:00:00.000 5
75 10271 2006-08-01 00:00:00.000 6
65 10272 2006-08-02 00:00:00.000 6
65 10294 2006-08-30 00:00:00.000 4
...
32 11040 2008-04-22 00:00:00.000 4
32 11061 2008-04-30 00:00:00.000 4
71 11064 2008-05-01 00:00:00.000 1
89 11066 2008-05-01 00:00:00.000 7
65 11077 2008-05-06 00:00:00.000 1
(122 row(s) affected)