SQL Server CROSS APPLY and OUTER APPLY

Creating some temporary objects

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 , 

) ON [PRIMARY] 

GO 

INSERT ##Employee ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 

VALUES (1, N'Sendhil', N'Kumar', 1 ) 

INSERT ##Employee ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 

VALUES (2, N'Arun', N'Kumar', 2 ) 

INSERT ##Employee ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 

VALUES (3, N'Vivek', N'Ram', 3 ) 

INSERT ##Employee ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 

VALUES (4, N'Bill', N'Gates', 3 )

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

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