Question about SELECT statements with subselects

Miloslav Semler <miloslav.semler@xxxxxxxxx> · Fri, 22 Sep 2017 08:06:44 +0200

Hello,

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?

Thanks in advance,

Miloslav Semler

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general