On 3 October 2011 08:33, Alexander Pyhalov <alp@xxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
