Thread: difference between EXCEPT and NOT IN?
Hi, The 2 following statements don't give the same result. I expected the second ti give the exact same result as the first one. What am I missing? development=> SELECT id FROM entrees except select entree_id from postes ORDER BY id desc; id ------3651 (1 row) development=> SELECT id FROM entrees WHERE id not in (select entree_id from postes) ORDER BY id desc;id ---- (0 rows) thanks in advance for the help. Raph -- Web database: http://www.myowndb.com Free Software Developers Meeting: http://www.fosdem.org
On Tue, 1 Apr 2008, Raphael Bauduin wrote: > The 2 following statements don't give the same result. I expected the > second ti give the exact same result as the first one. If any entree_id can be NULL they aren't defined to give the same result. EXCEPT is defined in terms of duplicates based on distinctness, and for example (1 is distinct from 1) is false, (1 is distinct from NULL) is true and (NULL is distinct from NULL) if false. NOT IN is defined in terms of equality, and for example, (1=1) is true, (1=NULL) is unknown and (NULL=NULL) is unknown.
On Tue, Apr 1, 2008 at 6:04 PM, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > On Tue, 1 Apr 2008, Raphael Bauduin wrote: > > > The 2 following statements don't give the same result. I expected the > > second ti give the exact same result as the first one. > > If any entree_id can be NULL they aren't defined to give the same result. > > EXCEPT is defined in terms of duplicates based on distinctness, and for > example (1 is distinct from 1) is false, (1 is distinct from NULL) is true > and (NULL is distinct from NULL) if false. > > NOT IN is defined in terms of equality, and for example, (1=1) is true, > (1=NULL) is unknown and (NULL=NULL) is unknown. > My problem came from 2 entries in the table postes that had an entree_id NULL Thanks for your fast answer, it has helped me spot the problem! Raph -- Web database: http://www.myowndb.com Free Software Developers Meeting: http://www.fosdem.org