Microsoft® SQL Server® 2012 Bible

246

Part II: Building Databases and Working with Data

First, set up some sample data:

USE tempdb;

CREATE TABLE TableA (ID INT);

INSERT INTO TableA(ID)

VALUES (1),(2);

CREATE TABLE TableB (ID INT);

INSERT INTO TableB(ID)

VALUES (1),(3);

The following query uses a CROSS APPLY to pass every row from the outer query to the

derived table subquery. The subquery then fi lters its rows to those that match IDs. The

CROSS APPLY returns every row from the outer query that had a match in the subquery.

Functionally, it’s the equivalent to an inner join between TableA and TableB:

SELECT B.ID AS Bid, A.ID AS Aid

FROM TableB AS B

CROSS APPLY

(Select ID from TableA

where TableA.ID = B.ID) AS A;

Result:

Bid Aid

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

1 1

The next query uses the same derived table subquery but changes to an OUTER APPLY to

include all rows from the outer query. This query is the same as a left outer join between

TableA and TableB:

SELECT B.ID AS Bid, A.ID AS Aid

FROM TableB AS B

OUTER APPLY

(Select ID from TableA

where TableA.ID = B.ID) AS A;

Result:

ID ID

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

1 1

3 NULL

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

http://www.it-ebooks.info