Thread: IN clause

am  Fri, dem 24.11.2006, um 14:42:30 +0530 mailte surabhi.ahuja folgendes:
> Hi,
>
> i have a table
> and i have the query select * from table where col_name is null;
>
> it returns some rows
>
> now, say i have to implement the same query using the in clause how shold it be
> done?
>
> select * from table where col_name in (null);

select * from table where col_name is null or col_name in (...);

Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

surabhi.ahuja wrote:
> That is fine
> but what I was actually expecting is this
> if
> select * from table where col_name in (null, 'a', 'b');
>
> to return those rows where col_name is null or if it = a or if it is = b
>
> But i think in does not not support null queries , am i right?

Expressions comparing NULL usually result in NULL, and not in true or
false. That's why there are special operators on NULL, like IS and
COALESCE().

The "problem" is that the WHERE clause interprets a NULL value similar
to false (as per the SQL spec). There's some interesting literature
about this, for example by C.J.Date.

As an example,
    NULL = NULL and NULL IS NULL;
have two different results (NULL and true respectively). You'll also
find that concatenation
    'a' || NULL
results in NULL.

The same goes for IN (...).

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

On Fri, Nov 24, 2006 at 05:31:07PM +0530, surabhi.ahuja wrote:
> That is fine
> but what I was actually expecting is this
> if
> select * from table where col_name in (null, 'a', 'b');
>
> to return those rows where col_name is null or if it = a or if it is = b
>
> But i think in does not not support null queries , am i right?

You'll need to check the standard, but IN() treats NULL specially, I
think it returns NULL if any of the elements is null, or something like
that. It certainly doesn't work the way you think it does.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Hasn't it been said enough?  Don't allow NULLs in your database.
Databases are for storing data, not a lack of it.  The only time NULL
should appear is during outer joins.

--
Brandon Aiken
CS/IT Systems Engineer
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Martijn van
Oosterhout
Sent: Friday, November 24, 2006 7:20 AM
To: surabhi.ahuja
Cc: A. Kretschmer; pgsql-general@postgresql.org
Subject: Re: [GENERAL] IN clause

On Fri, Nov 24, 2006 at 05:31:07PM +0530, surabhi.ahuja wrote:
> That is fine
> but what I was actually expecting is this
> if
> select * from table where col_name in (null, 'a', 'b');
>
> to return those rows where col_name is null or if it = a or if it is =
b
>
> But i think in does not not support null queries , am i right?

You'll need to check the standard, but IN() treats NULL specially, I
think it returns NULL if any of the elements is null, or something like
that. It certainly doesn't work the way you think it does.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability
to litigate.

I see we have a C J Date fan on the list! ;-)

There is one other case where I personally find nullable
columns a good thing: process_me ish flags. When a row
is not supposed to be processed that field is null and
when a field is null it wont be in the index [at least
on Oracle].

Best regards,
Marcus

Brandon Aiken skrev:
> Hasn't it been said enough?  Don't allow NULLs in your database.
> Databases are for storing data, not a lack of it.  The only time NULL
> should appear is during outer joins.
>
> --
> Brandon Aiken
> CS/IT Systems Engineer
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Martijn van
> Oosterhout
> Sent: Friday, November 24, 2006 7:20 AM
> To: surabhi.ahuja
> Cc: A. Kretschmer; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] IN clause
>
> On Fri, Nov 24, 2006 at 05:31:07PM +0530, surabhi.ahuja wrote:
>> That is fine
>> but what I was actually expecting is this
>> if
>> select * from table where col_name in (null, 'a', 'b');
>>
>> to return those rows where col_name is null or if it = a or if it is =
> b
>>
>> But i think in does not not support null queries , am i right?
>
> You'll need to check the standard, but IN() treats NULL specially, I
> think it returns NULL if any of the elements is null, or something like
> that. It certainly doesn't work the way you think it does.
>
> Have a nice day,

> That is fine
> but what I was actually expecting is this
> if
> select * from table where col_name in (null, 'a', 'b');
>
> to return those rows where col_name is null or if it = a or if it is = b
>
> But i think in does not not support null queries , am i right?
>

that is correct: if col_name was actually 'a' then you would get:

'a' in ( null, 'a', 'b', ...) works the same as:

'a' = null  ~ resolves to Unknown
or
'a' = 'a'   ~ resovles to true
or
'a' = 'b'   ~ resovles to false
or
...

so you end up with:
(unknown or true or false) = true
but if you have
(unknown or false or false) = false

Regards,

Richard Broersma Jr.

On fös, 2006-11-24 at 10:10 -0800, Richard Broersma Jr wrote:
> > That is fine
> > but what I was actually expecting is this
> > if
> > select * from table where col_name in (null, 'a', 'b');
> >
> > to return those rows where col_name is null or if it = a or if it is = b
> >
> > But i think in does not not support null queries , am i right?
> >
>
> that is correct: if col_name was actually 'a' then you would get:
>
> 'a' in ( null, 'a', 'b', ...) works the same as:
>
> 'a' = null  ~ resolves to Unknown
> or
> 'a' = 'a'   ~ resovles to true
> or
> 'a' = 'b'   ~ resovles to false
> or
> ...
>
> so you end up with:
> (unknown or true or false) = true
> but if you have
> (unknown or false or false) = false

yes, except I think you meant:
(unknown or false or false) = unknown

as can be demonstrated by:

test=# \pset null 'null'
Null display is "null".
test=# select (null or true);
 ?column?
----------
 t
(1 row)

test=# select (null or false);
 ?column?
----------
 null
(1 row)


and indeed the IN operator does behave this way:

test=# select 'a' in (null,'a');
 ?column?
----------
 t
(1 row)

test=# select 'a' in (null,'b');
 ?column?
----------
 null
(1 row)

test=# select 'a' in ('a','b');
 ?column?
----------
 t
(1 row)

test=# select 'a' in ('b','c');
 ?column?
----------
 f
(1 row)


and finally: NULL IN (NULL,'b') will return NULL
because it will translate to
(NULL = NULL) or (NULL = 'b')

test=# select null in (null,'b');
 ?column?
----------
 null
(1 row)

> yes, except I think you meant:
> (unknown or false or false) = unknown
>
> as can be demonstrated by:
>
> test=# \pset null 'null'
> Null display is "null".
> test=# select (null or true);
>  ?column?
> ----------
>  t
> (1 row)
>
> test=# select (null or false);
>  ?column?
> ----------
>  null
> (1 row)

Thanks for the clearification.  I was totally wrong on that point. :-)

Regards,

Richard Broersma Jr.

On Nov 24, 2006, at 9:04 AM, Marcus Engene wrote:
> There is one other case where I personally find nullable
> columns a good thing: process_me ish flags. When a row
> is not supposed to be processed that field is null and
> when a field is null it wont be in the index [at least
> on Oracle].

Actually, that's abuse of NULL. NULL is supposed to mean "I don't know".

In any case, like Oracle, PostgreSQL does not index NULL values (at
least not in btree).
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

On Mon, Nov 27, 2006 at 05:24:31PM -0600, Jim Nasby wrote:
> In any case, like Oracle, PostgreSQL does not index NULL values (at
> least not in btree).

Actually, PostgreSQL does store NULL values in an index, otherwise you
could never use them for full index scans (think multicolumn indexes).
You can't use the index for IS NULL tests, although patches exist for
that.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.