Re: "= Null" <> "is Null"?

On Jul 8, 2009, at 1:30 PM, Durumdara wrote:

> Hi!
> select * from any where is_deleted = Null
> select * from any where is_deleted in (Null)
>
> They are show 0 record.

Correct, that's normal in SQL.

NULL means 'unknown', so you can't say whether is_deleted is true or
false when it's NULL. The result of that comparison is NULL as well,
'unknown' and that makes the where-clause evaluate to false, so you
don't get any rows.

If it were otherwise you wouldn't be able to do some queries.

> select * from any where is_deleted is Null

This is exactly the reason the 'is null' operator exists. It's
exclusively for checking for null values. You can't say 'is_deleted is
true' for example.

> Some of other DBs are uses Null as Null in >=< comparisons. Is PG
> not? What are the rules?

Some DB's say that an empty string is the same as null, it doesn't
mean they're right. In fact, it can be rather inconvenient if an empty
string in your data also has a meaning (namely 'known to be an empty
string' instead of 'unknown')!

What if I'm comparing two columns, say in an outer join, and one of
the columns is null? Does that mean I don't get my row back while I
should? To me it seems like those other DB's use their comparisons
with null inconsistently, or they wouldn't be able to do outer joins...

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4a548b0a10137687714970!