Null-safe equals comparison

Question

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

Answer 1

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 in newer versions of SQLite.