Re: Question about SELECT statements with subselects

Miloslav Semler wrote:
> I found strange behavior with subselects and I am not able to explain
> it. I have several tables in schema:
>
> tramecky, mt_hodnoty, plata_kusy
>
> in these tables, id is always primary key (serial), table_id is always
> foreign key to table. When I run this query:
>
> select tramecky.id FROM a.tramecky WHERE
> id NOT IN(SELECT tramecky_id FROM a.plata_kusy) AND
> expedicni_plato IS NULL
>
> I get 55 rows.
>
> When I run this query:
>
> select tramecky.id FROM a.tramecky WHERE
> id NOT IN(SELECT tramecky_id FROM a.plata_kusy) AND
> id NOT IN(SELECT tramecky_id FROM a.mt_hodnoty) AND
> expedicni_plato IS NULL
>
> I get no rows.. so I expect that rows with foreign keys tramecky_id of
> 55 rows are present in table mt_hodnoty. However result of query:
>
> select mt_hodnoty.id FROM a.mt_hodnoty WHERE tramecky_id NOT IN(SELECT
> tramecky_id FROM a.plata_kusy)
>
> is empty set. Can anybody explain such strange behavior?

There is probably one or more rows in "mt_hodnoty" where "tramecky_id" is NULL.

Then the subselect
SELECT tramecky_id FROM a.mt_hodnoty
contains a NULL values, and the NOT IN clause will result in NULL,
which is not TRUE, so the result set is empty.

The NULL value does not show up in your second query, because
the condition NULL NOT IN (...) is also always NULL.

Yours,
Laurenz Albe