Parentheses Around Joins

This is something that I just learned about recently.

Here’s the scenario:  we have four tables.  A User has separate OAuth authorization types (for example, Facebook, Google, and Twitter), which we track through the UserAuthorization table.   Finally, each user has a number of items that they have to offer, but not all items need to be tied to a user.

The question that the following query tries to answer is, which items are either not owned by a user at all, or are owned by a user who has linked their Twitter account to the application?  In this case, we only want to get the Username value for users who have Twitter; if they don’t have Twitter, we don’t want to see the username at all.  This is kind of a silly query, but bear with me.  It turns out that you can use parentheses to direct inner and outer joins, allowing us to answer exactly this question.

select
	i.ID,
	u.Username
from
	dbo.Item i
	LEFT OUTER JOIN
	(
		dbo.[User] u
		INNER JOIN dbo.UserAuthorization ua on ua.UserId = u.Id
		INNER JOIN Lookup.OAuthType ot
			on ua.OAuthTypeId = ot.id
			and ot.Name = 'Twitter'
	) on i.UserId = u.Id;

This nested join means that we only get records back from the User table if there is a Twitter handle, but we do not exclude any records from the Item table; neither do we include any users who do not have Twitter handles.

There are other ways of doing this, such as two queries using a UNION ALL.  This is a succinct way of doing that, however, and could potentially be better for performance, because it would not need to scan the Item table twice (once for each side of the UNION ALL).

About these ads