From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | Alexander Pyhalov <alp(at)rsu(dot)ru> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: I don't understand something... |
Date: | 2011-10-03 12:18:56 |
Message-ID: | CAF-3MvPgzbF8fvDsCqDocs1u+Q-6RVXMHt05sw0cwmt9fUFxqA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 3 October 2011 08:33, Alexander Pyhalov <alp(at)rsu(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Venkat Balaji | 2011-10-03 12:33:35 | Re: : PostgreSQL Online Backup |
Previous Message | Joe Abbate | 2011-10-03 12:17:27 | Re: PL/Python |