Re: NULL with IN-Clause

On Fri, 07 May 1999 09:40:24 -0100,
"Gernot" == Gernot <Ewert_Ahr._Electronic_GmbH_at_t-online.de> writes:

Gernot> I've just tried the following SQL-expression:
Gernot> SELECT ...
Gernot> WHERE Value IN (NULL,255,2127)

Gernot> Sybase SQL-Server executed this in a consequent way: Gernot> Testing Value for IS NULL or for any of the values 255, 2127 ...

Gernot> I just wonder whether it is standard SQL to include NULL in an Gernot> IN-Clause?

Gernot> Any hints would be appreciated, since I'd like to remain portable with Gernot> my queries.

I have no idea, but I just tried it in Oracle 7.3.2.3.0, and it accepts ``... WHERE VALUE IN (NULL, 1)''. However, this query returns just the row with VALUE=1, not the row with VALUE=null (which is also present in the test table). ``... WHERE VALUE IN (NULL)'' also fails. It looks like in Oracle, an IN NULL clause never yields any results, regardless of table content. I don't know about the standard, but would argue that such a query is invalid (since meaningless, and difficult to spot), and a parse error should have been raised (which should be easy enough). Cheers,

                                                                      Philip

--
The mail transport agent is not liable for any coffee stains in this message

Philip Lijnzaad, lijnzaad_at_ebi.ac.uk | European Bioinformatics Institute


+44 (0)1223 49 4639                 | Wellcome Trust Genome Campus, Hinxton


+44 (0)1223 49 4468 (fax)           | Cambridgeshire CB10 1SD,  GREAT BRITAIN


PGP fingerprint: E1 03 BF 80 94 61 B6 FC  50 3D 1F 64 40 75 FB 53