MS SQL SERVER APPLY AND JOIN Keyword

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

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.