Обсуждение: [GENERAL] Question about SELECT statements with subselects
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_idFROM 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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