Mixing Explicit And Implicit Joins

CommentsProcedural SQL (PSQL) Statements

Mixing Explicit and Implicit Joins

Mixing explicit and implicit joins is not recommend, but is allowed.However, some types of mixing are not supported by Firebird.

For example, the following query will raise the error “Column does not belong to referenced table”

SELECT *
FROM TA, TB
JOIN TC ON TA.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

That is because the explicit join cannot see the TA table.However, the next query will complete without error, since the restriction is not violated.

SELECT *
FROM TA, TB
JOIN TC ON TB.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

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;