On 3 October 2011 08:33, Alexander Pyhalov <alp@rsu.ru> wrote:
> Now we want to select count of all employees who doesn't have any
> subordinates (query 1):
> SELECT count(employee_id) from employees o where not exists (select 1 from
> employees where manager_id=o.employee_id);
> count
> -------
> 89
> (1 row)
>
> We can select count of all managers (query 2):
> SELECT count(employee_id) from employees where employee_id in (select
> manager_id from employees);
> count
> -------
> 18
> (1 row)
>
> But if we reformulate the first query in the same way, answer is different
> (query 3):
> SELECT count(employee_id) from employees where employee_id not in (select
> manager_id from employees) (query 3);
> count
> -------
> 0
> (1 row)
>
> I don't understand why queries 1 and 3 give different results. They seems to
> be the same... Could someone explain the difference?
That's because NOT IN returns NULL if there are any NULLs in the list.
As the WHERE-clause requires something to evaluate to either true or
false (NULL won't do), you (correctly) get false if someone is a
manager, but also if _anyone_ is NOT a manager.
That's an artefact of how 3-valued logic is implemented in the SQL standard.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
