Skip site navigation (1) Skip section navigation (2)

Trying to understand why a null "fails" a select

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 (view raw or flat)
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

Responses

pgsql-novice by date

Next:From: Thom BrownDate: 2010-08-09 16:29:02
Subject: Re: Trying to understand why a null "fails" a select
Previous:From: Oliveiros d'Azevedo CristinaDate: 2010-08-09 15:22:47
Subject: Re: reuse a subquery

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group