Understanding T-SQL operator CROSS APPLY, and OUTER APPLY in SQL Server

SQL Server offers JOIN statements to join more than one tables in the T-SQL statements and get the desired output based on our business needs. We all know that we get distinct requirements in our day to day business operations so one solution does not work for every requirement.

Let’s take an example, suppose you got a requirement to join a SQL Server table and a table valued function in a T-SQL query to get your desired output. T-SQL statement JOIN will not work in this case and that is why Microsoft has introduced APPLY operators in T-SQL query to address this requirement. You can use either SQL Server management studio or other third party SQL tools for your database developments.

We can get output using the APPLY operator by joining more than one table just like JOINs as well as by joining tables and table valued functions together.

There are 2 types of APPLY operators.

• CROSS APPLY
• OUTER APPLY

The result set of the CROSS APPLY operator is similar to the INNER JOIN statement if you join two tables whereas OUTER APPLY works like LEFT OUTER JOIN if you compare their result set.

I will show you the comparison of CROSS APPLY & INNER JOIN and OUTER APPLY & LEFT OUTER JOIN in the below use cases to demonstrate their similar result set.

The CROSS APPLY operator returns a result set from the left table expression if it matches with the right table expression whereas the OUTER APPLY returns the result set from the left table expression irrespective of its match with the right table expression.

Today, I will demonstrate the use cases of both APPLY operators by joining more than one table and by joining tables with the table valued functions in this article. I will also show you the error you will receive when you join a table with a table valued function using a JOIN statement.

There are two sets of use cases in this article. One is by joining a table with a table valued function using APPLY operator and another one is by joining two tables using APPLY operator in the below section of this article.

Join table and table valued function using APPLY operator

Let’s start the first set of use cases in which we will join the SQL Server table and its table valued function using APPLY operator to get our desired output.
I already have 2 tables in my test database TESTDB. The names of the tables are Associates and OrderDetails. The Associates table stores information about each employee whereas the OrderDetails table stores sales related information.
Now, I will create a table valued function named fn_returnempsales to return sales details based on the employee id. Next, we need to create a table valued function and then we will join them to get the result set.

Create a table valued function

I have used the below statements to create a function named fn_returnempsales to return all the sales details based on the employee id. We need to pass the employee id to return all products sold by that employee.

-- Create a function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION fn_returnempsales(@PersonID AS INT)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM [dbo].[OrderDetails] od
WHERE od.Person.ID = @PersonID
)
GO

The above T-SQL statement has been successfully completed in the below image.

Let’s validate this function by passing any employee id to see all product sales done by that employee along with the other details as shown in the below example. We can see employee if 5 is Julie and she has converted 3 sales as shown in the below image.

Test joining table and table valued function using INNER JOIN

Here, we can see what will happen if we join a table with a table valued function using INNER JOIN. The query is throwing an error using INNER JOIN as we can see in the below image hence the requirement came to introduce APPLY operator in T-SQL.

Next, I will show you the joining table with table valued function using APPLY operators in the below section. Let’s start joining using CROSS APPLY first.

Join table and table valued function using CROSS APPLY

Here, I will join table Associates with table valued function fn_returnempsales to return its result set. I am fetching 3 columns from table Associates and 3 columns from table valued function fn_returnempsales.

SELECT a.name, a.city, a.designation, fn.ProductName, fn.Price, fn.InvoiceMonth
FROM [dbo].[Associates] as a
CROSS APPLY [dbo].[fn_returnempsales](a.ID) as fn
GO

When you run the above T-SQL statement, you will get the below result set. We can see all matched rows are displayed in the below output.

Join table and table valued function using OUTER APPLY

Here, I will show you the same example which I showed you in the above section but using OUTER APPLY.

SELECT a.name, a.city, a.designation, fn.ProductName, fn.Price, fn.InvoiceMonth
FROM [dbo].[Associates] as a
OUTER APPLY [dbo].[fn_returnempsales](a.ID) as fn
GO

The below result set will be displayed if you run the above query. We can see the number of rows is showing more than the no of rows displayed in the result set of the CROSS APPLY operator.

If you notice the below result set, you will observe that it is displaying even those rows which have NULL or no values. It means employees given from row no 29 have not made any sales and that is why their entries are not there in the OrderDetails table and showing their values as NULL.

If you want to filter the result set by applying additional conditions with WHERE clauses, then you can also do it with APPLY operator.

