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

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

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

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