Filter Data in Outer Joins

Aim: To clarify the difference between filtering data in a WHERE clause or within the OUTER JOIN.

When using an OUTER JOIN, there will be a difference in the results depending on whether the data is filtered in a WHERE clause or within the OUTER JOIN itself.

The below queries have been run against a copy of the publicly-available StackOverflow database. For context, the initial query does not contain any filtering. As expected, it returns all the rows that appear in the VoteTypes table but not in Votes.

SELECT VT.Name AS VoteType
    ,V.Id AS VoteId
    ,V.UserId
FROM dbo.VoteTypes VT
LEFT OUTER JOIN dbo.Votes V
ON VT.Id = V.VoteTypeId;

To exclude the Votes rows where the UserId is NULL, there appear to be two options:

Option 1: Filtering data in a WHERE clause

SELECT VT.Name AS VoteType
    ,V.Id AS VoteId
    ,V.UserId
FROM dbo.VoteTypes VT
LEFT OUTER JOIN dbo.Votes V
ON VT.Id = V.VoteTypeId
WHERE V.UserId IS NOT NULL;

While this query excludes the Votes rows where UserId is NULL, it also excludes the VoteTypes rows which do not have a match in Votes. It effectively makes the LEFT OUTER JOIN behave like an INNER JOIN. This is not the desired behaviour.

Option2: Filtering data within the OUTER JOIN

SELECT VT.Name AS VoteType
    ,V.Id AS VoteId
    ,V.UserId
FROM dbo.VoteTypes VT
LEFT OUTER JOIN dbo.Votes V
ON VT.Id = V.VoteTypeId
    AND V.UserId IS NOT NULL;

Like the initial query, this query returns all the rows that appear in the VoteTypes table but not in Votes. It also excludes the Votes rows where UserId is NULL, which is the desired behaviour.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!

%d bloggers like this: