Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL.

Why does = NULL not work here?

share|improve this question
3  
Yep, as Nick and Fabricio state correctly nothing really equals NULL because NULL means currently unknown. It's not a value as such, just an unknown - so nothing can equal it. – Peter Schofield Aug 3 '11 at 22:26
Oracle is not the only one that does that. See: stackoverflow.com/questions/7367327/… – NullUserException Sep 11 '11 at 15:56

4 Answers

up vote 9 down vote accepted

Take a look at PSOUG's notes on NULL. As Fabricio Araujo hinted, NULL is not really a value like the number 4 or string 'bacon strips'. In fact, NULL is untyped in the SQL language, which is why you cannot validly use it in an equality comparison. You need the special IS [NOT] NULL syntax to check if a value is NULL or not.

share|improve this answer
4  
+1 for making me hungry. – Aaron Bertrand♦ Aug 3 '11 at 22:29
1  
@Nick - I think the orafaq link is misleading when it states that null is "untyped". Consider the different result types in the following: select decode('A','B',to_char(null),'A','1') from dual; and select decode('A','B',to_number(null),'A','1') from dual; – Jack Douglas♦ Aug 4 '11 at 15:38
2  
@Jack - Actually, taking a cursory look at this posting of the SQL-92 standard, section 4.1 states: "A null value is an implementation-dependent special value that is distinct from all non-null values of the associated data type. There is effectively only one null value and that value is a member of every SQL data type." I would say "untyped" is a good summary of "is a member of every data type". :) – Nick Chammas Aug 4 '11 at 19:58
1  
@Nick - but to_number(null) is still null (select * from dual where to_number(null) is null;), it is just a 'typed' null. – Jack Douglas♦ Aug 4 '11 at 21:18
1  
@Jack - That's right. You've casted the original, "untyped" NULL to a "typed" NULL. The orafaq link is still correct to say that NULL (which to me implies a "pure" NULL) is untyped, but perhaps they should add that it can be explicitly cast as a specific type. I wonder if the only impact casting a NULL can have is on functions like DECODE that derive the data type of their output from one of the inputs. – Nick Chammas Aug 5 '11 at 15:53
show 5 more comments
up vote 5 down vote

In SQL Server, we have an connection setting to get =NULL to behave equally to IS NULL. But in latest versions is not recommended anymore - it's even marked as deprecated.

The recommended is the SQL Standard way - the IS [NOT] NULL operator.

(And I will not start an war whether 'NULL is a value or a status' here)... hehehe

share|improve this answer
up vote 1 down vote

Rather than justify the is null syntax I think it is better to point out that there are no good general rules-of-thumb when dealing with nulls and the syntax used to handle them. For example:

  1. updating a value uses syntax like set val = null rather than something like set val to null which might mirror the is null syntax better
  2. it is almost always a mistake when dealing with null to say: "nulls behave like so-and-so here, so they should behave like such-and-such here

Here is an excellent essay on the subject from a postgres perspective. Briefly summed up by saying nulls are treated differently depending on the context and don't make the mistake of making any assumptions about them.

share|improve this answer
up vote 1 down vote

Oracle treats NULL as an unknown value. Ask yourself if this equality works. 

(unknown amount in George's wallet) = (unknown amount in Harry's wallet)

or otherwise stated

NULL = NULL

The answer is clearly maybe, which is neither true nor false.

EDIT: In response to the comments I will add a little clarification to NULL as I have seen it used.

While NULL really means "not set", in the context of this question I believe the above statement is correct.

There are a number of reasons for a column to have a NULL value:

  • The column is not appropriate to the sub-type or state of the row. (It is known we shouldn't have a value. In this case we shouldn't be comparing the value of the column to another value. Testing for IS NULL may be useful.)
  • The value is not available, or hasn't been recorded in the column. (The value exists but for some reason isn't known by the database. In these cases NULL is equivalent to unknown by the database.)

In some contexts, NULL may be handled in ways that might be considered incorrect, including:

  • NULL values may be replaced with a default value. An unset NOT NULL number is likely to have the value 0. (This does not necessarily mean the value is known to be 0.)
  • An empty string may be converted to NULL or vice versa. (The value may be known, an empty.)
share|improve this answer
Oracle does not treat null as meaning that a value is unknown. Don't confuse the logical truth-value UNKNOWN with the missing value marker NULL - they are different things. – sqlvogel Aug 4 '11 at 6:38
@Bill - Oracle, and as far as I know all other RDBMS's treat null differently according to the context. – Jack Douglas♦ Aug 4 '11 at 15:47
@dportas - unknown is not used at all in Oracle (unlike postgres for example) – Jack Douglas♦ Aug 4 '11 at 15:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.