pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddifference between EXCEPT and NOT IN?
3+ messages / 2 participants
[nested] [flat]
* difference between EXCEPT and NOT IN?
@ 2008-04-01 15:50 Raphael Bauduin <rblists@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Raphael Bauduin @ 2008-04-01 15:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: difference between EXCEPT and NOT IN?
@ 2008-04-01 16:04 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Raphael Bauduin <rblists@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2008-04-01 16:04 UTC (permalink / raw)
To: Raphael Bauduin <rblists@gmail.com>; +Cc: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: difference between EXCEPT and NOT IN?
@ 2008-04-01 16:43 Raphael Bauduin <rblists@gmail.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 3+ messages in thread
From: Raphael Bauduin @ 2008-04-01 16:43 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-04-01 16:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-01 15:50 difference between EXCEPT and NOT IN? Raphael Bauduin <rblists@gmail.com>
2008-04-01 16:04 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2008-04-01 16:43 ` Raphael Bauduin <rblists@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox