I noticed somthing interesting today about NULL handling. This is very basic stuff, but if it’s new to me, then it’s disco material.
Say you have a table with one integer column:
CREATE TABLE Table1(Column1 INT)
With the following values:
INSERT INTO Table1 VALUES (NULL) INSERT INTO Table1 VALUES (0) INSERT INTO Table1 VALUES (1)
You might think that the following two queries produce inverse results, but they don’t:
SELECT Column1, CASE WHEN Column1<>0 THEN 'TRUE' ELSE 'FALSE' END AS Column2 FROM Table1 NULL FALSE 0 FALSE 1 TRUE SELECT Column1, CASE WHEN Column1=0 THEN 'TRUE' ELSE 'FALSE' END AS Column2 FROM Table1 NULL FALSE 0 TRUE 1 FALSE