244
Part II: Building Databases and Working with Data
WHERE 5 = SOME
(SELECT a
FROM
(VALUES
(2),
(3),
(5),
(7),
(9)
) AS MyTable(a)
);Result:SomeTest
--------------
TrueThe ANY and SOME conditions are similar to the IN condition. In fact = ANY and = SOME
are exactly like IN. ANY and SOME conditions have the extra functionality to testing for
other conditional tests such as <, <=, >, =>, and <>.Correlated Subqueries
Correlated subqueries sound impressive, but they actually aren’t. They are used in the same
ways that simple subqueries are used; the difference being that correlated subqueries refer-
ence columns in the outer query. They do this by referencing the name or alias of a table in
the outer query, to reference the outer query. This capability to limit the subquery by the
outer query makes these queries fl exible. Because correlated subqueries can reference the
outer query, they are especially useful for complex WHERE conditions.Correlating in the WHERE Clause
The capability to reference the outer query also means that correlated subqueries won’t run
by themselves because the reference to the outer query would cause the query to fail. The
logical execution order follows:- The outer query is executed once.
- The subquery is executed once for every row in the outer query, substituting the
values from the outer query into each execution of the subquery. - The subquery’s results are integrated into the result set.
If the outer query returns 100 rows, SQL Server executes the logical equivalent of 101
queries — one for the outer query and one subquery for every row returned by the outer
query. In practice, the SQL Server Query Optimizer tries to come up with a plan that is
more effi cient than this. To explore correlated subqueries, the next query, based on the
AdventureWorksLT2012 sample database, uses one to compare list prices to average
prices of items in a product category.c09.indd 244c09.indd 244 7/30/2012 4:25:11 PM7/30/2012 4:25:11 PM
http://www.it-ebooks.info