Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.

When it comes to programming languages, the null is usually treated the same in most of them. So when you compare a value to a null, you'll get a mismatch i.e. the values are not equal. Not so in relational databases though, and this can be a subtle caveat to any new-comer to SQL.

Lets take the following simple Java program as an example first...
 Java
String[] arr = {"a", null, "c"};
for (String s : arr) {
if (!"a".equals(s)) {
System.out.println("Found value other than 'a' : " + s);
}
}


It looks for anything that doesn't match the string "a" in the array arr and prints out the value. As expected, it will print both null and the string "c".
Found value other than 'a' : null
Found value other than 'a' : c


Now lets take a hypothetical database table with a single column that has the same data as the array above...
 SQL
> select value from table;
value
-----
a
null
c


If we try to get all values that do not match 'a', the results are rather different to the Java example...
 SQL
> select value from table where value <> 'a';
value
-----
c


The only value selected from the database is 'c', the null is not returned. So what's going on here? Well if we head over to the Oracle Database SQL Reference we will read the following statement - "Do not use null to represent a value of zero, because they are not equivalent."

In the Oracle database, nulls are treated as "UNKNOWN" and not as values representing nothing like they are in Java. Further the documentation states - "A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows.".

That explains why the row with the null value is not returned. So nulls need to be treated a little differently with SQL by using the IS NULL or IS NOT NULL clauses. To make an equivalent SQL statement to the Java code above, we would have to do this...
 SQL
> select value from table where value <> 'a' or value is null;
value
-----
null
c




-i

Skip down to comments...
A quick disclaimer...

Although I put in a great effort into researching all the topics I cover, mistakes can happen. If you spot something out of place, please do let me know.

All content and opinions expressed on this Blog are my own and do not represent the opinions of my employer (Oracle). Use of any information contained in this blog post/article is subject to this disclaimer.
 
comments powered by Disqus
Other posts you may like...