Subscribe to this thread
Home - General / All posts - SQL9 NVARCHAR NULLs and the <> Operand
joebocop
426 post(s)
#14-Oct-20 17:26

In Release 8 a query

SELECT * FROM [t] WHERE [c] <> "this value"

returns all rows where the value in [c] is 'not equal to' "this value" and also those rows where there is no value stored in [c].

In Release 9, when running that same query, those rows where 'there is no value stored in [c]' are not returned.

Is this change meant to bring SQL9 into compliance with a broader standard or norm? It results in having to rewrite query syntax to handle NULLs in nvarchar fields in 9, adding an "OR [c] IS NULL" to every statement; not the end of the world, but also an unwelcome discovery.

tjhb

9,452 post(s)
#14-Oct-20 18:08

Yes, 9 is being more correct. The result of any comparison with NULL is (should be) NULL, not TRUE or FALSE.This was fudged in 8. (It also treated empty strings as NULL, which 9 does not.)

joebocop
426 post(s)
#14-Oct-20 18:13

Thank you tjhb. This change seems to bring 9 into line with the RDBMS I use (PostgreSQL) as well, so this is likely for the best.

Dimitri


6,233 post(s)
#14-Oct-20 18:37

[Edit] Crossed with Tim's much better post (concise and to the point...), but what the heck... :-)

tjhb

9,452 post(s)
#14-Oct-20 18:42

If you like, you can use Coalesce instead of adding an extra test. E.g.

WHERE Coalesce([c], 'that value') <> 'this value'

Which evaluates as TRUE where [c] is NULL.

joebocop
426 post(s)
#14-Oct-20 20:12

Classy stuff, thank you

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.