Backend · Database

When WHERE Turns Your LEFT JOIN Into an INNER JOIN

By 一只牛博 · Jul 9, 2026

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

Queries that silently drop rows produce incorrect business results without errors or warnings. A developer migrating from MySQL or PostgreSQL can copy the same LEFT JOIN + WHERE pattern and get a different, wrong answer in KingbaseES, and no code review will catch it unless someone checks the execution plan.

Summary

A standard LEFT JOIN query meant to list all orders with optional after-sales details returned only one row instead of five. The culprit was a WHERE filter on the right table's column, which caused the KingbaseES optimizer to perform Outer Join Elimination — rewriting the query as an inner join without warning. The execution plan confirmed the join node lacked the expected 'Left' marker, showing the optimizer had already discarded the outer-join semantics.

Moving the filter condition from WHERE into the ON clause restored the correct behavior. The fix hinges on a simple rule: right-table filters that preserve outer-join semantics belong in ON, not WHERE. NULL comparisons in WHERE evaluate to 'unknown' and get discarded, so any left-join rows that produce NULLs on the right side are silently removed before the result set is built.

The same NULL semantics trip up inequality checks. Finding rows with no match requires IS NULL on a right-table key, not a != comparison. Developers migrating from MySQL or PostgreSQL are especially vulnerable because identical SQL can behave differently under KingbaseES's optimizer without any syntax error or warning.

Takeaways

— Put right-table filter conditions in the ON clause of a LEFT JOIN, not in WHERE, unless the goal is to also filter rows from the left table.

— Run EXPLAIN ANALYZE whenever a LEFT JOIN returns fewer rows than expected; if the join node lacks a 'Left' marker, the optimizer has rewritten it as an inner join.

— Use IS NULL on a right-table key to find rows with no match; inequality operators fail because NULL comparisons evaluate to 'unknown' and get discarded.

— Identical SQL can behave differently across database engines due to optimizer-specific Outer Join Elimination rules, with no error or warning.

— KingbaseES V009R001C010 performs this rewrite silently; the SQL text still says LEFT JOIN but the execution plan shows a plain Hash Join.

Conclusions

Outer Join Elimination is a legitimate optimization, but its silence is dangerous — the query succeeds, returns a result, and looks correct at a glance, yet data is missing.

The pitfall is most acute during database migrations because the SQL is syntactically identical and previously worked; the changed behavior is invisible to diff tools and code review.

Many developers treat LEFT JOIN + WHERE as a stylistic choice rather than a semantic one, not realizing the two placements produce different result sets when NULLs are involved.

Concepts & terms

Outer Join Elimination

A query optimizer rewrite where a LEFT/RIGHT JOIN is converted to an INNER JOIN because a WHERE clause on the right table would discard all NULL-extended rows anyway, making the outer join semantically equivalent to an inner join but cheaper to execute.

NULL semantics in SQL

Any comparison with NULL using standard operators (=, !=, <, >) evaluates to UNKNOWN, not TRUE or FALSE. In a WHERE clause, UNKNOWN is treated as FALSE, so rows with NULLs are discarded. IS NULL and IS NOT NULL are the only reliable ways to test for NULL.

Source: juejin.cn ↗ Google Translate ↗ Backup ↗