Re: NOT IN vs. OUTER JOIN and NOT NULL

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!