Why is my query returning null values when I have a NOT NULL clause in my where statement

I'm trying to get DISTINCT and NOT NULL values but this doesn't seem to work:

SELECT DISTINCT ITEM 
FROM TABLE 
WHERE ITEM IS NOT NULL

This returns DISTINCT values but it also returns NULL VALUES.

sample:

ITEM
a
a
b
b
c
c
NULL

output:

a
b
c
NULL

Try this. "IS NOT NULL" and "IS NULL" does not work with blank values. You can cover both, if the column allows blank.

SELECT 
   DISTINCT item FROM table 
WHERE 
   ISNULL(item,'') <> ''

This statement will return a b c unless you have the string NULL not the symbol in the column:

SELECT DISTINCT ITEM FROM TABLE WHERE ITEM IS NOT NULL

To test this out, try these

--check for
SELECT DISTINCT ITEM FROM TABLE WHERE ITEM = 'NULL'

--modify original query
SELECT DISTINCT ITEM FROM TABLE WHERE NULLIF(ITEM, 'NULL') IS NOT NULL

Check if Item is of type Varchar and you are storing the value "NULL" in it. If so then please try the query given below:

select distinct item from table where ISNULL(item,'')<>'' and item <> "NULL"