Re: confused on "where x=null"...

On Mar 2, 6:49 pm, Mark Harrison <m..._at_pixar.com> wrote:
> Of course, the proper form is "where x is null",
> but "where x=null" does not produce a diagnostic.
>
> It also does not match any rows where x is null.
> Likewise, "where x=''" will not match any string
> column.
>
> Can somebody explain to me what "x=null" means
> to Oracle? Is there ever a case where it will
> produce a meaningful answer?
>
> Thanks!
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios

Under the ANSI SQL standard Nulls are never equal to anything in a relational comparison operation including to other Nulls. That is why ANSI specified the IS NULL and IS NOT NULL clauses to identify null values.

The result of any comparison to Null is Null so where column = Null results in Null. In PL/SQL IF variable = Null results in Null which is not true but it does not mean False either. You logic must account for the possibility of a value being Null. To test for Null in PL/SQL use IF variable IS NULL.

However, In sorts and aggregate functions Nulls are grouped together. Again this is per the ANSI standard.

HTH -- Mark D Powell -- Received on Fri Mar 02 2007 - 18:33:52 CST