SQL Cross Apply and Outer Apply: The Complete Guide

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.

 1CREATE TABLE product (
 2  product_id INT,
 3  product_name VARCHAR(100),
 4  active INT
 5);
 6
 7INSERT INTO product (product_id, product_name, active) VALUES
 8(1, 'Chair', 1),
 9(2, 'Table', 0),
10(3, 'Couch', 1),
11(4, 'Desk', 1),
12(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.

 1CREATE FUNCTION GetProductDetails (
 2  @activeIndicator INT
 3)
 4RETURNS TABLE
 5AS
 6  RETURN
 7    SELECT
 8    product_id,
 9    product_name
10    FROM product
11    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.

1SELECT *
2FROM GetProductDetails(1);

The results are:

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.

 1CREATE TABLE manufacturer (
 2  manufacturer_id INT PRIMARY KEY,
 3  manufacturer_name VARCHAR(100)
 4);
 5
 6INSERT INTO manufacturer (manufacturer_id, manufacturer_name) VALUES
 7(1, 'Jims Furniture'),
 8(2, 'Luxury Construction');
 9
10CREATE TABLE product (
11  product_id INT,
12  product_name VARCHAR(100),
13  manufacturer_id INT,
14  CONSTRAINT fk_prod_man
15    FOREIGN KEY manufacturer_id
16    REFERENCES manufacturer(manufacturer_id)
17);
18
19INSERT INTO product (product_id, product_name, manufacturer_id) VALUES
20(1, 'Chair', 1),
21(2, 'Table', 1),
22(3, 'Couch', 2),
23(4, 'Desk', 2),
24(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.

 1CREATE FUNCTION GetProductsForManufacturer (
 2  @manufacturerID INT
 3)
 4RETURNS TABLE
 5AS
 6  RETURN
 7    SELECT
 8    product_id,
 9    product_name,
10    manufacturer_id
11    FROM product
12    WHERE manufacturer_id = @manufacturerID;

Let's select from this function to see what it shows.

1SELECT *
2FROM GetProductsForManufacturer(1);

The results are:

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.

1SELECT
2m.manufacturer_id,
3m.manufacturer_name,
4f.*
5FROM manufacturer m
6INNER JOIN GetProductsForManufacturer(m.manufacturer_id) f
7ON 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:

1SELECT
2m.manufacturer_id,
3m.manufacturer_name,
4f.*
5FROM manufacturer m
6CROSS APPLY GetProductsForManufacturer(m.manufacturer_id) f
7WHERE 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:

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:

1CREATE TABLE product_category (
2  cat_id INT,
3  cat_name VARCHAR(100)
4);
5
6INSERT INT product_category (cat_id, cat_name) VALUES
7(1, 'Lounge Room'),
8(2, 'Bedroom');

Here's the contents of the table:

Next, here's our product table.

 1CREATE TABLE product (
 2  product_id INT,
 3  product_name VARCHAR(100),
 4  date_added DATE,
 5  product_category_id INT
 6);
 7
 8INSERT INTO product (product_id, product_name, date_added, product_category_id) VALUES
 9(1, 'Couch', '2021-01-04', 1),
10(2, 'Bed frame', '2021-02-06', 2),
11(3, 'Queen mattress', '2021-01-20', 2),
12(4, 'Ottoman', '2021-03-06', 1),
13(5, 'Chair', '2021-02-18', 1),
14(6, 'TV unit', '2021-01-21', 1),
15(7, 'Side table', '2021-01-13', 2);

Here are the contents of the table:

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:

 1SELECT
 2c.cat_id,
 3c.cat_name,
 4p.product_name,
 5p.date_added
 6FROM product_category c
 7INNER JOIN (
 8  SELECT TOP 2
 9  product_id,
10  date_added,
11  product_category_id
12  FROM product
13  ORDER BY date_added DESC
14) p
15ON c.cat_id = p.product_category_id;

This query will run and give us the following result:

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.

 1SELECT
 2c.cat_id,
 3c.cat_name,
 4p.product_name,
 5p.date_added
 6FROM product_category c
 7CROSS APPLY (
 8  SELECT TOP 2
 9  product_id,
10  date_added,
11  product_category_id
12  FROM product p
13  WHERE c.cat_id = p.product_category_id
14  ORDER BY date_added DESC
15);

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.

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.

1INSERT INTO manufacturer (manufacturer_id, manufacturer_name) VALUES
2(3, 'XYZ Construction');

Here's our query with Cross Apply.

1SELECT
2m.manufacturer_id,
3m.manufacturer_name,
4f.*
5FROM manufacturer m
6CROSS APPLY GetProductsForManufacturer(m.manufacturer_id) f
7WHERE f.manufacturer_id = m.manufacturer_id;

This will show all manufacturers and products that are related.

What about the new manufacturer with no products?

If we want to see this, we have to use an Outer Apply.

1SELECT
2m.manufacturer_id,
3m.manufacturer_name,
4f.*
5FROM manufacturer m
6OUTER APPLY GetProductsForManufacturer(m.manufacturer_id) f
7WHERE f.manufacturer_id = m.manufacturer_id;

The results are:

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: