What use of Cross apply?

Question

.

Rajkiran Swain · Answer

See the article in my blog for detailed performance comparison: INNER JOIN vs. CROSS APPLY CROSS APPLY works better on things that have no simple JOIN condition. This one selects 3 last records from t2 for each record from t1 : SELECT t1 .*, t2o .* FROM t1 CROSS APPLY ( SELECT TOP 3 * FROM t2 WHERE t2 . t1_id = t1 . id ORDER BY t2 . rank DESC ) t2o It cannot be easily formulated with an INNER JOIN condition. You could probably do something like that using CTE 's and window function: WITH t2o AS ( SELECT t2 .*, ROW_NUMBER () OVER ( PARTITION BY t1_id ORDER BY rank ) AS rn FROM t2 ) SELECT t1 .*, t2o .* FROM t1 INNER JOIN t2o ON t2o . t1_id = t1 . id AND t2o . rn 3 , but this is less readable and probably less efficient. Update: Just checked. master is a table of about 20,000,000 records with a PRIMARY KEY on id . This query: WITH q AS ( SELECT *, ROW_NUMBER () OVER ( ORDER BY id ) AS rn FROM master ), t AS ( SELECT 1 AS id UNION ALL SELECT 2 ) SELECT * FROM t JOIN q ON q . rn t . id runs for almost 30 seconds, while this one: WITH t AS ( SELECT 1 AS id UNION ALL SELECT 2 ) SELECT * FROM t CROSS APPLY ( SELECT TOP ( t . id ) m .* FROM master m ORDER BY id ) q is instant.

Sundaram Subramanian · Answer

CROSS APPLY works better on things that have no simple JOIN condition. This one selects 3 last records from t2 for each record from t1: SELECT t1.*, t2o.* FROM t1 CROSS APPLY ( SELECT TOP 3 * FROM t2 WHERE t2.t1_id = t1.id ORDER BY t2.rank DESC ) t2o It cannot be easily formulated with an INNER JOIN condition. You could probably do something like that using CTE's and window function: WITH t2o AS ( SELECT t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn FROM t2 ) SELECT t1.*, t2o.* FROM t1 INNER JOIN t2o ON t2o.t1_id = t1.id AND t2o.rn , but this is less readable and probably less efficient. Update: Just checked. master is a table of about 20,000,000 records with a PRIMARY KEY on id. This query: WITH q AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM master ), t AS ( SELECT 1 AS id UNION ALL SELECT 2 ) SELECT * FROM t JOIN q ON q.rn runs for almost 30 seconds, while this one: WITH t AS ( SELECT 1 AS id UNION ALL SELECT 2 ) SELECT * FROM t CROSS APPLY ( SELECT TOP (t.id) m.* FROM master m ORDER BY id ) q is instant.

Shraddha Patel · Answer

CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. It other words, result of CROSS APPLY doesn’t contain any row of left side table expression for which no result is obtained from right side table expression. CROSS APPLY work as a row by row INNER JOIN. CROSS APPLY Query: SELECT * FROM ProjectAS PRO CROSSAPPLY ( SELECT * FROM Employee AS EMP WHERE PRO.Project_Id=EMP.Project_Id)Tab

Amit Kumar · Answer

Hi, Use of cross apply : The CROSS APPLY operator returns only those rows from the left table expression (in its final output) if it matches with the right table expression. In other words, the right table expression returns rows for the left table expression match only. So you might conclude, the CROSS APPLY is equivalent to an INNER JOIN (or to be more precise its like a CROSS JOIN with a correlated sub-query) with an implicit join condition of 1=1 whereas the OUTER APPLY is equivalent to a LEFT OUTER JOIN. For more detail https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

Shraddha Patel · Answer

CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. It other words, result of CROSS APPLY doesn't contain any row of left sidetable expression for which no result is obtained from right side table expression. CROSS APPLY work as a row by row INNER JOIN. Example is:- USE [tempdb] GO IF EXISTS ( SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N '[Employee]' ) AND type IN (N 'U' )) BEGIN DROP TABLE [Employee] END GO IF EXISTS ( SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N '[Department]' ) AND type IN (N 'U' )) BEGIN DROP TABLE [Department] END CREATE TABLE [Department]( [DepartmentID] [ int ] NOT NULL PRIMARY KEY , [ Name ] VARCHAR (250) NOT NULL , ) ON [ PRIMARY ] INSERT [Department] ([DepartmentID], [ Name ]) VALUES (1, N 'Engineering' ) INSERT [Department] ([DepartmentID], [ Name ]) VALUES (2, N 'Administration' ) INSERT [Department] ([DepartmentID], [ Name ]) VALUES (3, N 'Sales' ) INSERT [Department] ([DepartmentID], [ Name ]) VALUES (4, N 'Marketing' ) INSERT [Department] ([DepartmentID], [ Name ]) VALUES (5, N 'Finance' ) GO CREATE TABLE [Employee]( [EmployeeID] [ int ] NOT NULL PRIMARY KEY , [FirstName] VARCHAR (250) NOT NULL , [LastName] VARCHAR (250) NOT NULL , [DepartmentID] [ int ] NOT NULL REFERENCES [Department](DepartmentID), ) ON [ PRIMARY ] GO INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (1, N 'Orlando' , N 'Gee' , 1 ) INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (2, N 'Keith' , N 'Harris' , 2 ) INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (3, N 'Donna' , N 'Carreras' , 3 ) INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (4, N 'Janet' , N 'Gates' , 3 )