From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
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 12:13:37 |
Message-ID: | AANLkTi=cOpuPw=1ftz9paef2uiZvpwyKU1sFDztSqkgH@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2010/9/9 Martín Marqués <martin(dot)marques(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Szymon Guz | 2010-09-09 13:44:56 | Re: Regular expression in an if-statement will not work |
Previous Message | Martín Marqués | 2010-09-09 11:59:00 | NOT IN vs. OUTER JOIN and NOT NULL |