A Note On Equality

CommentsProcedural SQL (PSQL) Statements

A Note on Equality

Important

This note about equality and inequality operators applies everywhere in Firebird’s SQL language, not only in JOIN conditions.

The “=” operator, which is explicitly used in many conditional joins and implicitly in named column joins and natural joins, 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 join, 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, when you want to join on inequality, use IS DISTINCT FROM, not “<>”, if you want NULL to be considered different from any value and two NULLs considered equal:

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

Ambiguous field names in joins

Firebird rejects unqualified field names in a query if these field names exist in more than one dataset involved in a join.This is even true for inner equi-joins where the field name figures in the ON clause like this:

select a, b, c
  from TA
  join TB on TA.a = TB.a;

There is one exception to this rule: with named columns joins and natural joins, the unqualified field name of a column taking part in the matching process may be used legally and refers to the merged column of the same name.For named columns joins, these are the columns listed in the USING clause.For natural joins, they are the columns that have the same name in both relations.But please notice again that, especially in outer joins, plain colname isn’t always the same as left.colname or right.colname.Types may differ, and one of the qualified columns may be NULL while the other isn’t.In that case, the value in the merged, unqualified column may mask the fact that one of the source values is absent.