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.

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: