Question about SELECT statements with subselects

From: Miloslav Semler <semler(at)crytur(dot)cz>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Question about SELECT statements with subselects
Date: 2017-09-22 06:03:02
Message-ID: bb430389-0c94-3703-2aae-2acd9f5aa5fa@crytur.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Miloslav Semler 2017-09-22 06:06:44 Question about SELECT statements with subselects
Previous Message Frank Millman 2017-09-22 05:34:55 Re: a JOIN to a VIEW seems slow