A.2A Note on Equality

Important

This note about equality and inequality operators applies everywhere in Firebird’s SQL language.

The = operator, which is explicitly used in many conditions, only matches values to values. According to the SQL standard, NULL is not a value and hence two NULLs are neither equal nor unequal to one another. If you need NULLs to match each other in a condition, use the IS NOT DISTINCT FROM operator. This operator returns true if the operands have the same value or if they are both NULL.

  |select *
  |  from A join B
  |  on A.id is not distinct from B.code

Likewise, in cases where you want to test against NULL for a condition of inequality, use IS DISTINCT FROM, not <>. If you want NULL to be considered different from any value and two NULLs to be considered equal:

  |select *
  |  from A join B
  |  on A.id is distinct from B.code