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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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:
|
1 2 3 4 |
SELECT A.author_name, B.id, B.book_name, B.price FROM Author A INNER JOIN Book B ON 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.
|
1 2 3 4 |
SELECT A.author_name, B.id, B.book_name, B.price FROM Author A LEFT JOIN Book B ON 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:
|
1 2 3 4 5 6 7 8 |
CREATE FUNCTION fnGetBooksByAuthorId(@AuthorId int) RETURNS TABLE AS RETURN ( SELECT * FROM Book WHERE 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:
|
1 2 3 4 |
SELECT A.author_name, B.id, B.book_name, B.price FROM Author A INNER JOIN fnGetBooksByAuthorId(A.Id) B ON 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:
|
1 2 3 4 |
SELECT A.author_name, B.id, B.book_name, B.price FROM Author A CROSS 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.
|
1 2 3 |
SELECT A.author_name, B.id, B.book_name, B.price FROM Author A OUTER 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.
View all posts by Ben Richardson
- Working with the SQL MIN function in SQL Server - May 12, 2022
- SQL percentage calculation examples in SQL Server - January 19, 2022
- Working with Power BI report themes - February 25, 2021