NOT IN vs. OUTER JOIN and NOT NULL

From: Martín Marqués <martin(dot)marques(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: NOT IN vs. OUTER JOIN and NOT NULL
Date: 2010-09-09 11:59:00
Message-ID: AANLkTink6EN+OwmPwAPZWHb-Ogk22vQS8fJAVPK_JhDm@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thom Brown 2010-09-09 12:13:37 Re: NOT IN vs. OUTER JOIN and NOT NULL
Previous Message Phui Hock 2010-09-09 11:13:09 How to inherit search_path from template