NULL values not filtered out with WHERE statement

原问题：

SELECT ID, VOLUME,  TYPEOF(VOLUME) 
FROM DBT.BASE 

When I select these columns, I see that the results have some NULL values. They don’t seem to be strings. However, when I try to filter the NULL values out with a where statement:

SELECT ID, VOLUME,  TYPEOF(VOLUME) 
FROM DBT.BASE 
WHERE VOLUME = NULL

I don’t see any results. What might be the possible causes? I also tried filtering with 'NULL' but that would throw an error since the column type is double.

采纳答案：

use this for only want null recode

SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE VOLUME IS NULL
or
SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE ISNULL(VOLUME,'') = ''

if you get not null value then use

SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE ISNULL(VOLUME,'') <> ''
or 
SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE VOLUME IS NOT NULL

问题解析：

NULL值的判断，在数据库中NULL值的判断比较特殊，不像普通的值通过=即可，如果判断为NULL的值需要使用IS NULL，如果判断不为NULL的值则需要使用IS NOT NULL。