SQLite 3.27:

Is there a way to write an expression where comparing a null to a null would evaluate to true?

For example:

with data (a,b) as (
values
(1,1),
(1,null),
(null,null)
)

select
  *
from
  data
where
  a = b

But the result would be:

     a       b
------  ------
     1       1
(null)  (null)

Instead of:

     a       b
------  ------
     1       1

db<>fiddle

CodePudding user response：

It looks like the answer is yes.

Use IS instead of =:

with data (a,b) as (
values
(1,1),
(1,null),
(null,null)
)

select
  *
from
  data
where
  a is b

Result:

     a       b
------  ------
     1       1
(null)  (null)

db<>fiddle

Related:

• Can I use… IS NOT DISTINCT FROM (null-safe equals comparison)
• Can I use… is (binary comparison)

I don't have a newer version of SQLite to test in. But it's possible the is syntax doesn't work in newer versions. Maybe only the IS NOT DISTINCT FROM syntax works.