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

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.

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

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.

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.DepartmentID) GO SELECT * FROM Department D OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) GO

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_id) AS [Database], [text] AS [Query]  FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) st 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 ')'.