Postgres Pro
Downloads
Home   >   mailing lists

Thread: NOT IN vs. OUTER JOIN and NOT NULL

NOT IN vs. OUTER JOIN and NOT NULL

From
Martín Marqués
Date:
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)

SELECT g.* FROM grupo_concursantes g left outer join concursantes c on
(g.codigo=c.grupo)
where c.codigo IS NULL;
 codigo | numero | evento | escuela
--------+--------+--------+---------
     25 |      1 |      1 |   69331
     33 |      2 |      1 |   60233
     53 |      2 |      1 |   60490
     64 |      6 |      1 |   68861
     73 |      1 |      1 |   69220
(5 filas)

Why aren't the 5 rows from the second query in the first?

--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

Re: NOT IN vs. OUTER JOIN and NOT NULL

From
Thom Brown
Date:
2010/9/9 Martín Marqués <martin.marques@gmail.com>:
> 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)
>
> SELECT g.* FROM grupo_concursantes g left outer join concursantes c on
> (g.codigo=c.grupo)
> where c.codigo IS NULL;
>  codigo | numero | evento | escuela
> --------+--------+--------+---------
>     25 |      1 |      1 |   69331
>     33 |      2 |      1 |   60233
>     53 |      2 |      1 |   60490
>     64 |      6 |      1 |   68861
>     73 |      1 |      1 |   69220
> (5 filas)
>
> Why aren't the 5 rows from the second query in the first?

These are doing different things.  The first one is matching rows in
grupo_concursantes where codigo doesn't appear in concursantes.grupo
(and bear in mind, NULL concursantes.grupo values won't match this
evaluation) .  The second one is returning every instance of
concursantes where where they *do* match, but where
concursantes.codigo is NULL, which isn't mentioned in the first query.

I'm wondering that if you used * instead of g.* in the second query
whether you'd get NULLs returned in the grupo column.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Re: NOT IN vs. OUTER JOIN and NOT NULL

From
Alban Hertroys
Date:
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!