Re: NOT IN vs. OUTER JOIN and NOT NULL
От | Thom Brown |
---|---|
Тема | Re: NOT IN vs. OUTER JOIN and NOT NULL |
Дата | |
Msg-id | AANLkTi=cOpuPw=1ftz9paef2uiZvpwyKU1sFDztSqkgH@mail.gmail.com обсуждение исходный текст |
Ответ на | NOT IN vs. OUTER JOIN and NOT NULL (Martín Marqués <martin.marques@gmail.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: