CROSS APPLY and OUTER APPLY Examples
Last blog introduced the APPLY operator and covered how it differs from regular JOINs. In today's follow-up, we'll compare the performance of APPLY to that of an INNER JOIN as well as learn how to use APPLY with table valued functions.
APPLY and INNER JOIN Comparison
Recall that, at the end of Part 1, we ran a query made up of two parts: the first query selected data from the Department table and used a CROSS APPLY to evaluate the Employee table for each record of the Department table; the second query joined the Department table with the Employee table to produce the same results:
In Navicat, we can click on the EXPLAIN button to obtain valuable information about the database execution plan. Here's what it reveals about the above queries:
Although the execution plans for both queries are similar and carry an equal cost, their execution plans do differ somewhat from each other:
- The APPLY query uses a Compute Scalar. It's an operator that is used to calculate a new value from the existing row value by performing a scalar computation operation that results a computed value. These Scalar computations includes conversion or concatenation of the scalar value. Note that the Compute Scalar operator is not an expensive operator, and adds very little cost to the overall weight of our query, causing a minimal overhead.
- The JOIN query contains an additional Clustered index scan. This occurs when SQL server reads through for the Row(s) from top to bottom in the clustered index, such as when searching for data in non key column. This is a slightly more costly operation than Compute Scalar.
Using the APPLY Operator To Join Table Valued Functions and Tables
A table-valued function is a user-defined function that returns data of a table type. The return type of a table-valued function is a table, therefore, you can use the table-valued function just like you would use a table. Joining table valued functions with other tables is what the APPLY operator was designed for.
Let's create a table-valued function that accepts a DepartmentID as its parameter and returns all the employees who belong to that department. In Navicat, we can create a function by clicking the big function button on the main toolbar and then clicking on New Function on the Function toolbar:
Here is the GetAllEmployeesForDepartment function, after clicking the Save button:
Watch what happens when we join our new function to each department using both CROSS OUTER and OUTER APPLY:
In each case, the query passes the DepartmentID for each row from the outer table expression and evaluates the function for each row, similar to a correlated subquery. Whereas the CROSS APPLY returned only correlated data, the OUTER APPLY returned non-correlated data as well, which resulted in NULLs for the missing columns.
We could not replace the CROSS/OUTER APPLY in the above queries with an INNER JOIN/LEFT OUTER JOIN. Doing so would produce the error "The multi-part identifier "D.DepartmentID" could not be bound.". This is because the execution context of the outer (JOINed) query differs from that of the function (or a derived table). Thus, you cannot bind a value/variable from the outer query to the function as a parameter. Hence the APPLY operator is required for such queries.
Conclusion
That concludes our look at the CROSS APPLY and OUTER APPLY statements. So, in summary, while the APPLY operator is required when you have to use a table-valued function in the query, it may be utilized with inline SELECT statements as well.
