Apply operator in Sql Server

Recently in a scenario, I used Cross Apply in a query and one of my colleagues asked me why and where to use Cross Apply and what is the difference between Cross Join and Cross Apply, so I thought of publishing an article related to same.

Cross Apply clause comes under Apply Operator which was introduced in SQL Server 2005.

The Apply operator acts like a Join without the ON clause. The Apply function in a query allows you 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

Apply operator can be used in two ways : Cross and Outer.

• 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.

First create split function before running the below snippet.

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"