Ben Richardson

The Difference between CROSS APPLY and OUTER APPLY in SQL Server

June 6, 2018 by

SQL Server supports table valued functions, what are functions that return data in the form of tables.

JOIN operations in SQL Server are used to join two or more tables. However, JOIN operations cannot be used to join a table with the output of a table valued function.

APPLY operators are used for this purpose.

There are two main types of APPLY operators. 1) CROSS APPLY and 2) OUTER APPLY.

The CROSS APPLY operator is semantically similar to INNER JOIN operator. It retrieves those records from the table valued function and the table being joined, where it finds matching rows between the two.

On the other hand, OUTER APPLY retrieves all the records from both the table valued function and the table, irrespective of the match.

In this article we will take a look at the CROSS APPLY and OUTER APPLY operators. We will see how they are implemented practically with the help of an example and will also discuss how they differ from each other.

Preparing dummy data

First, let’s create a dummy database with some dummy records in it. We will use this dummy database to perform different operations throughout this article. As ever if you are trying things out on a live database be sure to check that you are fully backed up.

Execute the following script:

In the script above we created a database named Library. The database has two tables: Author and Book. Book has an author_id column which contains values from the id column of the Author table. This means that there is a one to many relationships between the Author and Book columns.

Joining tables using JOIN operators

Let’s first use the INNER JOIN operator to retrieve matching rows from both of the tables.

Execute the following script:

The following records will be selected.

You can see that only those records have been selected from the Author table where there is a matching row in the Book table. To retrieve all the records from Author table, LEFT JOIN can be used.

The output of the above query looks like this:

You can see that all the records are retrieved from the Author table, irrespective of there being any matching rows in the Book table.

Joining table valued functions with tables using APPLY operators

We saw how JOIN operators join the results from two tables. However, as mentioned above they cannot be used to join a table valued function with a table. A table valued function is a function that returns records in the form of a table.

Let’s first write a simple table valued function that accepts author id as parameter and returns all the books written by that author.

Execute the following script:

Let’s test the above function. We will pass 3 as the author id to the fnGetBooksByAuthorId function. It should return book4 and book6, since these are the two books written by the author with the id of three.

Execute the following script:

The output of the above script looks like this:

Let’s try to use an INNER JOIN operator to join the Author table with the table valued function fnGetBooksByAuthorId.

Take a look at the following script:

Here we are using the INNER JOIN operator to join a physical table (Author) with a table valued function fnGetBooksByAuthorId. All the ids from the Author table are passed to the function. However, the script above throws an error which looks like this:

Joining table and table valued function using CROSS APPLY

Now, let’s use the CROSS APPLY operator to join the Author table with the table valued function fnGetBooksByAuthorId. The CROSS APPLY operator is semantically similar to INNER JOIN. It retrieves all the records from the table where there are corresponding matching rows in the output returned by the table valued function.

Take a look at the following script:

In the script above, all the ids from the Author table are being passed to fnGetBooksByAuthorId function. For each id in the Author table, the function returns corresponding records from the Book table. The result from this table valued function is being joined with the table Author.

The output of the above script looks like this:

This is similar to the INNER JOIN operation performed on the Author and Book tables. CROSS APPLY returns only those records from a physical table where there are matching rows in the output of the table valued function.

Joining table and table valued functions using OUTER APPLY

To retrieve all the rows from both the physical table and the output of the table valued function, OUTER APPLY is used. OUTER APPLY is semantically similar to the OUTER JOIN operation.

Take a look at the following script to see OUTER APPLY in action.

The output of the above function looks like this:

You can see that all the records from the Author table have been retrieved irrespective of the matching rows in the output from the table valued function fnGetBookByAuthorId.

Conclusion

In this article, we studied what the CROSS APPLY and OUTER APPLY functions are, and how they can be used to perform join operations between a physical table and table valued function.

We first used JOIN operators to join two physical tables. We then explained how JOIN operators can be replaced by APPLY operators in order to achieve the same results by joining a physical table with output of a table valued function.

See more

Consider these free tools for SQL Server that improve database developer productivity.

References

  1. Selecting from CROSS APPLY and OUTER APPLY
  2. Cross Apply in SQL Server
  3. Replacing Cursors With Joins
  4. Apply vs Join in SQL Server


Ben Richardson

Ben Richardson

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson
Ben Richardson
223 Views