SQL Server CROSS and OUTER APPLY

Table valued functions are a type of functions in SQL Server that returns data as tables. When performing JOINS in SQL Server, you can join rows from two or more database tables. However, the ability to join a table with an output of a table valued function is not supported unless you use APPLY operators.

SQL Server supports two apply operators:

CROSS APPLY.
OUTER APPLY.

CROSS apply is closely similar, semantically, to an SQL Server INNER JOIN. This means that it retrieves the rows of a table valued function and that of the table to join only where there are matches between the two tables.

OUTER APPLY, will join the records of a table valued function, and the defined table, regardless of any matching rows.

To better understand how to use CROSS and OUTER apply operators in SQL Server, let us take a practical approach.

Start by creating a test database:

create database applydb;

Switch to the create database:

use applydb;

Next, create tables and insert sample data as shown:

createtableusers
(
id int identity primary keynot null,
username varchar(50) not null,
)
createtable posts
(
id int identity primary keynot null,
post_name varchar(255) not null,
category varchar(50),
pub_date date,
user_id int not null
);
insertintousers(username)
values ('breadyoga'),
('sputnik1'),
('babayaga'),
('jagten'),
('lavalp');

insertinto posts (post_name, category, pub_date, user_id)
values ('title1', 'category1', '2021-12-31', 1),
('title2', 'category2', '2019-03-03', 1),
('title3', 'category3', '2021-01-01', 2),
('title4', 'category4', '2021-06-07', 5),
('title5', 'category5', '2020-04-11', 3);

The example query script creates two tables: users and posts. The posts table contains a user_id column which holds the values from the users’ table allowing one-to-many relationships in the database.

SQL Server CROSS APPLY

Let us attempt an inner join with a table valued table as:

createfunction getpostsbyid(@id int)
returnstable
as
return
(
select * from posts where user_id = 1
);
go
select u.username, p.id, p.post_name, p.pub_date
fromusers u
innerjoin getpostsbyid(u.id) p
on u.id = p.user_id;

SQL Server will return an error as:

To solve this, we can use CROSS APPLY AS:

select u.username, p.id, p.post_name, p.pub_date

from users u

cross apply getpostsbyid(u.id) p

The query above should execute successfully and return result as:

SQL Server OUTER APPLY

We can fetch the rows from the physical and the table valued function using outer apply as shown:

select u.username, p.id, p.post_name, p.pub_date

from users u

outer apply getpostsbyid(u.id) p

The resulting query set is as shown:

Conclusion

This article covers how to work with SQL Server outer and cross apply operators to join a physical table and a table valued function.

SQL Server CROSS and OUTER APPLY

SQL Server CROSS APPLY

SQL Server OUTER APPLY

Conclusion

About the author

John Otieno