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.
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.