What use of Cross apply?

Shraddha Patel7y ago

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

0

Amit Kumar7y ago

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/

0

Shraddha Patel7y ago

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 )

0

Rajkiran Swain9y ago

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.

+1

Sundaram Subramanian9y ago

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

(