From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Trying to understand why a null "fails" a select |
Date: | 2010-08-09 16:29:02 |
Message-ID: | AANLkTikUmQ6cSfU68d8ctLcjhLk0SG3+AAd+7sN6Rzwq@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 9 August 2010 17:24, Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com> wrote:
> 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
>
It's NULL black magic. It can't tell if it's not in the subquery
because it contains a NULL, and therefore can't be evaluated against
any of the outer query values.
So is 7 = NULL? We can't say no because we don't know what NULL is.
--
Thom Brown
Registered Linux user: #516935
From | Date | Subject | |
---|---|---|---|
Next Message | Leon Starr | 2010-08-09 18:16:00 | How to access error message text in plpgsql |
Previous Message | Rikard Bosnjakovic | 2010-08-09 16:24:44 | Trying to understand why a null "fails" a select |