Grim Coder Grim Coder - 2 months ago 6
SQL Question

Why does my SQL query != "Some Value" not return any rows where NULL?

I'm querying an Oracle db for my MVC project and I found something in my SQL that I was curious about. Why does this Query not return rows where the VALIDATED_BY Column is NULL?

SELECT *
FROM DM_SSA_DEV.REQUESTS
WHERE BATCH_ID = 1399
AND VALIDATED_BY <> 'AUTOUSER'


When I commented out the last line I got my expected 481 results:

SELECT *
FROM DM_SSA_DEV.REQUESTS
WHERE BATCH_ID = 1399
--AND VALIDATED_BY <> 'AUTOUSER'


Finally I found I could circumvent this issue via explicitly handling the NULLs. Was this the right thing to do or was there a better way, and why?

SELECT *
FROM DM_SSA_DEV.REQUESTS
WHERE BATCH_ID = 1399
AND (VALIDATED_BY <> 'AUTOUSER' OR VALIDATED_BY IS NULL)


P.S. I'm also really using Linq in my project so this is the code I used. Most efficient?

db.REQUESTS.Where(r => r.BATCH_ID == batchId && (r.VALIDATED_BY != "AUTOUSER" || r.VALIDATED_BY == NULL)).ToList<REQUEST>();

Answer

NULL is never equal (=) to any other value, even another NULL (it is a bit like floating-point NaN). This also includes not equal expressions involving NULL.

The intent can be expressed as x = y OR (y IS NULL and x IS NULL), when NULL values are to be considered equal.

The special case of not-equal or NULL-and-not-equal is x <> y OR x IS NULL, assuming y is never NULL.

Another option is to coalesce, COALESCE(x, '*') = COALESCE(y, '*'), depending on how much the optimizer is trusted and the equality of the coalesced value to NULL. There may also be other vendor extensions.

LINQ/EF will translate x == null (C#, null must be a constant expression) to x IS NULL (SQL).

Comments