I faced a very typical question in one of the interview where i was asked whey do we really use Right join, when we can acheive the results by just interchanging the tables.
T1 Left Join T2 -> Would give all records from T1 and matching records from T2
T1 Right Join T2-> Would give all records from T2 and matching records from T1.
So when you want all records from T2, then instead of using the Right join, we can just interchange the tables and use the Left join itself
T2 Left join T1.
Any one who can shed some light on the necessity of this join?:)
You're right, T1 left join T2 equals T2 right join T1.
So why do we need both ? We don't, but it's a commodity, mostly for readability where you use on over the other and sometimes the other way.
It's like those operators : < and > , + and -, * and /
One of them is enough, but still, we're using both depending on what we're writing.
From a functional perspective I've never come across a requirement for a RIGHT join. As was pointed out you can simply swap the order of the tables. In a Teradata environment there isn't a performance difference either.
IMHO, the ANSI standard provides for three types of outer join (left, right and full) and so Teradata supports all three.
I've never coded a right outer join in 'real' code.