Postgres Pro
Downloads
Home   >   mailing lists

Thread: NOT IN (NULL) ?

NOT IN (NULL) ?

From
"Paul"
Date:
Please, help me.
Why the condition
SELECT 5 NOT IN (NULL)
returns NULL, but not FALSE (as I thought)?

--
Paul

Re: NOT IN (NULL) ?

From
Tom Lane
Date:
"Paul" <magamos@mail.ru> writes:
> Why the condition
> SELECT 5 NOT IN (NULL)
> returns NULL, but not FALSE (as I thought)?

Because the SQL standard says so.

If you think of NULL as meaning "unknown", it makes some intuitive
sense: it's unknown whether that unknown value is equal to 5.

            regards, tom lane

Re: NOT IN (NULL) ?

From
Raymond O'Donnell
Date:
On 31/10/2010 16:37, Paul wrote:
> Please, help me.
> Why the condition
> SELECT 5 NOT IN (NULL)
> returns NULL, but not FALSE (as I thought)?

Because NULL basically means "don't know" - so you don't know whether 5
is there or not.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: NOT IN (NULL) ?

From
"Paul"
Date:
Tom,

Sunday, October 31, 2010, 9:42:27 PM, you wrote:

TL> Because the SQL standard says so.

But  there  is  not  such  thing  in PostgreSQL as empty set as "IN ()" that must be
false, because nothing element may be found in empty set.
And  I  thought that instead of "IN ()" I could use "IN (NULL)", but I
was failed and result was NULL and not FALSE. :(

--
Paul

Re: NOT IN (NULL) ?

From
Pavel Stehule
Date:
2010/10/31 Paul <magamos@mail.ru>:
> Tom,
>
> Sunday, October 31, 2010, 9:42:27 PM, you wrote:
>
> TL> Because the SQL standard says so.
>
> But  there  is  not  such  thing  in PostgreSQL as empty set as "IN ()" that must be
> false, because nothing element may be found in empty set.
> And  I  thought that instead of "IN ()" I could use "IN (NULL)", but I
> was failed and result was NULL and not FALSE. :(
>

(NULL) isn't empty set.  Empty set can be

(SELECT 1 WHERE false)

Regards

Pavel
> --
> Paul
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: NOT IN (NULL) ?

From
Tom Lane
Date:
"Paul" <magamos@mail.ru> writes:
> But  there  is  not  such  thing  in PostgreSQL as empty set as "IN ()" that must be
> false, because nothing element may be found in empty set.
> And  I  thought that instead of "IN ()" I could use "IN (NULL)", but I
> was failed and result was NULL and not FALSE. :(

NULL is not an alternative spelling for an empty set.

You could get an empty IN set by using a sub-select yielding no rows,
for example

regression=# select 1 in (select 1 where false);
 ?column?
----------
 f
(1 row)

regression=# select 1 not in (select 1 where false);
 ?column?
----------
 t
(1 row)

            regards, tom lane