1

Do the following two queries produce the same result?

1.    SELECT * FROM Table a LEFT JOIN Table b ON a.id = b.id;

2.    SELECT * FROM Table b RIGHT JOIN Table a ON a.id = b.id;
William Robertson
  • 15,273
  • 4
  • 38
  • 44
hohoho236
  • 19
  • 2
  • Does this answer your question? [Difference between left join and right join in SQL Server](https://stackoverflow.com/questions/4715677/difference-between-left-join-and-right-join-in-sql-server) – Gabriele Franco Aug 01 '20 at 05:34
  • Yes they are the same. A right join is a left join written backwards to confuse everyone. I avoid them. – William Robertson Aug 01 '20 at 08:06
  • 1
    Just to expand on that, you'd normally read a `from` clause from left to right, e.g. `from customers` - OK this query is about customers - `join orders` - fine, I'm getting the orders for each customer - `join order_details` - got it, we also want the order details for each order. The optimiser may find a different join order, but when we write the query we can present the elements in a logical order to make it understandable. `RIGHT JOIN` violates that principle by forcing us to read a particular join in the opposite direction, which I personally find mildly infuriating. – William Robertson Aug 01 '20 at 09:59

2 Answers2

0

Yes both will provide same result with different sorting order

Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53
0

No, the two are not the same because the columns are in different orders. You are using select *, so the ordering of the columns is based on the order of the tables in from clause.

That said, with two tables, left join and right join are equivalent, with the tables switched and the same join conditions. This does not apply as more joins are added. So, these can be different:

select . . .
from a left join b left join c

versus:

select c right join b right join a

The reason is because of how the joins are grouped, which is always from left-to-right unless you have parentheses.

from (a left join b) left join c

select (c right join b) right join a

If you are interested in the more general situation (and perhaps examples of when they are not the same), then ask a new question. That said, my advice is to always use left joins. In my experience, right join is almost never needed.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786