This is the last posting in my brief discussion of SQL tricks. I’m dealing here with an issue which causes quite a bit of confusion to newer developers. What’s the point in having LEFT and RIGHT joins defined in the SQL standard, how do you decide which to use, and is there a way to keep things simple?

The two most common types of SQL join are INNER and OUTER. The words “left” and “right” are not used with inner joins. This is because the joined tables are both treated in the same way. Rows will not appear in the result set except where the join condition/s are satisfied.

In an OUTER join tables are treated differently. You will see all rows from the “outer” table, plus any rows from the inner table satisfying the join condition. In a row where no inner table match is found, its columns are padded with Nulls. The purpose of the “LEFT” or “RIGHT” in the join is to indicate which one is the outer table.

Take the following join expression:“TableA LEFT OUTER JOIN TableB”. The words “left outer” indicate that the outer table is TableA, since it is named to the left of the keyword “JOIN”. So that’s the table that will be returned in full. Rows from Table B will only return where they match the join condition. Can a left outer join be rewritten as a right outer join? Yes, where the entire query includes only two tables, there is no difference. You’ll very rarely see a RIGHT OUTER JOIN for simple queries though. It doesn’t do anything more than a left join, and since most people find the left join much more readable there’s no need for the right join.

Where an query involves more than two tables a case is sometimes made for using right outer joins.

There is an exhaustive discussion of the issue here (and this should also be read in conjunction with it). I can’t add much to what’s said, but I wanted to point up the alternative to the RIGHT join that I prefer, namely the nested join. Please note that in SQL Server you can’t use the bracketed syntax as in the blog, so to make clear what’s going on I comment and indent it as follows:

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
–nested join–
JOIN TableC ON TableB.Col1 = TableC.Col1
ON TableA.Col1 = TableB.Col1

If you don’t like the nested join the alternatives are:

• Derived tables (also known as inline views)
• CTEs (Common Table Expressions, also known as “With Clauses”)

…or if you don’t require your query to be self-contained, you could create a view or function to resolve the inner join for tables B and C, which can then be left outer joined to TableA, or even use a table variable or temporary table.

In most cases I want to avoid dependencies so I opt for one of the self-contained approaches. In the past I’ve always used derived tables. but nowadays I prefer the nested join. It seems to go better with my coding style since my column lists are vertical with each name on its own line. If you use a derived table the select list has to be repeated, which means quite a bulky expression.

I hope these three articles will be helpful in writing simpler SQL.