NOT IN vs. OUTER JOIN and NOT NULL

[Martín Marqués <martin.marques@xxxxxxxxx>]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general