What’s the difference between LEFT and RIGHT OUTER JOINs?

Leave a comment

August 3, 2016 by Kenneth Fisher

A DBA walked into a bar, saw a couple of tables, and asked “Can I JOIN you?”

There are several different kinds of JOINs. OUTER JOINs are one of the more complex options. With an INNER JOIN only those rows that have a match on both sides of the JOIN are returned. With an OUTER JOIN unmatched rows are returned also.

That’s where the LEFT, RIGHT and FULL comes in. They determine which unmatched rows are returned. Per BOL:

OuterJoin1
INNER

Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. When no join type is specified, this is the default.

 

 
FULL [ OUTER ]OuterJoin4_Full

Specifies that a row from either the left or right table that does not meet the join condition is included in the result set, and output columns that correspond to the other table are set to NULL. This is in addition to all rows typically returned by the INNER JOIN.

 
LEFT [ OUTER ]OuterJoin2

Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.

 
RIGHT [OUTER]OuterJoin3_Right

Specifies all rows from the right table not meeting the join condition are included in the result set, and output columns that correspond to the other table are set to NULL, in addition to all rows returned by the inner join.

Well, I hope that helps a bit. Of course most queries look like this:

SELECT *
FROM sys.tables t
LEFT OUTER JOIN sys.columns c
	ON t.object_id = c.object_id

So what’s the left and what’s the right? Really it’s up and down correct?

Nope. The missing piece of knowledge here is that T-SQL ignores white space (more than required single spaces, tabs, carriage returns, etc). So when the optimizer looks at the query it sees this:

SELECT * FROM sys.tables t LEFT OUTER JOIN sys.columns c ON t.object_id = c.object_id

Now we have a left and a right. And now the LEFT and RIGHT makes a whole lot more sense.

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,614 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: