sql - what is the difference between using filter condition in WHERE clause and JOIN condition -

say have following join query of join type (inner/left/right/full)

select e.id, d.name, e.sal emp e inner/left/right/full join dep d on e.id = d.id , e.sal > 100 

also, have similar query condition

select e.id, d.name, e.sal emp e inner/left/right/full join dep d on e.id = d.id e.sal > 100 

the difference in above query using e.sal > 100 in join , conditions.

in case above query result differently ? or both same ?

with inner join, there no difference, either in terms of performance or definition of result set. there difference outer joins.

the where clause filters results of from after from evaluated. so, consider these 2 queries:

select e.id, d.name, e.sal emp e left join      dep d      on e.id = d.id e.sal > 100;  select e.id, d.name, e.sal emp e left join      dep d      on e.id = d.id , e.sal > 100; 

the first filters emp table appropriate ids. second does not filter emp table. why not? well, definition of left join says take rows in first table, regardless of whether on finds matching record in second table. so, there no filtering.

now, consider version:

select e.id, d.name, e.sal emp e right join      dep d      on e.id = d.id e.sal > 100;  select e.id, d.name, e.sal emp e right join      dep d      on e.id = d.id , e.sal > 100; 

the first turns right join inner join. why? because non-matching rows, e.sal null , fails where. second version keeps departments, have no employees matching condition. note: (and others) prefer left outer joins right outer joins in terms of following logic. logic left outer join simple: keep rows in first table.

the full outer join combines these 2 situations.