Re: NOT IN (NULL) ?

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

--
Paul

"Paul" <magamos(at)mail(dot)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

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(at)iol(dot)ie

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

2010/10/31 Paul <magamos(at)mail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

"Paul" <magamos(at)mail(dot)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