From: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
---|---|
To: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: NOT IN vs. OUTER JOIN and NOT NULL |
Date: | 2010-09-09 16:49:23 |
Message-ID: | F2EDF954-5F68-4FF6-989A-5933CF87CE2D@solfertje.student.utwente.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 9 Sep 2010, at 13:59, Martín Marqués wrote:
> I was looking at rows in a table which are not referenced from another
> and found some discrepencies.
>
> These are the queries (with results):
>
> SELECT * from grupo_concursantes where codigo NOT IN (SELECT grupo
> FROM concursantes);
> codigo | numero | evento | escuela
> --------+--------+--------+---------
> (0 filas)
You're possibly getting bitten by the behaviour of NULL in NOT IN lists. This is an often debated feature of the SQL standard. See for example:
development=> \pset null '(null)'
Null display is "(null)".
development=> SELECT 1 NOT IN (1, 2, 3, 4, NULL);
?column?
----------
f
(1 row)
development=> SELECT 1 NOT IN (2, 3, 4, NULL);
?column?
----------
(null)
(1 row)
development=> SELECT 1 NOT IN (2, 3, 4);
?column?
----------
t
(1 row)
development=> SELECT 1 NOT IN (2, 3, 4, NULL, 1);
?column?
----------
f
(1 row)
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4c89101f10402127211624!
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Richardson | 2010-09-09 17:13:59 | Re: Query help, please |
Previous Message | Merlin Moncure | 2010-09-09 16:25:20 | Re: psql '\copy' command for writing binary data from BYTEA column to file |