Definition:
The APPLY operator is used to apply a table-valued function to each row of a table expression. It allows us to join a table expression with a table-valued function in a more flexible and efficient way than traditional joins.
The APPLY operator comes in two variants: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only those rows from the table expression that match the output of the table-valued function, while OUTER APPLY returns all rows from the table expression, even if there is no match in the table-valued function.
Cross Apply:
The CROSS APPLY operator is used to apply a table-valued function to each row of a table expression and return only those rows that match the output of the function. The result set of a CROSS APPLY operation includes all the columns of the table expression and the columns returned by the table-valued function.
Syntax:
SELECT <Select_List>
FROM <Table_Expression>
CROSS APPLY <Table-Valued_Function>
(Here, <Select_List> specifies the columns to be included in the output, <Table_Expression> specifies the table or view on which the join is performed, and <Table-Valued_Function> specifies the function to be applied.)
Tables used in below examples:
——-create table department
CREATE TABLE [Department](
[DepartmentID] [int] NOT NULL PRIMARY KEY,
[Name] VARCHAR(250) NOT NULL,
) ON [PRIMARY]
—–Insert values in department
INSERT INTO [Department] (
[DepartmentID],
[Name]
)
VALUES (1, N’Engineering’),
(2, N’Administration’),
(3, N’Sales’),
(4, N’Marketing’),
(5, N’Finance’);
——created table employee
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]
—–insert values in employees
INSERT INTO [Employee] (
[EmployeeID],
[FirstName],
[LastName],
[DepartmentID]
)
VALUES
(1, N’Orlando’, N’Gee’, 1 ),
(2, N’Keith’, N’Harris’, 2 ),
(3, N’Donna’, N’Carreras’, 3 ),
(4, N’Janet’, N’Gates’, 3 );
Examples:
SELECT *
FROM dbo.Department D
CROSS APPLY (
SELECT * FROM dbo.Employee E
WHERE E.DepartmentID = D.DepartmentID
) A
(the CROSS APPLY operation has returned only those rows that match the output of the subquery)
Outer Apply:
OUTER APPLY is another type of APPLY function in SQL, which returns all the rows from the left table (similar to LEFT JOIN), even if there are no matching rows in the right table. The syntax for OUTER APPLY is similar to CROSS APPLY, but with the keyword “OUTER” before “APPLY”.
Syntax:
SELECT <Select_List>
FROM <Table_Expression>
OUTER APPLY <Table-Valued_Function>
(Here, <Select_List> specifies the columns to be included in the output, <Table_Expression> specifies the table or view on which the join is performed, and <Table-Valued_Function> specifies the function to be applied.)
Example:
SELECT *
FROM dbo.Department D
OUTER APPLY (
SELECT *
FROM dbo.Employee E
WHERE E.DepartmentID = D.DepartmentID
) A
Result:
Example3: How to use apply with table valued function
- First we created a function
CREATE or alter FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM dbo.Employee E
WHERE E.DepartmentID = @DeptID
)
GO
- Then we use this function in apply query
SELECT *
FROM dbo.Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO
Result:
SELECT * FROM dbo.Department D
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
Go
Result:
