Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 29 January 2015

CROSS APPLY WITH INNER JOIN AND OUTER JOIN and CROSS JOIN in QUERY

CROSS APPLY WITH INNER JOIN AND OUTER JOIN 

First query in Script #2 selects data from Department table and uses CROSS APPLY to evaluate the Employee table for each record of the Department table. Second query simply joins the Department table with the Employee table and all the matching records are produced.
Script #2 - CROSS APPLY and INNER JOIN
SELECT FROM Department D
CROSS APPLY
   (
   
SELECT FROM Employee E
   WHERE E.DepartmentID D.DepartmentID
   A
GO
SELECT FROM Department D
INNER JOIN Employee E ON D.DepartmentID 
E.DepartmentID
GO
cross apply and inner join result set
If you look at the results they produced, it is the exact same result-set; not only that even the execution plan for these queries are similar to each other and has equal query cost, as you can see in the image below. So what is the use of APPLY operator?  How does it differ from a JOIN and how does it help in writing more efficient queries. I will discuss this later, but first let me show you an example of OUTER APPLY also.
cross apply and inner join query plan
The first query in Script #3 selects data from Department table and uses OUTER APPLY to evaluate the Employee table for each record of the Department table.  For those rows for which there is not a match in Employee table, those rows contains NULL values as you can see in case of row 5 and 6. The second query simply uses a LEFT OUTER JOIN between the Department table and the Employee table.  As expected the query returns all rows from Department table; even for those rows for which there is no match in the Employee table.
Script #3 - OUTER APPLY and LEFT OUTER JOIN
SELECT FROM Department D
OUTER APPLY
   (
   
SELECT FROM Employee E
   WHERE E.DepartmentID D.DepartmentID
   A
GO
SELECT FROM Department D
LEFT OUTER JOIN Employee E ON D.DepartmentID 
E.DepartmentID
GO
outer apply and left outer join result set
Even though the above two queries return the same information, the execution plan is a bit different. Although cost wise there is not much difference, the query with the OUTER APPLY uses a Compute Scalar operator (which has an estimated operator cost of 0.0000103 or almost 0% of total query cost) before Nested Loops operator to evaluate and produce the columns of Employee table.
outer apply and left outer join query plan
Now comes the time to see where the APPLY operator is really required. In Script #4, I am creating a table-valued function which accepts DepartmentID as its parameter and returns all the employees who belong to this department. The next query selects data from Department table and uses CROSS APPLY to join with the function we created.  It passes the DepartmentID for each row from the outer table expression (in our case Department table) and evaluates the function for each row similar to a correlated subquery. The next query uses the OUTER APPLY in place of CROSS APPLY and hence unlike CROSS APPLY which returned only correlated data, the OUTER APPLY returns non-correlated data as well, placing NULLs into the missing columns.
Script #4 - APPLY with table-valued function
IF EXISTS (SELECT FROM sys.objects WHERE OBJECT_ID OBJECT_ID(N'[fn_GetAllEmployeeOfADepartment]') AND type IN (N'IF'))BEGIN
   DROP FUNCTION 
dbo.fn_GetAllEmployeeOfADepartment END GO
CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)  RETURNS TABLE
AS
RETURN
   
(
   
SELECT FROM Employee E
   WHERE E.DepartmentID @DeptID
   
GO
SELECT FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentIDGO
SELECT FROM Department D
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID
GO
apply with table valued function result set
So now if you are wondering, can we use a simple join in place of the above queries? Then the answer is NO, if you replace CROSS/OUTER APPLY in the above queries with INNER JOIN/LEFT OUTER JOIN, specify ON clause (something as 1=1) and run the query, you will get "The multi-part identifier "D.DepartmentID" could not be bound."  error. This is because with JOINs the execution context of outer query is different from the execution context of the function (or aderived table), and you can not bind a value/variable from the outer query to the function as a parameter.  Hence the APPLY operator is required for such queries.
So in summary the APPLY operator is required when you have to use table-valued function in the query, but it can also be used with an inline SELECT statements.
Now let me show you another query with a Dynamic Management Function (DMF). Script #5 returns all the currently executing user queries except for the queries being executed by the current session. As you can see the script below, the sys.dm_exec_requests dynamic management view is being CROSS APPLY'ed with the sys.dm_exec_sql_textdynamic management function which accepts a "plan handle" for the query and the same "plan handle" is being passed from the left/outer expression to the function to work and to return the data.
Script #5 - APPLY with Dynamic Management Function (DMF)
USE master
GO
SELECT DB_NAME(database_idAS [Database][text] AS [Query] 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handlest
WHERE session_Id 50           -- Consider spids for users only, no system spids. AND session_Id NOT IN (@@SPID)  
-- Don't include request from current spid.

Ref:
http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

CROSS JOIN 
SELECT * FROM Department_Det D 
cross join dbo.fn_GetAllEmployeeOfADepartment(3) 

O/P:
Get records

SELECT * FROM Department_Det D 
cross join dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) 
O/P:
Get  Error as 

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "D.DepartmentID" could not be bound.



SELECT * FROM Department_Det D 
cross join dbo.fn_GetAllEmployeeOfADepartment(SELECT DepartmentID FROM Department_Det)) 
O/P:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.