Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

[GENERAL] NOT IN (NULL) ?

0 views
Skip to first unread message

Paul

unread,
Oct 31, 2010, 12:37:28 PM10/31/10
to
Please, help me.
Why the condition
SELECT 5 NOT IN (NULL)
returns NULL, but not FALSE (as I thought)?

--
Paul


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

Tom Lane

unread,
Oct 31, 2010, 12:42:27 PM10/31/10
to
"Paul" <mag...@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

Raymond O'Donnell

unread,
Oct 31, 2010, 12:44:01 PM10/31/10
to
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
r...@iol.ie

Paul

unread,
Oct 31, 2010, 12:53:46 PM10/31/10
to
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

Pavel Stehule

unread,
Oct 31, 2010, 1:08:16 PM10/31/10
to
2010/10/31 Paul <mag...@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

Tom Lane

unread,
Oct 31, 2010, 1:19:14 PM10/31/10
to
"Paul" <mag...@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

0 new messages