Join

Database
Enthusiast

Join

Hi Experts,

 

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.

 

For Example:

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?:)

 

Thanks

Sunny

2 REPLIES
Highlighted
Teradata Employee

Re: Join

Hi johnsunny,

 

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.

Senior Apprentice

Re: Join

Hi,

 

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.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com