Click here to Skip to main content
15,124,646 members
Articles / Programming Languages / T-SQL
Tip/Trick
Posted 15 Nov 2015

Tagged as

Stats

13.5K views
7 bookmarked

Apply Operator in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.92/5 (12 votes)
16 Nov 2015CPOL1 min read
Apply Operator in SQL Server

Introduction

In this tip, we will see what is Apply operator in SQL Server and how it works.

Using the Code

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

split function before running the below snippet.

SQL
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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun17-Nov-15 1:14
MemberHumayun Kabir Mamun17-Nov-15 1:14 
GeneralRe: My vote of 5 Pin
sandeepmittal1117-Nov-15 17:32
Membersandeepmittal1117-Nov-15 17:32 
QuestionWhat is Split() ? Pin
Jacob Milter16-Nov-15 9:04
MemberJacob Milter16-Nov-15 9:04 
AnswerRe: What is Split() ? Pin
sandeepmittal1116-Nov-15 19:56
Membersandeepmittal1116-Nov-15 19:56 
SuggestionMy vote 5 + JSON suggestion Pin
Jovan Popovic(MSFT)15-Nov-15 11:16
MemberJovan Popovic(MSFT)15-Nov-15 11:16 
Well explained.
Maybe you can add some example with built-in TVF. In SQL 2016 will come OPENJSON that can be used with OUTER/CROSS APPLY, see Friday the 13th - JSON is coming to SQL Server[^]

Jovan

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.