Intrinsic difference between where clause and join criteria

the where is a limit on the joined set while join criteria limits the source before the join

for example

select a.name,sum(b.sale_amount) amount

from

(select 1 id ,'herby'  name

union  select 2 id ,'Boris'  name

union select 3 id ,'tony'  name)

a

left join

(select 1 id , 34  sale_amount

union  select 2 id ,344  sale_amount

union select 2 id ,14  sale_amount)

b

on a.id = b.id

group by a.name

gives a result

 

if a clause is added to the join as thus

select a.name,sum(b.sale_amount) amount

from

(select 1 id ,'herby'  name

union  select 2 id ,'Boris'  name

union select 3 id ,'tony'  name)

a

left join

(select 1 id , 34  sale_amount

union  select 2 id ,344  sale_amount

union select 2 id ,14  sale_amount)

b

on a.id = b.id  and b.sale_amount > 15

group by a.name

we get

if however  a where clause is added

select a.name,sum(b.sale_amount) amount

from

(select 1 id ,'herby'  name

union  select 2 id ,'Boris'  name

union select 3 id ,'tony'  name)

a

left join

(select 1 id , 34  sale_amount

union  select 2 id ,344  sale_amount

union select 2 id ,14  sale_amount)

b

on a.id = b.id 

where  b.sale_amount > 15

group by a.name

we will now get

once upon a time I fixed this with
where b.sale_amount > 15 or b.sale_amount is null
this is not a good solution because of the following case

select a.name,sum(b.sale_amount) amount

from

(select 1 id ,'herby'  name

union  select 2 id ,'Boris'  name

union select 3 id ,'tony'  name)

a

left join

(select 1 id , 34  sale_amount

union  select 2 id ,344  sale_amount

union select 2 id ,14  sale_amount)

b

on a.id = b.id 

where  b.sale_amount > 50 or b.sale_amount is null

group by a.name

in this case herby gets chopped of because his sale is less than 50

this is the correct solution

select a.name,sum(b.sale_amount) amount

from

(select 1 id ,'herby'  name

union  select 2 id ,'Boris'  name

union select 3 id ,'tony'  name)

a

left join

(select 1 id , 34  sale_amount

union  select 2 id ,344  sale_amount

union select 2 id ,14  sale_amount)

b

on a.id = b.id  and b.sale_amount > 50

group by a.name