SQL Joins and Applies – Understanding APPLY
This is part of a four part post series on SQL Joins and Applies.
If you want to follow the examples given, you can get the T-SQL to create the tables, function and test data from my earlier post SQL Joins and Applies – Test Data.
The other posts in this series are:
SQL Joins and Applies – Understanding INNER JOIN
SQL Joins and Applies – Understanding CROSS JOIN
SQL Joins and Applies – Understanding OUTER JOIN
For some reason APPLY is not well known or understood. It is required when you have a Table-Valued Function (UDF, XML.nodes(), etc) and you need to process a table of values against the function and get a “Joined” result.
From Books Online (For an outer Join):
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
To simplify matters, let’s first look at timed when an APPLY is not required when using a Table-Valued Function in a query:
SELECT tsi.sysID [sysID_table], tudf.calculatedValue [result_function] FROM dbo.tblSourceIntegers tsi LEFT OUTER JOIN dbo.udfGet5Rows(2) tudf ON tsi.sysID = tudf.calculatedValue
In the above query, the function is called with a constant which means the function can be evaluated to a table BEFORE the join is performed. Any values which do not rely on the rest of the query should work in place of the value of 2 – You can even use a random integer here.
Here is one that uses a value from the table:
SELECT tsi.sysID [sysID_table] FROM dbo.tblSourceIntegers tsi WHERE 8 IN (SELECT calculatedValue FROM dbo.udfGet5Rows(tsi.sysID))
Although the function is using a value from the main query, this is in the WHERE which happens after the table is loaded into memory and as such.
As soon as the function is being executed for each value in the query as part of the query result, an Apply needs to be used.
There are two types of applies, CROSS APPLY and OUTER APPLY.
- CROSS APPLY will not “Join” if the Table-Valued Result is NULL
- OUTER APPLY will “Join” if the Table-Valued Result is NULL
Note: If the table-Valued function returns a column with NULL in it, CROSS APPLY will still perform the “join”. The only time CROSS APPLY will not is when the Table-Valued Result is actually a NULL table.
Here is a sample CROSS APPLY:
SELECT tsi.sysID [sysID_table], applied.calculatedValue FROM dbo.tblSourceIntegers tsi CROSS APPLY dbo.udfGet5Rows(tsi.sysID) applied
The result of the above Cross Apply is:
sysID_table calculatedValue ----------- --------------- 1 1 1 2 1 NULL 1 4 1 5 2 2 2 3 2 NULL 2 5 2 6 4 4 4 5 4 NULL 4 7 4 8 5 5 5 6 5 NULL 5 8 5 9 (20 row(s) affected)
Note that the NULLs on the Right hand column were one row out of 5 rows in the table returned from the Table-Valued Function.
Now let’s try the OUTER APPLY:
SELECT tsi.sysID [sysID_table], applied.calculatedValue FROM dbo.tblSourceIntegers tsi OUTER APPLY dbo.udfGet5Rows(tsi.sysID) applied
The result of the above Outer Apply is:
sysID_table calculatedValue ----------- --------------- 1 1 1 2 1 NULL 1 4 1 5 2 2 2 3 2 NULL 2 5 2 6 NULL NULL 4 4 4 5 4 NULL 4 7 4 8 5 5 5 6 5 NULL 5 8 5 9 (21 row(s) affected)
Note that the OUTER APPLY only returned one more row from when the function was called with a NULL value as the input parameter which the function returned a NULL result.
To review, an APPLY is useful for when you need to apply the values from a table to a Table-Valued function. APPLY is very resource intensive as it is recursive (The function will be run once for every matching value from the first table).
