The APPLY clause in SQL Server 2005 can be used on a
table-valued function to be invoked for each row returned by the outer table
expression. Unlike SQL Server 2000, the order of the operation does matter
with the APPLY clause in SQL Server 2005.
The simplified syntax of APPLY clause (T-SQL) is as follows.
SELECT column1, column2…
FROM outer_table_expression ote
CROSS APPLY|OUTER APPLY table_valued_function (ote.join_column)
From the above syntax we can see there are two types of
APPLY clause available in SQL Server 2005: CROSS APPLY and OUTER APPLY.
CROSS APPLY is similar to INNER JOIN. It returns only rows
from the outer table that produce a result set from the table-valued function. The
table valued function in the CROSS APPLY clause also acts the correlated
sub-query where the sub-query is executed repeatedly for each row of the outer
query.
Listing 4
SELECT DeptName, EmpName, EmpSalary
FROM Departments d
CROSS APPLY fn_DepartmentEmployees (d.DeptId)
This query will list the employees' names and salaries in
all the departments that have one or more employees. If a department does not
have any employees allocated then that department will not appear in the result
set. To include such departments requires use of OUTER APPLY clause.
OUTER APPLY is similar to LEFT OUTER JOIN. It returns all
rows from the outer result set with NULL values in the columns of the
table-valued function that do not produce a result set for the outer row.
Listing 5
SELECT DeptName, EmpName, EmpSalary
FROM Departments d
OUTER APPLY fn_DepartmentEmployees (d.DeptId)
