Cross Apply and Outer Apply comes under Apply Operator which was introduced in SQL Server 2005.
Apply function allows to join a table to a table-valued function in such a way that function is invoked for each row returned from the table which you can't do with Join and is the main difference between Join and Apply:
- The Cross Apply only returns rows from the left side table if the table-valued-function returns rows.
- The Outer Apply clause returns all the rows of the left side table regardless of whether table-valued-function returns any row or not. If no row is returned by table-valued-function, the columns that the table-valued-function returns are null.
Let's take an example to understand in more detail.
In the following example we have used split function (Table Valued Function) in which we would pass comma separated string and it would return a table.
Firstly, understand and create a split function before running the following snippet using the link : split function in SQL Server
- declare @tab table(Category varchar(20), item varchar(max))
- insert into @tab
- select 'Vegetables', 'Carrot,Tomato' union all
- select 'Fruits', 'Apple,Banana,Grape' union all
- select 'Beverages', null
-
- select t.Category, s.val as item
- from @tab t
- cross apply dbo.split(item, ',') s
-
- select t.Category, s.val as item
- from @tab t
- outer apply dbo.split(item, ',') s
![output]()
Look at the output.
- First output with Cross Apply: No row with Category "Beverages" in the output.
- Second output with Outer apply: Getting a row with Category "Beverages" in the output.
Reason for difference
Function is returning null value for category "Beverages" because there is null value in item column for "Beverages".