Conisder following SQL statement
SELECT * FROM sometable WHERE num <> 1;
If you expected this to return rows where value of num is null, then you are wrong. Similarly
SELECT * 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.
Got some more time read this on wikipedia.
Saturday, August 18, 2007
weirdo NULL handling in SQL
Subscribe to:
Post Comments (Atom)
2 comments:
anjain bhai...baaki to theek hai, par topic ke naam me "wierdo" adjective ki kya zaroorat hai. Since null is not a value it should not be considered or compared as a value, isme wierd kya hai ??
M@DAR a.k.a G@URAV
its weird because its behavior differs from what you would normally or intuitively expect it to behave.
e.g. intuitively a new SQL user would expect < > to include null values in result.
Post a Comment