As we know,the two following SQLs can get the same results: select a.* from a inner join b on a.id = b.id where a.name='xxx';
select a.* from a inner join b on a.id = b.id and a.name='xxx';
But,I'd like to know,what's the difference between the tow SQLs?Especially how the DB to deal with them? The and a.name='xxx' OR where a.name='xxx' will filter firstly?It depends on the statistics information or other conditions? Thanks!
Only Outer Joins handle AND and WHERE syntax differently.In all other joins they act in the same manner. The AND is evaluated along with the ON whereas WHERE is evaluated at the end as explained below:
Select E.No, E.Name,D.Name from department_table d left outer join employee_table e ON D.DNo = E.DNo AND E.ENo =2 (AND is evaluated with the ON , hence it considers ENo 2 as the only record present in employee_table)