Обсуждение: Trying to understand why a null "fails" a select

Поиск
Список
Период
Сортировка

Trying to understand why a null "fails" a select

От
Rikard Bosnjakovic
Дата:
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

Re: Trying to understand why a null "fails" a select

От
Thom Brown
Дата:
On 9 August 2010 17:24, Rikard Bosnjakovic <rikard.bosnjakovic@gmail.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