SQL: The Magic of Apply
Author: jason
Date: 2014-01-23 09:33:38
Category: Musing

Using CROSS and OUTER APPLY helps me change a one to many problem into a one to one solution.

APPLY works a lot like JOIN. One of its advantages though is the ability to select a certain number of rows from the distant table, instead of all rows like with a JOIN.

In the below query we set up 2 tables. The 1st table has our main listing of fruit. The 2nd table has a date value and is associated with the 1st table by the ID field.

As we can see from the 1st JOIN/SELECT statement we are returning more than 1 row for Apple. In the 2nd APPLY/SELECT statement we are just getting the "newest" row from the 2nd table.





OUTER APPLY is like a LEFT JOIN: list all the rows from the 1st table even if they are not in the 2nd table.

CROSS APPLY is like an INNER JOIN: list only those rows from the 1st table where they are in the 2nd table.

Code

DECLARE @First AS TABLE ([id] BIGINT, [name] VARCHAR(10))
INSERT INTO @First SELECT 1, 'Apple'
INSERT INTO @First SELECT 2, 'Peach'
INSERT INTO @First SELECT 3, 'Pear'

DECLARE @Second AS TABLE ([id] BIGINT, [date] DATE)
INSERT INTO @Second SELECT 1, '1/20/2014'
INSERT INTO @Second SELECT 2, '1/21/2014'
INSERT INTO @Second SELECT 1, '1/23/2014'

SELECT f.*, s.*
FROM @First f
INNER JOIN @Second s ON s.id = f.id

SELECT f.*, s.*
FROM @First f
OUTER APPLY (SELECT TOP 1 s.id, s.date
FROM @Second s
WHERE s.id = f.id
ORDER BY s.date DESC) s





jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009