Microsoft® SQL Server® 2012 Bible

238

Part II: Building Databases and Working with Data

The subquery (SELECT 3) returns a single value of 3 , which is passed to the outer SELECT

statement. The outer SELECT statement is then executed as if it were the following:

SELECT 3 AS SubqueryValue;

Of course, a subquery with only hard-coded values is of little use. Generally, a simple sub-

query fetches data from a table, for example:

USE AdventureWorksLT2012;

SELECT pd.Name as ProductName

FROM SalesLT.Product pd

WHERE ProductCategoryID

 IN (Select ProductCategoryID

FROM SalesLT.ProductCategory

Where Name = 'Cranksets');

To execute this query, SQL Server fi rst evaluates the subquery and returns a value to the

outer query:

SELECT ProductCategoryID

FROM SalesLT.ProductCategory

WHERE Name = 'Cranksets';

Result:

ProductCategoryID

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

12

The outer query then executes as if it were the following:

SELECT Name as ProductName

FROM SalesLT.Product

WHERE ProductCategoryID

 = 12

Result:

ProductName

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

LL Crankset

ML Crankset

HL Crankset

Best Practice

Use a join to pull data from two data sources that can be fi ltered or manipulated as a whole after the

join. If the data must be manipulated prior to the join, try a derived table subquery.

c09.indd 238c09.indd 238 7/30/2012 4:25:10 PM7/30/2012 4:25:10 PM

http://www.it-ebooks.info