Re: Question about SELECT statements with subselects

Thank you very much. You have been right. Adding tramecky_id IS NOT NULL
solved the problem.

Cheers,

Miloslav
Dne 22.9.2017 v 09:32 Albe Laurenz napsal(a):
> 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

--
Technolog
Crytur, spol. s r.o.
Palackého 175
51101 Turnov