my confusion about LEFT JOIN

Database

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 (1)
4 REPLIES
Enthusiast

Re: my confusion about LEFT JOIN

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

Senior Apprentice

Re: my confusion about LEFT JOIN

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#60...

It's a rewrite from an article in the old Teradata Magazine called "A lesson on outer joins, learned the hard way" :-) 

Re: my confusion about LEFT JOIN

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

Enthusiast

Re: my confusion about LEFT JOIN

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.