Are "From Table1 Left Join Table2" and "From Table2 Right Join Table1" Interchangeable

Are from Table1 left join Table2 and from Table2 right join Table1 interchangeable?

Select * from Table1 left join Table2 ...

and

Select * from Table2 right join Table1 ...

are indeed completely interchangeable. Try however Table2 left join Table1 (or its identical pair, Table1 right join Table2) to see a difference. This query should give you more rows, since Table2 contains a row with an id which is not present in Table1.

What is the difference between left join (table1, table2) and left join table1 left join table2

Lets have this data:

A       B        C
id      uid      uid
--      ---      ---
 1        1        2
 2

---

First, the second query:

select * from a
left join b on a.id = b.uid
left join c on a.id = c.uid

 ID UID  UID
 -- ---- ----
  1    1 NULL
  2 NULL    2

This should come as no surprise - second column is joined from b and where there's no data in b, NULL is used (outer join); third column behaves the same, just for c.

---

The first query, rewritten with CROSS JOIN (which it is equivalent to) to be ANSI-compliant:

select * from a
left join (b CROSS JOIN c)
on a.id = b.uid and a.id = c.uid

 ID UID  UID
 -- ---- ----
  2 NULL NULL
  1 NULL NULL

Why there are all NULLs?

First, the CROSS JOIN is performed, but that results in a resultset with just one row:

b.UID   c.UID
-----   -----
    1       2

Then, the left join is performed, but there's no row in the result of the cross join that would have same uid for both b and c, so no row can be matched for either row in a.

If we can interchange left or right join then what is the use of any specific type of join?

I think this is actually a good question. I have never heard a really good reason why right join exists. Maybe something from the very old days of SQL required it? It would be really cool is someone had an answer other than appearance.

I like the comment by Gordon.

1 - 1 is the same as 1 + (-1)

To expand on the idea, I have always felt it was for looks. I always use left joins and order the tables in my query to achieve the results I want but could easily use right joins or a mix.

For example

Select a.field, b.field
   From a
   left join b on a.id = b.aid

will return the same results as:

Select a.field, b.field
   From b
   right join a on a.id = b.aid

What it comes down to is that the first table is the left table and the next table is the right table. If you want all the records from a it has to be first in a left join and has to be second in a right join.

Example where a right join cannot be converted to left join?

Although you can always convert between right joins and left joins, the parsing of the SQL language can make this tricky in some cases. For just two tables, the two forms are interchangeable. However, for more tables, SQL parsing rules affect the outcome. SQL parses the from clause from left to right, so it implicitly puts in parentheses.

For instance, it is tempting to say:

from a right join b right join c

(This is shorthand for from a right join b on . . . right join c on . . . . I'm leaving out the on clauses, assuming they are in the customary places immediately following the joins.)

is the same as

from c left join b left join a

But, this is not strictly true. One obvious difference is that the first could represent a join condition between c and a. The second has trouble if that is the only condition.

The first is parsed as:

from (a right join b) right join c

This means that the first keeps all rows in b that don't match to a . . . and then all rows in c even if there are no matches in b.

The second:

from (c left join b) left join a

This means keep all rows in c, regardless of matches in b and a.

One difference is a row in a that matches c but not b. In the left join version, the values of the a columns will come from a. In the right join version, the values will be NULL. Of course, this depends on the on clauses; the point is that there are subtle differences.

I don't like to ponder such things. So, I basically never use right join. Left join follows the parsing rules of SQL and it has a simple interpretation: keep all rows in the first table regardless of matches in any of the others.

right join versus left join

Codeproject has this image which explains the simple basics of SQL joins, taken from: http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx SQL joins explained

right join equal to left join?

Yes this is right, you can find more information if you searched:

Difference between left join and right join in SQL Server

---

Related Topics