From: | Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Trying to understand why a null "fails" a select |
Date: | 2010-08-09 16:24:44 |
Message-ID: | AANLkTinwsE4StGf3r-P5UCXWyP=PymJxB-AMAZtycDY+@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Premises:
ecdb=> select distinct datasheet from components;
datasheet
-----------
6
3
4
5
(5 rows)
(There's a null on the first line)
ecdb=> select distinct datasheet from components where datasheet is not null;
datasheet
-----------
6
3
4
5
(4 rows)
Now:
ecdb=> select * from datasheets where id not in (select distinct
datasheet from components where datasheet is not null);
id | filename | filesize | md5
| uploaded | uploaded_by
----+----------------------------------+----------+----------------------------------+-------------------------------+-------------
7 | 3128869683212154485514496389.png | 10187 |
b787eba58db5ce84b5dd8d06380c6ec6 | 2010-08-09 18:17:58.048666+02 |
1
(1 row)
This is as expected, but I'm curious why this won't work:
ecdb=> select * from datasheets where id not in (select distinct
datasheet from components);
id | filename | filesize | md5 | uploaded | uploaded_by
----+----------+----------+-----+----------+-------------
(0 rows)
That is, when a null is returned from the sub-query, the select will
not show the row with id 7.
Why?
--
- Rikard
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2010-08-09 16:29:02 | Re: Trying to understand why a null "fails" a select |
Previous Message | Oliveiros d'Azevedo Cristina | 2010-08-09 15:22:47 | Re: reuse a subquery |