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:

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 ]

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 ]

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]

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.