Re: [GENERAL] Question about SELECT statements with subselects

2017-09-22 Thread Miloslav Semler 
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

[GENERAL] Question about SELECT statements with subselects

2017-09-22 Thread Miloslav Semler 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Question about SELECT statements with subselects

2017-09-22 Thread Albe Laurenz 
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

[GENERAL] Question about SELECT statements with subselects

2017-09-22 Thread Miloslav Semler 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general