Cross Apply in SQL Server

APPLY OPERATORS:-

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The APPLY operator allows you to join two table expressions the right table expression is processed every time for each row from the left table expression. The final result-set contains all the selected columns from the left table expression followed by all the columns of right table expression.

The APPLY operator comes in two variants : CROSS APPLY and OUTER APPLY.

Cross Apply :-

The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression. In other words, the right table expression returns rows for left table expression match only.  For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression.  CROSS APPLY is semantically equivalent to INNER JOIN (or to be more precise its like a CROSS JOIN with a correlated sub-query) with a implicit join condition of 1=1.

Cross Apply Vs Inner Join :-

Script #1 creates a Department table to hold information about departments. Then it creates an Employee table which hold information about the employees. Please note, each employee belongs to a department, hence the Employee table has referential integrity with the Department table.

Script #1 - Creating some temporary objects to work on...

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 )

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