Re: Simple Query

From: Tommy Wareing <p0070621_at_brookes.ac.uk>
Date: 6 Feb 1995 15:42:52 GMT
Message-ID: <3h5g1s$2pt_at_cs3.brookes.ac.uk>

In article <3h4aml$k9a_at_peaches.cs.utexas.edu>, Bhalchandra Ghatate says...
>
>
>Now is this a bug: ?
>SQL> select * from junk where name not in ('1','2','');
>
>no rows selected
 

>Same Query with sybase.
>SYBASE:
>1> select * from junk where name not in ('1','2','')
>2> go
> name
> -------------------------
> 3
> 4
>
>3 and 4 present.
>

It's not a bug, but might not be conforming with a standard. Oracle defines the result of (almost) any operation involving NULL to be NULL. so: for name='1'

  name not in ('1', '2', '') expands to

  not (name='1' or name='2' or name=null) which reduces to
  not (TRUE or FALSE or NULL) which reduces to
  not (NULL) which reduces to

  NULL which isn't TRUE.
Nothing gets printed. Same for 2, or 3 or 4. The crucial bit with understanding this is that Oracle uses 3 state logic, TRUE, FALSE or NULL.

I don't know what Sybase does, and I don't know what a standard might say.   

-- 
 _________________________   __________________________________________
/  Tommy Wareing          \ /  I've been looking for an original sin,  \
|  p0070621_at_brookes.ac.uk  X   One with a twist and a bit of a spin    |
\  0865-483389            / \     -- Pandora's Box, Jim Steinman       /
 ~~~~~~~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Received on Mon Feb 06 1995 - 16:42:52 CET