Microsoft® SQL Server® 2012 Bible

243

Chapter 9: Merging Data with Joins, Subqueries, and CTEs

9

(5),

(7),

(9)

) AS ValuesTable(a)

);

Result:

AllTest

--------------

True

Row-Value Constructors

The previous sample uses a feature introduced in SQL Server 2008, row-value constructors. This struc-

ture enables you to hard-code values as a result set. ValuesTable is a derived table, but there is no

SELECT. Instead, the VALUES clause was used to specify the contents of the table.

Be careful with the ALL condition if the subquery might return a null. A null value in

the subquery results forces the ALL to return a false because it’s impossible to prove that

the test is true for every value in the subquery if one of those values is unknown.

In this query, the last value is changed from a 9 to null, and the query no longer returns

true:

SELECT 'True' AS 'AllTest'

WHERE 1 < ALL

(SELECT a

FROM

(VALUES

(2),

(3),

(5),

(7),

(null)

) AS ValuesTable(a)

);

Result (empty result set):

AllTest

--------------

The SOME and ANY conditional tests return true if the condition is met for any values in

the subquery result set. For example:

SELECT 'True' as 'SomeTest'

c09.indd 243c09.indd 243 7/30/2012 4:25:11 PM7/30/2012 4:25:11 PM

http://www.it-ebooks.info