Have you heard of the SQL Cross Apply feature but have no idea what it does? Or are you familiar with how both Cross Apply and Outer Apply work but don’t know how they can work in the real world?
Learn all about Cross Apply and Outer Apply in this guide, including:
- What Cross Apply is
- What Outer Apply is
- How they differ from Inner and Outer Joins
- Which databases support this feature
- Why you would use it
- Some realistic examples
Let’s get into the guide.
What is Cross Apply?
Cross Apply is an SQL feature that was introduced in SQL Server that works in a similar way to a join. It lets you join a table to a “table-valued function”, or to join to a subquery that refers to the outer query for each row, which is not possible with joins.
We’ll look at what a “table-valued function” is, and how this is different to a join, later in this guide.
The feature also works in Oracle databases.
What is Outer Apply?
Outer Apply is an SQL feature that works in a similar way to a join. It lets you perform an outer join to a “table-valued function”, which is something that an outer join does not support.
It’s different to a Cross Apply due to the treatment of non-matching rows: a Cross Apply is like an inner join and an Outer Apply is like an outer join.
Which Databases Support Cross Apply and Outer Apply?
SQL Server and Oracle both support the Cross Apply and Outer Apply feature.
SQL Server 2005 was released in November 2005. Oracle then introduced the Cross Apply feature in 12c which was released in June 2013.
The Cross Apply and Outer Apply features are not available in MySQL or PostgreSQL. A feature called Lateral Joins, which is similar, was introduced in MySQL 8.0.14 and PostgreSQL 9.0.
What is a Table-Valued Function?
To understand how Cross Apply and Outer Apply work, and why they are needed, it helps to understand what a table-valued function is.
A table-valued function is a function that returns a single set of data. It’s returned as a Table data type.
You can use this returned table just like a regular table.
Here’s an example of a table-valued function. The examples in this guide use SQL Server syntax and data types, but the concepts also apply to Oracle SQL.
First, we create a table and add some sample data.
CREATE TABLE product (
product_id INT,
product_name VARCHAR(100),
active INT
);
INSERT INTO product (product_id, product_name, active) VALUES
(1, 'Chair', 1),
(2, 'Table', 0),
(3, 'Couch', 1),
(4, 'Desk', 1),
(5, 'Dining Table', 0);Now, let’s say we want to create a table-valued function to return a table of data. This will return all products that match the provided “active” value.
This function is not a realistic function, as we could just use a regular query or a view, but it demonstrates the concept.
CREATE FUNCTION GetProductDetails (
@activeIndicator INT
)
RETURNS TABLE
AS
RETURN
SELECT
product_id,
product_name
FROM product
WHERE active = @activeIndicator;There are a few things to notice here.
The RETURNS TABLE indicates that the result of the Select query in the function is returned as a table to whatever calls the function. This will return a table of the results.
The parameter @activeIndicator is used in the WHERE clause of the query.
We can test this function by calling it in a Select query with a parameter.
SELECT *
FROM GetProductDetails(1);The results are:
| product_id | product_name |
| 1 | Chair |
| 3 | Couch |
| 4 | Desk |
The results of this query are the results of the GetProductDetails function, which returns the results of a Select query on the product table.
You’re probably thinking, “why do I need this function when I can just select from the table directly?”
And that’s a good question. This is not a realistic use of a table-valued function. It’s just a simple example.
Later in this guide, we’ll look at some reasons and realistic scenarios for why you would use table-valued functions and the Cross Apply feature.
How does Cross Apply Work in SQL?
We’ve seen how table-valued functions work in SQL. How does Cross Apply work?
It works in a similar way to a join. The Cross Apply keyword is used, and the function or table that’s being joined to can have a parameter provided or joined to the rest of the query.
Let’s see a couple of examples.
Usage 1: Joining a Table to a Table-Valued Function
Let’s see an example using the product table above, but with some modifications so that we can store the manufacturer of each product as well.
Sample Data
Here’s the script for creating and populating the tables. Notice that the product table now has a foreign key to the manufacturer table, and there is no active column.
CREATE TABLE manufacturer (
manufacturer_id INT PRIMARY KEY,
manufacturer_name VARCHAR(100)
);
INSERT INTO manufacturer (manufacturer_id, manufacturer_name) VALUES
(1, 'Jims Furniture'),
(2, 'Luxury Construction');
CREATE TABLE product (
product_id INT,
product_name VARCHAR(100),
manufacturer_id INT,
CONSTRAINT fk_prod_man
FOREIGN KEY manufacturer_id
REFERENCES manufacturer(manufacturer_id)
);
INSERT INTO product (product_id, product_name, manufacturer_id) VALUES
(1, 'Chair', 1),
(2, 'Table', 1),
(3, 'Couch', 2),
(4, 'Desk', 2),
(5, 'Dining Table', 1);Here’s our updated table function. We provide the manufacturer_id as a parameter, and products are returned that match this value.
CREATE FUNCTION GetProductsForManufacturer (
@manufacturerID INT
)
RETURNS TABLE
AS
RETURN
SELECT
product_id,
product_name,
manufacturer_id
FROM product
WHERE manufacturer_id = @manufacturerID;Let’s select from this function to see what it shows.
SELECT *
FROM GetProductsForManufacturer(1);The results are:
| product_id | product_name | manufacturer_id |
| 1 | Chair | 1 |
| 2 | Table | 1 |
| 5 | Dining Table | 1 |
Try an Inner Join
Let’s say we have this function, but we want to see manufacturer details as well.
We could try this by selecting from the manufacturer table and joining to the results of this function. This function is a table-valued function, which returns a table, so we should be able to join to it, right?
Let’s try it.
SELECT
m.manufacturer_id,
m.manufacturer_name,
f.*
FROM manufacturer m
INNER JOIN GetProductsForManufacturer(m.manufacturer_id) f
ON f.manufacturer_id = m.manufacturer_id;This query joins the manufacturer table to the function based on the manufacturer_id.
If we run this query, you’ll get an error.
This error happens because the JOIN feature does not allow you to join to a table-valued function.
So how can you do this? How can you get the results of a table-valued function and include them in your query?
While you’re here, if you want an easy-to-use list of the main features in SQL for different vendors, get my SQL Cheat Sheets here:
Use Cross Apply
We can combine the results of a table-valued function and other tables in a query using the Cross Apply feature.
It looks similar to a join. Here’s what our example would look like:
SELECT
m.manufacturer_id,
m.manufacturer_name,
f.*
FROM manufacturer m
CROSS APPLY GetProductsForManufacturer(m.manufacturer_id) f
WHERE f.manufacturer_id = m.manufacturer_id;This will join the manufacturer table to the results of the GetProductsForManufacturer column. We can provide the parameter to the function and run it without errors.
This is what we’ll get when we run this query:
| manufacturer_id | manufacturer_name | product_id | product_name | manufacturer_id |
| 1 | Jims Furniture | 1 | Chair | 1 |
| 1 | Jims Furniture | 2 | Table | 1 |
| 1 | Jims Furniture | 5 | Dining Table | 1 |
| 2 | Luxury Construction | 3 | Couch | 2 |
| 2 | Luxury Construction | 4 | Desk | 2 |
This is a simple example, and you can do the same thing without this function, but it shows that using a table-valued function and a Select query with Cross Apply can give you the result you need.
Usage 2: Cross Apply with a Table
Another way to use Cross Apply is when you want to join two tables based on the results of a TOP N query.
This example was based on a StackOverflow answer here.
Let’s say we have two tables (product and product_category) and we want to see the two most recent products in each category.
Here’s our product_category table:
CREATE TABLE product_category (
cat_id INT,
cat_name VARCHAR(100)
);
INSERT INT product_category (cat_id, cat_name) VALUES
(1, 'Lounge Room'),
(2, 'Bedroom');Here’s the contents of the table:
| cat_id | cat_name |
| 1 | Lounge Room |
| 2 | Bedroom |
Next, here’s our product table.
CREATE TABLE product (
product_id INT,
product_name VARCHAR(100),
date_added DATE,
product_category_id INT
);
INSERT INTO product (product_id, product_name, date_added, product_category_id) VALUES
(1, 'Couch', '2021-01-04', 1),
(2, 'Bed frame', '2021-02-06', 2),
(3, 'Queen mattress', '2021-01-20', 2),
(4, 'Ottoman', '2021-03-06', 1),
(5, 'Chair', '2021-02-18', 1),
(6, 'TV unit', '2021-01-21', 1),
(7, 'Side table', '2021-01-13', 2);Here are the contents of the table:
| product_id | product_name | date_added | product_category_id |
| 1 | Couch | 2021-01-04 | 1 |
| 2 | Bed frame | 2021-02-06 | 2 |
| 3 | Queen mattress | 2021-01-20 | 2 |
| 4 | Ottoman | 2021-03-06 | 1 |
| 5 | Chair | 2021-02-18 | 1 |
| 6 | TV unit | 2021-01-21 | 1 |
| 7 | Side table | 2021-01-13 | 2 |
Now we have our sample data, let’s write a query.
We want to see the product categories and the two most recently added products in each category.
We can try that with an Inner Join:
SELECT
c.cat_id,
c.cat_name,
p.product_name,
p.date_added
FROM product_category c
INNER JOIN (
SELECT TOP 2
product_id,
date_added,
product_category_id
FROM product
ORDER BY date_added DESC
) p
ON c.cat_id = p.product_category_id;This query will run and give us the following result:
| cat_id | cat_name | product_name | date_added |
| 1 | Lounge Room | Ottoman | 2021-03-06 |
| 1 | Lounge Room | Chair | 2021-02-18 |
This doesn’t give us what we want. It shows the two products with the most recent date_added values across all categories. It then shows the categories for those two products.
We want to see all categories and the top products for those categories.
We can do this using Cross Apply.
SELECT
c.cat_id,
c.cat_name,
p.product_name,
p.date_added
FROM product_category c
CROSS APPLY (
SELECT TOP 2
product_id,
date_added,
product_category_id
FROM product p
WHERE c.cat_id = p.product_category_id
ORDER BY date_added DESC
);We’ve used Cross Apply, and moved the column matching to within the subquery. This matching in the subquery can only be done with a Cross Apply.
Here are the results of the query.
| cat_id | cat_name | product_name | date_added |
| 1 | Lounge Room | Ottoman | 2021-03-06 |
| 1 | Lounge Room | Chair | 2021-02-18 |
| 2 | Bedroom | Bed frame | 2021-02-06 |
| 2 | Bedroom | Queen mattress | 2021-01-20 |
We can see both categories are showing, and the two top products for each category.
This is the result we want.
Why not use a left outer join to get the result we want?
This won’t work because:
- We can’t refer to an outer query column inside a subquery using a join.
- The left join will show NULL values for the product columns if no value is found, and we want to see product_name and date_added, as shown in the cross apply example.
Example of Outer Apply
The Outer Apply works in a similar way to Cross Apply, but uses the feature of an Outer Join to show NULL values where no matches are found.
Using our earlier example of products and manufacturers, we can add a new manufacturer that has no products.
INSERT INTO manufacturer (manufacturer_id, manufacturer_name) VALUES
(3, 'XYZ Construction');Here’s our query with Cross Apply.
SELECT
m.manufacturer_id,
m.manufacturer_name,
f.*
FROM manufacturer m
CROSS APPLY GetProductsForManufacturer(m.manufacturer_id) f
WHERE f.manufacturer_id = m.manufacturer_id;This will show all manufacturers and products that are related.
| manufacturer_id | manufacturer_name | product_id | product_name | manufacturer_id |
| 1 | Jims Furniture | 1 | Chair | 1 |
| 1 | Jims Furniture | 2 | Table | 1 |
| 1 | Jims Furniture | 5 | Dining Table | 1 |
| 2 | Luxury Construction | 3 | Couch | 2 |
| 2 | Luxury Construction | 4 | Desk | 2 |
What about the new manufacturer with no products?
If we want to see this, we have to use an Outer Apply.
SELECT
m.manufacturer_id,
m.manufacturer_name,
f.*
FROM manufacturer m
OUTER APPLY GetProductsForManufacturer(m.manufacturer_id) f
WHERE f.manufacturer_id = m.manufacturer_id;The results are:
| manufacturer_id | manufacturer_name | product_id | product_name | manufacturer_id |
| 1 | Jims Furniture | 1 | Chair | 1 |
| 1 | Jims Furniture | 2 | Table | 1 |
| 1 | Jims Furniture | 5 | Dining Table | 1 |
| 2 | Luxury Construction | 3 | Couch | 2 |
| 2 | Luxury Construction | 4 | Desk | 2 |
| 3 | XYZ Construction | NULL | NULL | NULL |
We can see that the last row shows the new manufacturer, but NULL values for the product columns.
Why Use Cross Apply or Outer Apply in SQL?
There are a few reasons why you may want to use Cross Apply or Outer Apply in SQL.
Join table-valued functions to other tables: this is not something you can do with a Join but you can do it with Cross Apply or Outer Apply.
Get results for each row of an outer query: you can use an inner query and get results that differ for each row of an outer query using Cross Apply, as shown in the “top 2” example above.
If you have any situations where you need to join to a subquery and can’t do it with a Join, try using Cross Apply or Outer Apply.
Conclusion
The SQL Cross Apply and Outer Apply features in SQL Server and Oracle can be useful to get you results that would have been complicated or not possible in other ways. Understanding how they work and when to use them is a good way to improve your SQL skills.
While you’re here, if you want an easy-to-use list of the main features in SQL for different vendors, get my SQL Cheat Sheets here:
Thanks for this, very helpful and much appreciated.
Great article, thank you. One thing to note though is that Oracle *does* let you join to table functions. So if GetProductsForManufacturer is created as a table function in Oracle, then this 1st example from above absolutely works:
SELECT
m.manufacturer_id,
m.manufacturer_name,
f.*
FROM manufacturer m
INNER JOIN GetProductsForManufacturer(m.manufacturer_id) f
ON f.manufacturer_id = m.manufacturer_id;
*this works in 18c or above, but otherwise you’d just need to surround the function call with TABLE()
Thanks for this helpful article. There is a problem with the example code that creates the Manufacturer table the Usage 1 example, however. Manufacturer_id needs to be defined as the primary key on the table in order for the foreign key definition in the Product table to work.
Thanks for the blog post !
IMHO there is a typo (Copy & Paste error) in the last sample query. It is announced a OUTER APPLY join, but the query shows a CROSS APPLY.
Thanks for letting me know!
Article seems useful, but there’s a weird auto-scroll behavior with the website, on Chrome, that makes it very hard to read.
Hi Jo, could you let me know what’s happening? Is it on desktop or mobile? I can’t seem to reproduce it, but someone else told me the same thing recently so I want to know more.
Hi Ben,
thanks for the explanation!
One thing: in the last script in “usage 2” there is a “p” missing which confused me a lot today ;).
Greetings,
Frank
Thanks for the article. This subject is difficult to understand, especially benefits and opportunities to solve problems.
I worked with a large healthcare reporting database. NULLs were always an issue so rarely used CROSS APPLY, but was a huge fan of OUTER APPLY and reusable table value functions. My TVF’s only returned a single record with multiple columns, a ‘table of one.row’ That allowed me to return a list of calculated fields, like a series of MSSQL Scalar functions, all from one well tested reusable package. A nested inner query was used for the expensive work of filtering data to a reasonable result set. One or more TVF’s were OUTER APPLY’d in an outer query. In the TVF the first step was to read in all needed data for the inner query row, just once, into a table variable. Then return fields were calculated and assigned one by one. Was able to outer join a dozen or more TVF’s with highly efficient results. My favorite technique.