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
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
