Re: difference between EXCEPT and NOT IN? - Mailing list pgsql-sql

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.