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
Who can tell me why?
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.
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:
It's a rewrite from an article in the old Teradata Magazine called "A lesson on outer joins, learned the hard way" :-)
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.