When exactly does the optimizer secretly rewrite a LEFT JOIN into an INNER JOIN?

When writing a LEFT JOIN, most people have a very simple expectation: the rows from the left table will be preserved no matter what, and if the right table has no match, the result is NULL. This expectation holds true in most scenarios, but as soon as you add a filter condition on a right-table column in the WHERE clause, this expectation can quietly collapse—the SQL text clearly says LEFT JOIN, but the execution plan shows a pure inner join, and the result set changes accordingly.

This article does not reproduce a business scenario. Instead, it uses the two cleanest possible tables to test the boundary conditions of this mechanism one by one: what conditions trigger this rewrite, what conditions don't, and how much the result differs when conditions are placed in different positions. The environment is still KES V009R001C010, connected with a business account:

ksql -h 127.0.0.1 -p 54321 -U app_user -d app_db

Set up a minimal test bench

Two tables, t_oje_t1 is the left table, t_oje_t2 is the right table:

create table app_schema.t_oje_t1 (
  id1 integer primary key,
  name1 varchar(30) not null
);

create table app_schema.t_oje_t2 (
  id2 integer primary key,
  id1 integer not null,
  name2 varchar(30) not null
);

insert into app_schema.t_oje_t1(id1, name1) values
  (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

insert into app_schema.t_oje_t2(id2, id1, name2) values
  (101, 1, 'aa'),
  (102, 2, 'bb'),
  (103, 2, 'cc'),
  (104, 3, 'cc');

The data is deliberately designed this way: t1 has 4 rows, id1=4 has no match at all in t2 (simulating a "missing right table"), and id1=2 corresponds to two records in t2 (bb and cc, simulating a one-to-many relationship). Later we will see that this "one-to-many" design hides a very critical pitfall—more on that later.

Step 1: Right-table condition in WHERE, the outer join disappears directly

explain analyze
select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2 on t1.id1 = t2.id1
where t2.name2 = 'cc';

The first line of the execution plan is Hash Join—note—not Hash Left Join. In KES, whenever a query is truly executed as an outer join, the plan will honestly include the word Left. It's not here, which means this LEFT JOIN was already rewritten into a plain inner join during the optimization phase. Looking further down, t_oje_t2 is scanned via Seq Scan with a Filter: (name2)::text = 'cc'::text), Rows Removed by Filter: 2—t2 has a total of 4 rows, only 2 remain after filtering (the cc with id1=2, the cc with id1=3), these 2 rows then do a Hash Join with t1, final actual rows=2.

The reasoning is straightforward: the condition t2.name2 = 'cc', for id1=1 (no cc match) and id1=4 (no data at all in t2, all fields are NULL), NULL = 'cc' evaluates to neither true nor false, but "unknown". In a WHERE clause, "unknown" is equivalent to being discarded. Since these NULL rows produced by the outer join will be filtered out anyway, the result of "outer join + this filter" is exactly the same as "inner join + this filter". The optimizer sees this is a good deal and directly switches to the cheaper inner join to execute. This is outer join elimination.

Step 2: Switch to IS NULL, the result is completely reversed

explain analyze
select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2 on t1.id1 = t2.id1
where t2.name2 is null;

This time the execution plan shows Hash Right Join, Filter: (t2.name2 IS NULL), Rows Removed by Filter: 4, final actual rows=1—that is, the row with id1=4.

There's a detail here that can easily make one pause: the plan says Right Join, not Left Join. This is not elimination; it's the optimizer swapping the positions of the driving table and the probe table (scanning t2 first to build the hash table, then probing with t1). Semantically, t1 LEFT JOIN t2 and this Right Join where t2 is the build side and t1 is the probe side are completely equivalent outer joins; only the physical execution order is reversed. The "preservation" semantics of the outer join are not lost at all. This is completely different from the Hash Join in Step 1 (no Left/Right keyword, a pure inner join); don't mix them up.

Why can't it be eliminated this time: the IS NULL condition is specifically designed to catch the NULL rows produced by an outer join. If the outer join were changed to an inner join, rows in t2 with no match would never enter the result set, and t2.name2 is null could never be true. This is no longer "getting the same result in a faster way"; it fundamentally changes the query semantics, so the optimizer will not touch this kind of condition.

The judgment criterion is very clear here: whether elimination is possible depends on what this WHERE condition evaluates to for the NULL values produced by the outer join—if it's definitely false or unknown (like a normal equality comparison), elimination is safe; if it could possibly be true (like IS NULL), elimination would change the result, and the optimizer won't do it.

Step 3: Condition moved to the left table, unrelated to outer join elimination

explain analyze
select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2 on t1.id1 = t2.id1
where t1.name1 = 'b';

This time the execution plan is Nested Loop Left Join, the word Left is honestly present; it's a true outer join. Join Filter: (t1.id1 = t2.id1), t_oje_t1 is first filtered by Filter: (name1)::text = 'b'::text to produce 1 row (id1=2), then this one row does an outer join with t2, final actual rows=2—because id1=2 has two matches in t2 (bb, cc), one left-table row correlates to two result rows. This has nothing to do with outer join elimination.

The essence of filtering in this step is "first decide which rows the left table wants, then use those rows for the outer join", which has no relation to the Nullable characteristic of the right table. Many people easily treat "any condition appearing in WHERE" as a trigger for outer join elimination; this step serves as a control group to break that misunderstanding.

Step 4: Moving the left-table condition into ON, the result has one more row than expected

Originally, the intention for this step was to verify: does putting a left-table condition into ON have any hidden effect similar to "right-table condition in WHERE"?

select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2
  on t1.id1 = t2.id1 and t1.name1 = 'b';

The originally envisioned result was 4 rows—t1 fully preserved, only rows where name1 <> 'b' would have all NULLs for t2 fields. After running it, it was 5 rows, one more. Looking closely at the output: the row id1=2 appears twice, once corresponding to id2=102/bb, once to id2=103/cc; id1=1, 3, 4 each appear once, with all t2-related fields being NULL.

After figuring this out, it felt quite reasonable: putting the condition t1.name1='b' into ON only tells the optimizer "only left-table rows where name1='b' are allowed to match the right table". It governs "whether matching is allowed", not "how many rows the right side can produce after matching". id1=2 satisfies name1='b', so it takes id1=2 to t2 to find all records with id1=2—and t2 originally has two records with id1=2, both will be joined out; none will be merged just because "an outer join guarantees at least one row". The "preservation" guarantee of a left join only ensures that this left-table row appears at least once, it never guarantees it appears only once.

Where did the earlier assumption of "4 rows" go wrong: it carelessly equated "4 rows in the left table" with "4 rows in the result", forgetting that the right table originally had one-to-many data. This pitfall is actually quite common. When writing business SQL, as long as the right table of a JOIN has a one-to-many relationship, whether you add conditions or not, and where you put them, the row count may not match "the number of rows in the left table". You must first confirm the correspondence in the right table, then see if the result matches expectations.

Step 5: Moving the right-table condition into ON is the correct way to preserve outer join semantics

explain analyze
select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2
  on t1.id1 = t2.id1 and t2.name2 = 'cc';

This time the execution plan is Hash Left Join, the word Left is firmly there, actual rows=4. The difference from Step 4 is: this time the filter condition t2.name2='cc' acts on the right table. It first filters t2 down to only rows satisfying name2='cc' (the cc with id1=2, the cc with id1=3; the bb with id1=2 is blocked out). After filtering, each id1 in t2 has at most one row left, which then does an outer join with t1. Each of t1's 4 rows corresponds to exactly 1 result row; id1=1 and id1=4 have NULLs for t2 fields, id1=2 and id1=3 have values.

Comparing with Step 1 makes it very clear: similarly wanting to find "records associated with name2='cc'", putting the condition in WHERE will also kill the rows in t1 that have no match (outer join eliminated), while putting the condition in ON filters the right table while preserving the full set of the left table. This is the most important rule to remember from this article: For filtering on the right table, as long as the goal is to preserve outer join semantics, put it in ON, not WHERE. And for filtering on the left table (Step 4), putting it in ON and putting it in WHERE have different effects—putting it in WHERE first filters the left table then joins; putting it in ON only decides "whether the filtered left-table rows are allowed to join", the right table will still produce as many rows as it should. The two cannot be memorized interchangeably.

Step 6: Oracle (+) syntax, the same rules in a different skin

KES is compatible with Oracle-style (+) outer join syntax. Let's test whether it follows the same set of rules:

explain analyze
select * from app_schema.t_oje_t1 t1, app_schema.t_oje_t2 t2
where t1.id1 = t2.id1(+)
  and t2.name2 = 'cc';

The execution plan is Hash Join, no Left/Right keyword, actual rows=2, exactly the same result as the WHERE approach in Step 1—the filter condition does not carry (+), the outer join is still eliminated. Now look at the approach where the filter condition also carries (+):

explain analyze
select * from app_schema.t_oje_t1 t1, app_schema.t_oje_t2 t2
where t1.id1 = t2.id1(+)
  and t2.name2(+) = 'cc';

This time it's Hash Left Join, actual rows=4, completely consistent with the result of pushing the condition down to ON in Step 5. This shows that the (+) syntax follows the same set of judgment logic under the hood. (+) is just another syntactic sugar for outer joins; it does not mean the outer join is guaranteed to be preserved just because you wrote it—whether the filter condition should also carry (+) corresponds to the effect of "condition in WHERE vs. condition in ON". This point is especially important for readers migrating from Oracle: if old code only wrote (+) on the join condition, and then separately added a filter condition without (+), it will still be judged as safely eliminable into an inner join.

Wrapping up: How to audit this issue in your own SQL

After running through all this, the judgment criteria can be summarized into a few points:

1. Check if the SQL text has LEFT/RIGHT JOIN or (+), this is the starting point of the audit.
2. Run EXPLAIN ANALYZE and watch whether the join node explicitly carries the word Left/Right. If it doesn't, you can basically confirm it has been eliminated.
3. Cross-reference the WHERE clause to see if there is an equality, range, IN, or similar filter on a column of the right table (the Nullable side), and IS NULL/IS NOT NULL is not used—this type of condition is the typical signal that triggers elimination.
4. To preserve outer join semantics for a right-table filter condition, push it down to ON; the same logic applies under (+) syntax, whether the filter condition should carry (+) follows the corresponding relationship.
5. A left-table condition in ON is not the same as in WHERE: putting it in WHERE first filters the left table then joins; putting it in ON only decides whether this left-table row can participate in the join, the right table will still produce as many rows as it should—especially when the right table has a one-to-many relationship, never directly map the left-table row count onto the right-table row count.

Ultimately, these rules all boil down to one thing: whether an outer join is eliminated never depends on whether you wrote LEFT, but on whether the overall logic of this query, when calculated as an inner join, produces exactly the same result. If there is even the slightest difference (even just the possibility of preserving one more NULL row), the optimizer will not eliminate it; if it is exactly the same, it will definitely eliminate it, aiming for the cheaper execution cost of an inner join. When writing SQL, what you have in mind is "does the business need preservation"; when the database executes, what it looks at is "can the logic be equated". The gap between these two is the root of this kind of pitfall.