The Difference between CROSS APPLY and OUTER APPLY in SQL Server June 6, 2018 by Ben Richardson 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: 123456789101112131415161718192021222324252627282930313233343536373839404142434445 CREATE DATABASE Library GO USE Library; CREATE TABLE Author( id INT PRIMARY KEY, author_name VARCHAR(50) NOT NULL, ) CREATE TABLE Book( id INT PRIMARY KEY, book_name VARCHAR(50) NOT NULL, price INT NOT NULL, author_id INT NOT NULL ) USE Library; INSERT INTO Author VALUES(1, 'Author1'),(2, 'Author2'),(3, 'Author3'),(4, 'Author4'),(5, 'Author5'),(6, 'Author6'),(7, 'Author7') INSERT INTO Book VALUES(1, 'Book1',500, 1),(2, 'Book2', 300 ,2),(3, 'Book3',700, 1),(4, 'Book4',400, 3),(5, 'Book5',650, 5),(6, 'Book6',400, 3) 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: 1234 SELECT A.author_name, B.id, B.book_name, B.priceFROM Author AINNER JOIN Book BON A.id = B.author_id 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. 1234 SELECT A.author_name, B.id, B.book_name, B.priceFROM Author ALEFT JOIN Book BON A.id = B.author_id 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: 12345678 CREATE FUNCTION fnGetBooksByAuthorId(@AuthorId int)RETURNS TABLEASRETURN( SELECT * FROM BookWHERE author_id = @AuthorId) 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: 1 SELECT * FROM fnGetBooksByAuthorId(3) 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: 1234 SELECT A.author_name, B.id, B.book_name, B.priceFROM Author AINNER JOIN fnGetBooksByAuthorId(A.Id) BON A.id = B.author_id 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: 1234 SELECT A.author_name, B.id, B.book_name, B.priceFROM Author ACROSS APPLY fnGetBooksByAuthorId(A.Id) B 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. 123 SELECT A.author_name, B.id, B.book_name, B.priceFROM Author AOUTER APPLY fnGetBooksByAuthorId(A.Id) B 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. About Latest Posts Ben RichardsonBen 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 blogView all posts by Ben Richardson Latest posts by Ben Richardson (see all) Nested Triggers in SQL Server - March 27, 2019 Python in SQL Server: The Basics - February 13, 2019 Importing and Working with CSV Files in SQL Server - January 28, 2019 Related posts: Few Outer Rows Optimization in SQL Server Difference between Identity & Sequence in SQL Server SQL Join types overview and tutorial Identifying Object Dependencies in SQL Server Management Studio What is the difference between Clustered and Non-Clustered Indexes in SQL Server?