SQL: Join First Table On the Top 1 Row of Second Table For Each Unique Id Using CROSS APPLY and OUTER APPLY Instead of INNER JOIN and LEFT JOIN

12/5/2022 10:17:07 PM

SQL

SQL Server

INNER and LEFT JOINS work great for connecting data across multiple tables. However, if you are trying to connect to only 1 row on a second table (most recent, highest rated, etc.), JOINS are going to leave you wanting more. But there is a very simple solution.

CROSS APPLY and OUTER APPLY work very similar to standard JOINs but they allow you to pass values from the first table into a query (table value function) that defines the second table. This will allow you to pass an Id from the first table, into a second query, and select only 1 row from that second table/query.

Assume we have the following 2 tables:

Users

Id FirstName LastName
1 First1 Last1
2 First2 Last2
3 First3 Last3

 

UserPosts

Id UserId Title DateCreated
1 1 Posting Monday 2022-12-05
2 1 Posting Tuesday 2022-12-06
3 1 Posting Friday 2022-12-09
4 1 Posting Saturday 2022-12-10
5 2 Hello World 2022-12-05
6 2 Goodbye Cruel World 2022-12-06

Now, lets get users and their posts.

INNER JOIN

This will return all users who have a post and every post that user has.


-- inner join 
SELECT
		Users.*
		,UserPosts.*

FROM	Users
		join UserPosts on Users.Id = UserPosts.UserId

LEFT JOIN

This will return all users, even if they haven't posted, and all posts they have.


-- left join 
SELECT
		Users.*
		,UserPosts.*

FROM	Users
		left join UserPosts on Users.Id = UserPosts.UserId

CROSS APPLY

This will return all users who have a post and every post that user has.


-- cross apply (instead of inner join)
SELECT
		Users.*
		,UserPosts.*

FROM	Users
		CROSS APPLY
		(
			SELECT	UserPosts.Id
					,UserPosts.UserId
					,UserPosts.Title
					,UserPosts.DateCreated
			FROM	UserPosts
			WHERE	UserPosts.UserId = Users.Id -- the value we are passing in from the fist table
		) AS UserPosts

OUTER APPLY

This will return all users, even if they haven't posted, and all posts they have.


-- outer apply (instead of left join)
SELECT
		Users.*
		,UserPosts.*

FROM	Users
		OUTER APPLY
		(
			SELECT	UserPosts.Id
					,UserPosts.UserId
					,UserPosts.Title
					,UserPosts.DateCreated
			FROM	UserPosts
			WHERE	UserPosts.UserId = Users.Id -- the value we are passing in from the fist table
		) AS UserPosts

CROSS APPLY TOP 1

This will return all users who have a post AND ONLY THEIR MOST RECENT POST.


-- cross apply top 1
SELECT
		Users.*
		,UserPosts.*

FROM	Users
		CROSS APPLY
		(
			SELECT		TOP 1
						UserPosts.Id
						,UserPosts.UserId
						,UserPosts.Title
						,UserPosts.DateCreated
			FROM		UserPosts
			WHERE		UserPosts.UserId = Users.Id -- the value we are passing in from the fist table
			ORDER BY	UserPosts.DateCreated DESC
		) AS UserPosts

OUTER APPLY TOP 1

This will return all users, even if they haven't posted, AND ONLY THEIR MOST RECENT POST.


-- outer apply (instead of left join)
SELECT
		Users.*
		,UserPosts.*

FROM	Users
		OUTER APPLY
		(
			SELECT		TOP 1
						UserPosts.Id
						,UserPosts.UserId
						,UserPosts.Title
						,UserPosts.DateCreated
			FROM		UserPosts
			WHERE		UserPosts.UserId = Users.Id -- the value we are passing in from the fist table
			ORDER BY	UserPosts.DateCreated DESC
		) AS UserPosts