Database

How a Database Optimizer Proves It Can Rewrite Your LEFT JOIN into an Inner Join

By 倔强的石头_ · Jul 13, 2026

Read original on juejin.cn ↗ Google Translate ↗ Alt translation

A database that silently rewrites join semantics can introduce subtle data bugs during a platform migration. KES's conservative, provably correct approach to outer join elimination means teams can trust that migrated SQL will not produce different results just because the optimizer got more aggressive.

Summary

When a WHERE clause filters on the nullable side of a LEFT JOIN with a null-rejecting predicate, the optimizer can safely rewrite the outer join to an inner join. This transformation is backed by a theorem-level proof in relational algebra, not a rule of thumb. Kingbase KES enforces this strictly, checking two prerequisites before acting: the predicate must reference only nullable-side columns, and it must reject NULLs.

The rewrite flows through five stages inside KES — parsing, normalization, pattern matching, predicate pushdown, and physical operator selection. The optimizer splits AND conditions and checks each predicate individually; a single matching null-rejecting predicate is enough to trigger elimination. Once the join type changes, filters can be pushed down to the scan layer, often cutting query time by an order of magnitude on large tables.

KES distinguishes itself with cascading elimination across nested joins, transparent handling of views so that wrapped and raw SQL produce identical plans, and faithful translation of Oracle's legacy `(+)` syntax. Its design philosophy prioritizes semantic safety over speculative speed, which reduces migration risk when moving legacy workloads onto a domestic database.

Takeaways

— Outer join elimination rewrites a LEFT JOIN to an inner join when a WHERE predicate on the nullable side rejects NULLs, a transformation proven equivalent in relational algebra.

— A null-rejecting predicate is any condition that returns False or Unknown for NULL inputs — strict comparisons, IN, BETWEEN, and arithmetic, but not IS NULL or COALESCE.

— Two prerequisites must both hold: the predicate must reference only nullable-side columns, and it must be null-rejecting. If either fails, the rewrite does not fire.

— KES splits WHERE clause AND conditions and evaluates each predicate separately; a single qualifying predicate is enough to eliminate the outer join.

— After elimination, filters can be pushed down to the scan layer, reducing the data volume entering the join and often improving query performance by 10x or more on large tables.

— KES handles cascading elimination across nested outer joins, transparently rewrites views to match hand-written SQL plans, and faithfully translates Oracle's `(+)` syntax.

— The optimizer's strictness-first philosophy means it will not guess or heuristically rewrite ambiguous SQL, preventing semantic drift during Oracle-to-KES migrations.

Conclusions

KES's decision to split AND predicates and evaluate them individually, rather than treating the WHERE clause as a monolithic block, is a practical engineering choice that increases the optimizer's chances of finding an elimination opportunity without sacrificing correctness.

The cascading elimination across nested joins relies on a chain of nullability inferences — once an inner join guarantees a key is not-null, that property propagates upward and can justify eliminating the outer join above it. This is a subtle but powerful compositional reasoning step.

View transparency is an underappreciated optimizer property. When a view and its inline equivalent produce different execution plans, developers lose the ability to reason about performance through abstraction layers. KES treating them identically preserves that predictability.

The handling of Oracle's `(+)` syntax is not just syntactic sugar; it requires the optimizer to map legacy outer-join semantics onto the same null-rejection framework used for standard JOIN ON clauses, which is a non-trivial semantic alignment problem.

Concepts & terms

Null-rejecting predicate

A filter condition that returns False or Unknown (never True) for any row containing a NULL in the referenced columns. Examples include strict comparisons like `col = 5`; counterexamples include `col IS NULL` or `COALESCE(col, 0) = 0`.

Outer join elimination

An optimizer rewrite that converts a LEFT/RIGHT OUTER JOIN into an INNER JOIN when a WHERE clause predicate on the nullable side is null-rejecting, proven equivalent by a relational algebra theorem.

Predicate strictness inference

A static analysis mechanism inside the optimizer that traverses an expression tree from leaves to root, determining whether each sub-expression rejects NULLs, to ultimately classify the whole predicate as null-rejecting or not.

View Inline

An optimizer phase that expands a view reference into its underlying query definition within the calling query, enabling subsequent rewrite rules (like outer join elimination) to operate on the combined query tree as if the view were never used.

Cascading elimination

The process where eliminating one outer join changes the nullability properties of join keys, which in turn justifies eliminating a parent outer join in a nested join tree — a chain reaction of rewrites.

Source: juejin.cn ↗ Google Translate ↗ Backup ↗