[Feature Request] Syntax for fallback default values in outer joins (e.g. LEFT JOIN ... WITH DEFAULT)

[tomneko]

Hi everyone,

I would like to propose a syntactic enhancement regarding outer joins and NULL handling, and hear your thoughts on its feasibility.

Background & Problem

When performing outer joins (e.g. LEFT JOIN), unmatched rows inevitably generate NULL values. In everyday real-world applications, this forces developers to clutter the SELECT list with extensive defensive expressions like COALESCE(b.amount, 0) or COALESCE(b.status, 'none').

This creates two issues:

1. Responsibility mismatch: Fallback values for join misses belong to the join logic, yet they pollute the SELECT projection.
2. Duplication of metadata: Even when target tables define NOT NULL DEFAULT ..., outer joins bypass these table-level defaults entirely and generate NULLs anyway.

Proposed Syntax

I propose introducing an optional clause to outer joins that automatically fills missed rows with defaults:

-- 1. Implicit fallback to target table's column DEFAULTs
SELECT a.id, b.amount, b.status
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id
  WITH DEFAULT;

-- 2. Explicit column-level override
SELECT a.id, b.amount, b.status
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id
  WITH DEFAULT (
    amount = 0,
    status = 'none'
  );

Semantics & Edge Cases

• Implicit WITH DEFAULT:
  • Applies to base tables where columns have explicit DEFAULT definitions.
  • For derived tables, views without underlying defaults, or columns without default clauses, it simply falls back to the standard NULL.
• Explicit WITH DEFAULT (...):
  • Explicit values override table defaults or provide fallbacks where no table default exists.

Implementation Perspective

From an engine perspective, this doesn't necessarily require deep changes to the optimizer or the relational execution engine. It could potentially be treated as syntactic sugar at the parser / AST-rewrite stage:

• Rewriting b.amount in the projection list internally to COALESCE(b.amount, <default_value>).

This approach keeps queries clean, readable, and closer to actual business intent without breaking standard SQL compatibility (as it is a purely optional extension).

What do you think about this direction? Could this be viable as a future feature or experimental extension in Firebird?

Best regards,

[asfernandes]

This is not an area the engine should go.
This is application data/logic problem.

[tomneko]

Thanks for the feedback, Adriano.

I understand the design principle of keeping the engine scope strictly focused on core relational algebra.

My motivation was simply from a practical standpoint—much like how COALESCE, CASE, or Oracle's DECODE originally emerged not from pure relational theory, but from real-world application demands to handle presentation logic and reduce repetitive boilerplate in queries.

I completely respect the project's architectural boundary on this, though. Thank you for taking the time to review it!