APPLY IN SQL

USE demostart
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'IT')
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'Peter', N'Anderson', 1 )
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (2, N'David', N'Ronado', 2 )
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (3, N'O', N'Larry', 3 )
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (4, N'Scott', N'Merlis', 3 )

---------------------------------------------------------------------------------
--Working with Cross Apply
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



-----------------------------------------------------------------------------------
--Working with Outer Apply

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

----------------------------------------------------------------------------------

--creating a table-valued function which accepts DepartmentID as its parameter and returns all the employees who belong to this department.
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