Click here to Skip to main content
Click here to Skip to main content
Go to top

MS SQL SERVER APPLY AND JOIN Keyword

, 16 Mar 2014
Rate this:
Please Sign up or sign in to vote.
Tip on CROSS APPLY and OUTER APPLY

Introduction

This tip just provides a difference between apply keyword that was introduced by MSSQL server 2005 and join keyword. The developer should clearly know the difference between apply and join to tune SQL query.

Using the Code

First, create two tables as mentioned below:

And insert few records in that table. For example, you can see that I inserted few records in both tables.

SELECT * FROM EMPLOYEE
SELECT * FROM DEPARTMENT  

I am going to demonstrate cross apply behaves the same like inner join. You can find the same result from both queries that use inner join and cross join. For example, you see from below:

SELECT E.EMPID,E.NAME,E.DEPTID,D.NAME FROM EMPLOYEE E 
INNER JOIN  DEPARTMENT D
ON E.DEPTID=D.DEPTID

SELECT E.EMPID,E.NAME,E.DEPTID,CA.NAME FROM EMPLOYEE E
CROSS APPLY(
SELECT * FROM DEPARTMENT D WHERE D.DEPTID=E.DEPTID
) CA  

From the above result, it is clear that both produce the same result. Now, take a look at both query execution plans.

From the above execution plan, it is clear that both queries have the same execution plan.

Now I am going to demonstrate outer apply with left join. outer apply is the same as left join. You can see from the below mentioned query and both results. For example:

SELECT E.EMPID,E.NAME,E.DEPTID,D.NAME FROM EMPLOYEE E 
LEFT JOIN  DEPARTMENT D
ON E.DEPTID=D.DEPTID
SELECT E.EMPID,E.NAME,E.DEPTID,OA.NAME FROM EMPLOYEE E
OUTER APPLY(
SELECT * FROM DEPARTMENT D WHERE D.DEPTID=E.DEPTID
) OA

From the above result, you can see both the queries produce the same result. Now, take a look at the execution plan.

From the execution plan, you can see both queries have the same execution plan.

I hope this tip will help you. If you have any suggestions, then you are welcome.

License

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

Share

About the Author

Dinesh K.S. Kushwaha
Team Leader Kochar Infotech Pvt. Ltd.
India India
No Biography provided
Follow on   Google+   LinkedIn

Comments and Discussions

 
AnswerJOINS vs APPLY PinmemberMember 1001771910-Apr-14 22:06 
QuestionA few comments / suggestions PinmemberGary Henning19-Mar-14 6:52 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 16 Mar 2014
Article Copyright 2014 by Dinesh K.S. Kushwaha
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid