Vishal Pandya wrote:
Now if I do something like,
then it would consider all the records of B while applying the join but I want only few records to be considered for join means I have to apply filter before join occurs.
and not mention the difference between,
Why would you want to prefer one approach over the other...
As far as i understand, both these queries will get the same result...and will be of the same cost to the DB....!!!
Yes....you can verify it by running Explain Plan on the query.....
Today DBs dont execute the SQL in the same way as your SQL.....DBs can choose to modify the query to appropriate one(which will be better performing and will be effectively be same.)..
Also you would be mistaken if you think "A a left outer join a.b " to "consider all the records of B while applying the join "....
It will not.....The DB does not join the tables initially....but instead filters the row using the where clause, after that i starts to Join the tables.....so that both the above ways are equal performing in terms of cost to the DB...
The bottom line is dont try to second guess as to what exactly the DB will be doing in this case...and both the queries are equally valid.....and have the same cost for the DB.....although given a choice i would prefer to use the second one as it is cleaner and does not include an inner select.....