Left Join Elimination

Scenarios

In enterprise applications, ORM frameworks, view expansions, reporting queries, and generic SQL templates frequently generate complex SQL statements containing multiple LEFT JOIN clauses. The table on the right side of a LEFT JOIN is referenced only within the join condition, meaning that the query does not actually read any columns from that table. In other cases, rows from the left-hand table may undergo row amplification due to the join, but subsequent GROUP BY or DISTINCT clauses eliminate the amplification effect. When executing such queries, community MySQL still generates access paths and executes the join operations for these LEFT JOIN clauses. For complex queries, this introduces additional table scans, index lookups, join computations, and intermediate result processing, all of which degrade query performance.

TaurusDB supports left join elimination, allowing the optimizer to remove right-hand tables that do not affect the final result before generating the execution plan. This ensures that the query engine accesses only the tables required to produce the correct output, reducing CPU, I/O, and join execution overhead.

This optimization primarily covers three scenarios:

• Elimination based on GROUP BY or DISTINCT: If the right-hand table only amplifies the row count of the left-hand table, a subsequent GROUP BY or DISTINCT removes duplicates, and the query does not contain expressions (such as aggregate functions) that depend on the row count, the right-hand table can be removed.
• Elimination based on uniqueness: If the equality predicates in the ON condition cover all columns of a unique key on the right-hand table, each row from the left-hand table can match at most one row from the right-hand table and the right-hand table can be removed. Derived tables that produce unique rows through GROUP BY or DISTINCT are also treated as unique.
• Elimination based on subquery semantics (EXISTS/IN): When a LEFT JOIN appears inside an EXISTS, IN, NOT EXISTS, or NOT IN subquery and the right-hand table does not affect the boolean evaluation of that subquery, the right-hand table of the LEFT JOIN can be removed.

How It Works

Left join elimination is a semantic query-rewriting optimization based on equivalence rules of relational algebra. The optimizer identifies and removes redundant LEFT JOIN operations while guaranteeing that the final result set remains unchanged.

SELECT t1.a
FROM t1
LEFT JOIN t2 ON t2.a = t1.b;

SELECT t1.a
FROM t1;

Left join elimination occurs during the query resolver phase, after join structure simplification and join information collection. The optimizer first simplifies the join tree and then analyzes whether the right-hand table of a LEFT JOIN is referenced externally and whether it satisfies uniqueness or deduplication criteria. If the table qualifies, it is removed from the subsequent execution plan. Consequently, once this feature is enabled, eliminated tables will not appear in EXPLAIN outputs. You can inspect the join_elimination node and check the number of eliminated tables in the join_preparation step of an optimizer_trace.

Prerequisites

• The TaurusDB kernel version must be 2.0.75.260300 or later. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?
• left_join_elimination in the optimizer_switch system parameter must be ON. For details, see Modifying TaurusDB Instance Parameters.
• The SQL statement must contain LEFT JOIN or RIGHT JOIN and meet the conditions for left join elimination without affecting the final query results.

Supported Query Statements

• SELECT
• INSERT ... SELECT
• REPLACE ... SELECT
• CREATE TABLE ... SELECT
• Subqueries within any query statement
• Eligible queries inside views, derived tables, and prepared statements

Constraints

How to Use

You can manage left join elimination by configuring the optimizer_switch parameter.

SET optimizer_switch='left_join_elimination=ON';

SET optimizer_switch='left_join_elimination=OFF';

Examples

CREATE TABLE t1(a INT PRIMARY KEY, b INT);
CREATE TABLE t2(a INT PRIMARY KEY, b INT);
INSERT INTO t1 VALUES(1,1),(2,3),(7,NULL);
INSERT INTO t2 VALUES(2,2),(4,5),(5,4);
ANALYZE TABLE t1,t2;