ainpoissee wrote:

>
> then I do get the answer I expect, but as the date is parameter I
> would like to keep this condition in WHERE.
>

Conditions that apply to the right hand tables in left joins are
better left in the JOIN ON clause. If you feel you really must
put them in the WHERE clause, you must allow null values for the
referenced fields.

Short form - you want to find employees who don't work for
Voldemort. If you write it like this, you won't find employees
with no department because the WHERE clause is applied to the
output after the join, and employees without a department will
have a null d.manager and null <> 'Voldemort' returns false.


select e.name
from employees e
left join departments d on e.dept_id = d.dept_id
where e.age > 20
and d.manager <> 'Voldemort'

This works:

select e.name
from employees e
left join departments d
on e.dept_id = d.dept_id and d.manager <> 'Voldemort'
where e.age > 20
and d.manager <> 'Voldemort'


So does this

select e.name
from employees e
left join departments d on e.dept_id = d.dept_id
where e.age > 20
and (d.manager <> 'Voldemort'
or d.manager is null)


Regards,


Ann