Cross Apply And Outer Apply With Examples

CROSS APPLY

The Cross Apply returns rows from 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.

Example: To view the working of the Cross Apply operator first we shall create two tables namely EmployeeDetails and EmpSalary.

Below is the schema and create a query for EmployeeDetails

    CREATE TABLE EmployeeDetails (
      EmpId int PRIMARY KEY,
      EmpFirstName VARCHAR(50),
      EmpLastName VARCHAR(50),
      Department VARCHAR(50),
      DepartID INT
    )
                

The data in EmployeeDetails table is provided below

Now Create another table EmpSalary

    CREATE TABLE EmpSalary (
      EmpID INT,
      EmpFullName VARCHAR(80),
      EmpSalary INT,
      EmpWorkingYears INT,
      DepartID INT
    )
                

Here is the data in EmpSalary table

Now we will create a user defined function of SQL server with the name fn_Salaryinc, this function returns output with increased salary by Rs.5000 on the basis of DepartID column.

    CREATE FUNCTION fn_Salaryinc (@DepartmentID int)
    RETURNS TABLE
    AS
    RETURN
    (
      SELECT 
        EmpID, EmpFullName,
        EmpSalary+5000 AS Salaryinc
      FROM
        Empsalary
      WHERE
       DepartID = @DepartmentID 
  
    )
    GO

                

Use funtion fn_Salaryinc to get increased salary.

    SELECT EmpID, Salaryinc FROM fn_Salaryinc(2)
                

After applying the above function the salary got increased by 5000. The below table shows the output for the function fn_Salaryinc

Now since the function is giving the desired results we will write a Cross apply Query

    SELECT
      e.EmpFirstName,
      e.EmpLastName,
      f.Salaryinc
    FROM
      EmployeeDetails AS e
    CROSS APPLY
      fn_Salaryinc  (e.DepartID) AS f

                

The output of the above query which shows the functioning for Cross Apply operator

We are getting repetitive employees as we are running the function, and it gets data once for each Departid.

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 match the condition from the table-valued function (which is on the right side of the operator), NULL values are displayed.

The Outer Apply is equivalent to Left Outer Join, but it works with a table-valued function.

Example: First create a table EmployeeDetails with the below given query

                    
    CREATE TABLE EmployeeDetails (
      EmpId int PRIMARY KEY,
      EmpFirstName VARCHAR(50),
      EmpLastName VARCHAR(50),
      Department VARCHAR(50),
      DepartID INT
    )
                

Here is the EmployeeDetails data

Create another table EmployeeProject

     CREATE TABLE EmployeeProject (
          EmpID INT,
          DepartmentName VARCHAR(100),
          DepartID INT,
          ProjectName VARCHAR(100),
          Projectid VARCHAR(50)
    )
                

The data for this table is

Let's create a function named fn_Project, the function returns the column from EmployeeProject table after producing DepartID as a parameter

    CREATE FUNCTION fn_Project(@DepartID INT)
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT
            EmpID,
            DepartmentName,
            ProjectName
        FROM
            EmployeeProject
        WHERE
            DepartID = @DepartID
    )
    GO

                

Run the below given query to verify the results

    SELECT  * FROM fn_Project(2)
                

Now we will write an Outer Apply Query in order to check the functioning and the results of the operator.

    SELECT 
        e.EmpID, 
        e.EmpFirstName ,
        fn.DepartmentName, 
        fn.Projectname 
    FROM
 	    Employeedetails e 
    OUTER APPLY
  	    fn_Project (e.departID) AS fn

                

The Outer Apply query gives the result as shown below

Hence by using the Outer Apply operator we are able to return all the rows from the outer table no matter if the function returns any row.

Difference between Outer Apply and Cross Apply

Let me show you another example using the same above Outer Apply query with Cross Apply, it will clearly show the difference as the number of rows will be lesser as per the join condition.

    SELECT  
        e.EmpID, 
        e.EmpFirstName,
 	    fn.DepartmentName, 
        fn.Projectname 
    FROM
 	    Employeedetails e 
    Cross APPLY
  	    fn_Project (e.departID) AS fn