Saturday, August 18, 2007

Conisder following SQL statementSELECT * FROM sometable WHERE num <> 1;If you expected this to return rows where value of num is null, then you are wrong. SimilarlySELECT * FROM sometable WHERE LENGTH(my_col) < 20 above will not return rows with my_col having null values.

This is bcoz SQL don't consider null as a value and since null is not a value it cannot be compared with a value. This is the diffrence between Null and either 0 (zero) or an empty string (a string value with a length of zero, represented in SQL as '' ). While null indicates the absence of any value, the empty string and numerical zero both represent actual values. So be careful while you are writing "less than" or "not equal to" queries on nullable column.