Thread: "= 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!

Durumdara <durumdara@gmail.com> writes:

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

PostgreSQL implements SQL, which has a 3-valued logic. There's True,
there's False, and there's NULL. NULL means that we know nothing about
what's in there.

Would you really want to say that something you know nothing about is
the same thing as this other thing you know nothing about?

In PostgreSQL, NULL = NULL answers NULL (we know nothing about the
result).

Regards,
--
dim

On Wed, Jul 8, 2009 at 6:03 AM, Alban
Hertroys<dalroi@solfertje.student.utwente.nl> wrote:
> 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.

Uh, yes you can.  is false also works. and is not true and is not false.

> 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')!

This is the behavior in Oracle.  And I found that out the hard way...
what a PITA.

Anyhow, this isn't specific to Postgres, but here's a Wikipedia entry
for how ternary or 3 valued logic works.

http://en.wikipedia.org/wiki/Ternary_logic