Question about SELECT statements with subselects

From: Miloslav Semler <miloslav(dot)semler(at)crytur(dot)cz>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Question about SELECT statements with subselects
Date: 2017-09-22 06:06:44
Message-ID: cdbb06f5-cedb-8654-4c83-4a48fa49f12f@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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2017-09-22 07:32:59 Re: Question about SELECT statements with subselects
Previous Message Miloslav Semler 2017-09-22 06:03:02 Question about SELECT statements with subselects