Is RIGHT JOIN Ever Required?

Answer 1

You can always swap the table order to turn a RIGHT JOIN into a LEFT JOIN. Sometimes it's just more efficient to do it one way or the other.

Answer 2

You can always re-write them to get the same result set. However, sometimes the execution plan may be different in significant ways (performance) and sometimes a right join let's you express the query in a way that makes more sense.

Let me illustrate the performance difference. Programmers tend to think in terms of an sql statement happening all at once. However, it's useful to keep a mental model that complicated queries happen in a series of steps where tables are typically joined in the order listed. So you may have a query like this:

SELECT * /* example: don't care what's returned */
FROM LargeTable L
LEFT JOIN MediumTable M ON M.L_ID=L.ID
LEFT JOIN SmallTable S ON S.M_ID=M.ID
WHERE ...

The server will normally start by applying anything it can from the WHERE clause to the first table listed (LargeTable, in this case), to reduce what it needs to load into memory. Then it will join the next table (MediumTable), and then the one after that (SmallTable), and so on.

What we want to do is use a strategy that accounts for the expected impact of each joined table on the results. In general you want to keep the result set as small as possible for as long as possible. Apply that principle to the example query above, and we see it's obviously much slower than it needs to be. It starts with the larger sets (tables) and works down. We want to begin with the smaller sets and work up. That means using SmallTable first, and the way to do that is via a RIGHT JOIN.

Another key here is that the server usually can't know which rows from SmallTable will be needed until the join is completed. Therefore it only matters if SmallTable is so much smaller than LargeTable that loading the entire SmallTable into memory is cheaper than whatever you would start with from LargeTable (which, being a large table, is probably well-indexed and probably filters on a field or three in the where clause).

It's important to also point out that in the vast majority of cases the optimizer will look at this and handle things in the most efficient way possible, and most of the time the optimizer is going to do a better job at this than you could.

But the optimizer isn't perfect. Sometimes you need to help it along: especially if one or more of your "tables" is a view (perhaps into a linked server!) or a nested select statement, for example. A nested sub-query is also a good case of where you might want to use a right join for expressive reasons: it lets you move the nested portion of the query around so you can group things better.

Answer 3

It's a bit like asking if using greater-than is ever required. Use the one that better fits the task at hand.

Answer 4

Yes! all the time! (Have to admit, mostly used when you're strict as to which table you want to call first)

On this subject: here's a nice visual guide on joins.

/mp

Answer 5

You can always use only left Joins...

SELECT * FROM t1
    LEFT JOIN t2 ON t1.k2 = t2.k2
    RIGHT JOIN t3 ON t3.k3 = t2.k3

is equivilent to:

Select * From t3 
   Left Join (t1 Left Join t2 
                  On t2.k2 = t1.k2)
      On T2.k3 = T3.K3

In general I always try to use only Left Joins, as the table on the left in a Left Join is the one whose rows are ALL included in the output, and I like to think of it, (The Left side) as the "base" set I am performing the cartesion product (join) against ... So I like to have it first in the SQL...

Answer 6

I use LEFT JOINs about 99.999% of the time, but some of my dynamic code generation uses RIGHT JOINs which mean that the stuff outside the join doesn't need to be reversed.

I'd also like to add that the specific example you give I believe produces a cross join, and that is probably not your intention or even a good design.

i.e. I think it's effectively the same as:

SELECT *
FROM t1
CROSS JOIN t3
LEFT JOIN t2
    ON t1.k2 = t2.k2
    AND t3.k3 = t2.k3

And also, because it's a cross join, there's not a lot the optimizer is going to be able to do.

Answer 7

There are many elements of many programming languages which are not strictly required to achieve the correct results but which permit one a) to express intent more clearly b) to boost performance. Examples include numbers, characters, loops, switches, classes, joins, types, filters, and thousands more.