Understanding APPLY Clause in SQL Server 2005
page 3 of 6
by Sachin Dedhia
Average Rating: 
Views (Total / Last 10 Days): 27576/ 74

The APPLY clause

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)

View Entire Article

User Comments

Title: The Apply Clause   
Name: Shailesh
Date: 2010-09-16 9:51:08 AM
The presentation is better as compared to the MSDN article.

Small and Sweet!

Title: APPLY clause   
Name: Vivek
Date: 2009-07-08 11:24:18 AM
Excellent Explanation..
Title: The APPLY clause   
Name: Sachin Dedhia
Date: 2009-05-26 4:14:26 PM
Still cannot see why is apply clause any better than join for SQL 2005
Title: user   
Name: user
Date: 2009-04-24 1:22:09 PM
Beautifully explained
Title: "Using APPLY" MSDN article   
Name: Michael Freidgeim
Date: 2009-03-02 4:19:54 PM
You should add reference to "Using APPLY" MSDN article http://msdn.microsoft.com/en-us/library/ms175156.aspx

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2020 ASPAlliance.com  |  Page Processed at 2020-07-02 4:42:43 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search