LEFT vs RIGHT?

Is there a difference? A circumstance that dictates a LEFT join over a RIGHT join? Or can all LEFT’s be rewritten as RIGHT’s?

The difference is which table will always return values.

Say you have the following data:


Table1 (rowId, fkid, rowvalue)
1, 1, "Table1 Row1"
2, 1, "Table1 Row2"
3, 2, "Table1 Row3"
4, 2, "Table1 Row4"
5, 3, "Table1 Row5"
6, 3, "Table1 Row6"
7, 4, "Table1 Row7"
8, 4, "Table1 Row8"
9, 5, "Table1 Row9"
10, 5, "Table1 Row10"
 
Table2 (rowId, rowvalue)
1, "Table2 Row1"
2, "Table2 Row2"
3, "Table2 Row3"
4, "Table2 Row4"
5, "Table2 Row5"
6, "Table2 Row6"
7, "Table2 Row7"
8, "Table2 Row8"

this query


SELECT t1.rowValue AS t1Value
     , t2.rowValue AS t2Value
  FROM Table1 t1
  JOIN Table2 t2 ON t1.fkid = t2.rowId

returns this:


t1Value         t2value
"Table1 Row1"   "Table2 Row1"
"Table1 Row2"   "Table2 Row1"
"Table1 Row3"   "Table2 Row2"
"Table1 Row4"   "Table2 Row2"
"Table1 Row5"   "Table2 Row3"
"Table1 Row6"   "Table2 Row3"
"Table1 Row7"   "Table2 Row4"
"Table1 Row8"   "Table2 Row4"

and this


SELECT t1.rowValue AS t1Value
     , t2.rowValue AS t2Value
  FROM Table1 t1
  LEFT JOIN Table2 t2 ON t1.fkid = t2.rowId

will return this (notice the last two rows):


t1Value         t2value
"Table1 Row1"   "Table2 Row1"
"Table1 Row2"   "Table2 Row1"
"Table1 Row3"   "Table2 Row2"
"Table1 Row4"   "Table2 Row2"
"Table1 Row5"   "Table2 Row3"
"Table1 Row6"   "Table2 Row3"
"Table1 Row7"   "Table2 Row4"
"Table1 Row8"   "Table2 Row4"
"Table1 Row9"   NULL
"Table1 Row10"  NULL

and finally this


SELECT t1.rowValue AS t1Value
     , t2.rowValue AS t2Value
  FROM Table1 t1
 RIGHT JOIN Table2 t2 ON t1.fkid = t2.rowId

will return (again, notice the rows at the end)


t1Value         t2value
"Table1 Row1"   "Table2 Row1"
"Table1 Row2"   "Table2 Row1"
"Table1 Row3"   "Table2 Row2"
"Table1 Row4"   "Table2 Row2"
"Table1 Row5"   "Table2 Row3"
"Table1 Row6"   "Table2 Row3"
"Table1 Row7"   "Table2 Row4"
"Table1 Row8"   "Table2 Row4"
NULL            "Table2 Row5"
NULL            "Table2 Row6"
NULL            "Table2 Row7"
NULL            "Table2 Row8"

Hope that helps…

I’m aware of the difference - but there is no difference between t1 LEFT JOIN t2 and t2 RIGHT JOIN t1.

The question was more to the effect of ‘Is there any circumstance which necessitates a LEFT instead of a right, or are they -always- completely interchangable just in reversing the table order’

That left joinn should give the exact same result as the following right join.


SELECT t1.rowValue AS t1Value
     , t2.rowValue AS t2Value
  FROM Table2 t2
  RIGHT JOIN Table1 t1 ON t1.fkid = t2.rowId

what you should do that is important, is to pick one or the other and don’t mix them because it is easy to mix up which table is being joined in which direction. This is especially true as your queries get more complex.

my suggestion is to use LEFT or RIGHT depending on the direction you read text.

This link here (Coding Horror: A Visual Explanation of SQL Joins) may help as you can visualise it.

All very good posts, though noone actually answered the question, i’m going to assume the implied answer is “No, there is no situation in which you must use one instead of the other”.

Since

TABLE1 LEFT JOIN TABLE2

and

TABLE2 RIGHT JOIN TABLE1

are equivalent to one another and just involve switching which side of the join you specify which table there is no situation where one cannot be translated into the other. So if you want to only use left joins and never use right joins (or vice versa) you can.