What's the difference between on and where

Database
Enthusiast

What's the difference between on and where

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!
4 REPLIES
Enthusiast

Re: What's the difference between on and where


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:

Department Table:
----------------
DNo DName
10 HR
20 Finance
30 Education

Employee Table:
----------------
ENo DNo EName
1 10 abcd
2 20 cdef

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)

OUTPUT:
-------
E.No E.Name D.Name
? ? HR
2 cdef Finance
? ? Education

Select E.No, E.Name,D.Name
from
department_table d left outer join employee_table e
ON D.DNo = E.DNo
WHERE E.ENo =2 (WHERE is evaluated at the last, WHERE is applied after the join is formed)

OUTPUT:
-------
E.No E.Name D.Name
2 cdef Finance

Enthusiast

Re: What's the difference between on and where

Thanks very much!
Enthusiast

Re: What's the difference between on and where

hi,

some more info here:

Check the EXPLAIN plan's output.

In the INNER JOIN, the "WHERE" clause is applied as rows are read, before the ON clause. So, it is recommended that we use a WHERE clause instead of a compound ON condition.

In the OUTER JOIN, the WHERE clause is executed last.

Tip: Use columns from the outer table in the WHERE clause. If you use inner table's columns, it will result in a MERGE JOIN.

Enthusiast

Re: What's the difference between on and where

I got it ,you described it very detailed.