--Use APPLY operator with WHERE clause.
SELECT a.name, a.city, a.designation, fn.ProductName, fn.Price, fn.InvoiceMonth
FROM [dbo].[Associates] as a
CROSS APPLY [dbo].[fn_returnempsales](a.ID) as fn
WHERE fn.Price>2000
GO
SELECT a.name, a.city, a.designation, fn.ProductName, fn.Price, fn.InvoiceMonth
FROM [dbo].[Associates] as a
OUTER APPLY [dbo].[fn_returnempsales](a.ID) as fn
WHERE fn.Price>2000
GO

Here, I fetched details of products and employees who have generated invoices of more than $2000.

Join tables using APPLY operator

We have learned how to use APPLY operator to join a table and a table valued function to get the desired result set. Now, I will demonstrate how to join more than one table using APPLY operator. I will also compare the result set returned by joining two tables using APPLY operator and using JOIN statements.

Join tables using CROSS APPLY

You can see the below T-SQL statement in which I have joined two tables Associates and OrderDetails using the CROSS APPLY operator.

SELECT a.name, a.city, a.designation, od2.ProductName, od2.Price, od2.InvoiceMonth
FROM [dbo].[Associates] as a
CROSS APPLY (
SELECT *
FROM [dbo].[OrderDetails] as od1
WHERE a.ID=od1.PersonID
) as od2

The result set of the above query is shown in the below image.

As I have stated above that the result set of CROSS APPLY will return equivalent to the INNER JOIN so let’s validate this statement with the help of the below example where I will execute two sets of queries; one is using INNER JOIN and another one is using CROSS APPLY.

-- Output using INNER JOIN
USE TESTDB
Go
SELECT a.name, a.city, a.designation, od.ProductName, od.Price, od.InvoiceMonth
FROM [dbo].[Associates] as a
INNER JOIN [dbo].[OrderDetails] as od
ON a.ID=od.PersonID
WHERE od.Price>2000

--Output using CROSS APPLY
USE TESTDB
Go
SELECT a.name, a.city, a.designation, od2.ProductName, od2.Price, od2.InvoiceMonth
FROM [dbo].[Associates] as a
CROSS APPLY (
SELECT *
FROM [dbo].[OrderDetails] as od1
WHERE a.ID=od1.PersonID) as od2
WHERE od2.Price>2000

Here is the output of the above statements. I have applied a condition to filter the output and reduce the number of rows returned for both outputs. Here we can see the output of both queries is the same. If you will remove the WHERE clause statement to remove the price filter, then also you will get the same output from both the queries. The number of rows will be increased in both results but they will be the same in both outputs.

Join tables using OUTER APPLY

Let’s understand the output of joining two tables using OUTER APPLY. I have used the same T-SQL query from the above example and just changed the APPLY operator from CROSS APPLY to OUTER APPLY.

SELECT a.name, a.city, a.designation, od2.ProductName, od2.Price, od2.InvoiceMonth
FROM [dbo].[Associates] as a
OUTER APPLY (
SELECT *
FROM [dbo].[OrderDetails] as od1
WHERE a.ID=od1.PersonID) as od2

When you execute the above query, you will get a result set like the one given in the below image. Here, we can see the number of rows has been increased if you compare the above example.

Here, we are also getting results for which there are NULL values. Those employees who have not done any sales so their entries are not they’re in the OrderDetails table but still, we can see them in the result set with the NULL value in the products and price columns because the result is showing all entries from the table Associates.

The OUTER APPLY returns a similar result to the LEFT OUTER JOIN. We can get it validated by joining both tables using the LEFT OUTER JOIN statement. Have a look at the below T-SQL where I have joined both tables using LEFT OUTER JOIN.

USE TESTDB
Go
SELECT a.name, a.city, a.designation, od.ProductName, od.Price, od.InvoiceMonth
FROM [dbo].[Associates] as a
LEFT OUTER JOIN [dbo].[OrderDetails] as od
ON a.ID=od1.PersonID

Here, you can compare the output of this query with the last query shown above. We can see both result sets are the same.

Conclusion

Today, I have explained APPLY operator and its use cases by joining tables and table valued functions in this article. I also explained CROSS APPLY and OUTER APPLY and their result set comparison with the INNER JOIN and LEFT OUTER JOIN statements. Please share this article and comment with us your feedback in the comment section so that we can improve in a better way.