SQL - An explanation of != 'NULL'

Question

My SSMS code is as follows:

Select top(50)
From FilteredContact
Where statuscode = 1 
and emailaddress1 != 'NULL' 
and telephone1 != 'NULL' 
and address1_line1 != 'NULL' 

I realize that ideally, the last three lines are to be formatted as follows:

and emailaddress1 IS NOT NULL
and telephone1 IS NOT NULL
and address1_line1 IS NOT NULL

However, my original code actually worked completely fine. Why is that? I thought NULL was an unknown value, not a string value equal to 'NULL'

If anyone knows, an explanation would be greatly appreciated! Thanks.

Answer 1

emailaddress1 != 'NULL' compares emailaddress1 and the string 'NULL'. Since the field is almost certainly a string, the type matches well enough. And since no one's going to have an email address of 'NULL', the comparison won't eliminate valid rows (which have non-null, but also non-'NULL', values).

Where things get a bit less intuitive is when the field's value is actually NULL. In standard SQL, the result of just about any comparison with a null value is UNKNOWN (which is generally treated the same as NULL). Since the DBMS only returns rows where the condition is true, it won't return that row.

So the comparison works unless the string 'NULL' would otherwise be valid in that field.

Answer 2

This doesn't involve ANSI_NULL (I don't think). You are trying to match a string in your where clause. When you look for !='NULL', you are asking SSMS to return anything that is not the string "NULL". Since NULL is an unknown and all comparisons to it will be NULL, it will only return other valid strings.

DECLARE @testing TABLE (ID INT, items VARCHAR(100))
INSERT INTO @testing (ID, items)
VALUES 
(1,'blah'),
(2,'blahhhhhh'),
(3,'NULL'),
(4,NULL)


SELECT * 
FROM @testing 

--will return values that are NOT the string 'NULL' and values that are not NULL
--because NULL compared to anything is NULL
SELECT * 
FROM @testing 
WHERE items != 'NULL'

SELECT * 
FROM @testing 
WHERE items <> 'NULL'

--will return all string values that have a non-NULL value
SELECT * 
FROM @testing 
WHERE items IS NOT NULL 

Answer 3

I'm guessing

SET ANSI_NULLS OFF

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-2017

Or as others have suggested NULL is being stored as string.

Answer 4

However, my original code actually worked completely fine.

Worked by accident. If for example emailaddress1 ='NULL' (a string value consisting of four characters: N U L L), then the condition evaluates to FALSE, and is incorrect. Since the email address is unlikely to be NULL, then yes - it worked perfectly :)

---

See this answer for a long explanation: NOT IN selection with NULL values

In short:

Comparisons with NULL and the three-valued logic (3VL)

Since Null is not a member of any data domain, it is not considered a "value", but rather a marker (or placeholder) indicating the absence of value. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown.[8] The logical result of the expression below, which compares the value 10 to Null, is Unknown:

SELECT 10 = NULL -- Results in Unknown

so that both comparisons: x = NULL and x <> NULL evaluates to NULL(unknown).

In your condition, say and telephone1 != 'NULL', if telephone is NULL, then the whole conditions evaluates to NULL, which is treated as false condition in the WHERE clause.