All Forums Database
dxb351880135 7 posts Joined 02/14
05 Mar 2014
my confusion about LEFT JOIN

table A(col1)
1
2
3
5
 
table B(col2)
2
3
4
6
SQL: SEL a.* FROM tableA a left join tableB b On a,col1=b.col1 Where a.col1<5 and b.col1<5
ANSWER : 2
                  3
Who can tell me why?
 
 

Tags:
Santanu84 122 posts Joined 04/13
05 Mar 2014

Hi
Optimizer will make the final decision. My understanding is probably optimizer is going for merge inner join. Try running explain and see the steps. You can try the below query if you want the left join result.
 

SEL a.C1

FROM tableA a

 left join 

 (SEL C1 FROM tableB  WHERE C1 < 5) B

 On a.c1= b.c1 

 Where a.c1<5 

 

Thanking You

Santanu

dnoeth 4628 posts Joined 11/04
05 Mar 2014

A WHERE-condition on the inner table changes the result of the Outer join to an Inner join. The optimizer is smart enough to know that and when you check explain there's no outer join.
There's a good article about that in the manuals:
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/SQL_Reference/B035_1146_111A/ch02.033.001.html#609028
It's a rewrite from an article in the old Teradata Magazine called "A lesson on outer joins, learned the hard way" :-) 

Dieter

dxb351880135 7 posts Joined 02/14
05 Mar 2014

thank you all, I  need  more time to gauge it.
 

karthikcsekar 17 posts Joined 02/14
05 Mar 2014

From a pure coding perspective, I think this is the best rule to follow. 
When we do table A Left Join Table B - 
All conditions on table A should be in 'Where' Clause - This is done since we want to eliminate the rows which fail to satisfy the condition from the base table
All conditions on table B should be in 'On' Clause - This is done since we do not want to eliminate the row but we want to just eliminate the value in the row. 

CK

You must sign in to leave a comment.