Cross Apply vs Outer Apply
CREATE TABLE #EmployeeDetails (
EmpId int PRIMARY KEY,
EmpFirstName VARCHAR(50),
EmpLastName VARCHAR(50),
Department VARCHAR(50),
DepartID INT
)
CREATE TABLE #EmpSalary (
EmpID INT,
EmpFullName VARCHAR(80),
EmpSalary INT,
EmpWorkingYears INT,
DepartID INT
)
Insert into #EmployeeDetails values(1001,'Kate', 'Thomas', 'IT',2)
Insert into #EmployeeDetails values(1002,'John', 'Wills', 'IT',2)
Insert into #EmployeeDetails values(1003,'Branda', 'Pat', 'Accounts',3)
Insert into #EmployeeDetails values(1004,'Sofia', 'Kaul', 'HR',1)
Insert into #EmployeeDetails values(1005,'Tim', 'Stouts', 'IT',2)
Insert into #EmployeeDetails values(1006,'Mick', 'Presto', 'Accounts',3)
Insert into #EmployeeDetails values(1007,'Steave', 'Berg', 'Trainee',null)
Insert into #EmpSalary values(1001, 'Kate Thomas', 35000, 3, 2)
Insert into #EmpSalary values(1002,'John Wills', 25000, 2, 2)
Insert into #EmpSalary values(1003, 'Branda Pat', 20000, 2, 3)
Insert into #EmpSalary values(1004, 'Sofia Kaul', 18000, 1, 1)
Insert into #EmpSalary values(1005, 'Tim Stouts', 25000, 2, 2)
Insert into #EmpSalary values(1006,'Mick Presto', 28000,3, 3)
Insert into #EmpSalary values(1007,'Petro johnson', 8000,1, null)
Insert into #EmpSalary values(1008,'Christie Pic', 6000,1, null)
--drop table #EmployeeDetails; drop table #EmpSalary
1.CROSS APPLY
The Cross Apply returns rows form the outer table (table on the left of the Apply operator)
that produces matching values from the table-valued function (which is on the right side of the operator).
The Cross Apply is equivalent to Inner Join, but it works with a table-valued function.
SELECT
e.EmpId,
e.EmpFirstName,
e.EmpLastName,
f.Salaryinc
FROM
#EmployeeDetails AS e
cross apply
(SELECT
EmpID, EmpFullName,
EmpSalary AS Salaryinc
FROM
#Empsalary
WHERE
DepartID = e.DepartID) f
2.OUTER APPLY
--The Outer Apply returns all the rows from the outer table (table on the left of the Apply operator),
--and rows that do not matches the condition from the table-valued function (which is on the right side of the operator), NULL values are displayed.
SELECT
e.EmpId,
e.EmpFirstName,
e.EmpLastName,
f.Salaryinc
FROM
#EmployeeDetails AS e
outer apply
(SELECT
EmpID, EmpFullName,
EmpSalary AS Salaryinc
FROM
#Empsalary
WHERE
DepartID = e.DepartID) f
