When WHERE Turns Your LEFT JOIN Into an INNER JOIN

A few days ago, I helped look at an old requirement migrated from MySQL. The business requirement was stated like this: "List all orders, and if an order has a pending after-sales application, also bring out the after-sales reason." This kind of requirement is very common. My first reaction was LEFT JOIN — the left table (orders) guarantees all rows, and the right table (after-sales) brings out matching records; if there's no match, it's NULL. A textbook approach. But the results this SQL produced left me staring at the screen for several seconds.

This article records the entire troubleshooting process: how it was discovered, how it was investigated, how the root cause was finally located, and how to fix it in KingbaseES (KES). The environment is KES V009R001C010, connected using a regular business account app_user, not an administrator:

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

Scenario Reconstruction: Order Table + After-Sales Table

First, set up the scenario. Two tables: an order table t_pit_order and an after-sales service order table t_pit_service. An order may have 0, 1, or even multiple after-sales records:

create table app_schema.t_pit_order (
  order_id integer primary key,
  order_no varchar(40) not null unique,
  user_id integer not null,
  order_status varchar(20) not null,
  amount numeric(10,2) not null,
  created_at timestamp not null
);

create table app_schema.t_pit_service (
  service_id integer primary key,
  order_no varchar(40) not null,
  service_status varchar(20) not null,
  reason varchar(100),
  created_at timestamp not null
);

Insert 5 orders, all in paid status:

insert into app_schema.t_pit_order(order_id, order_no, user_id, order_status, amount, created_at)
values
  (1, 'PIT20260701001', 1001, 'paid', 199.00, '2026-06-28 09:00:00'),
  (2, 'PIT20260701002', 1002, 'paid',  88.00, '2026-06-28 10:00:00'),
  (3, 'PIT20260701003', 1003, 'paid', 256.00, '2026-06-28 11:00:00'),
  (4, 'PIT20260701004', 1004, 'paid', 320.00, '2026-06-28 12:00:00'),
  (5, 'PIT20260701005', 1005, 'paid', 150.00, '2026-06-28 13:00:00');

After-sales records are only provided for two orders: Order 1 is "Pending Review", Order 3 is "Completed". Orders 2, 4, and 5 have never submitted after-sales requests:

insert into app_schema.t_pit_service(service_id, order_no, service_status, reason, created_at)
values
  (1, 'PIT20260701001', 'Pending Review', 'Wrong size', '2026-06-29 09:00:00'),
  (2, 'PIT20260701003', 'Completed', 'Quality issue', '2026-06-29 10:00:00');

This data design is not random; it deliberately leaves three situations: has after-sales and is pending review (Order 1), has after-sales but not pending review (Order 3), and has no after-sales at all (Orders 2, 4, 5). You'll see why all three situations need to be covered.

First Version of SQL: Looks Completely Fine

The first version written according to the business requirement is a standard LEFT JOIN with a filter condition:

select
  o.order_no,
  o.order_status,
  o.amount,
  s.service_status,
  s.reason
from app_schema.t_pit_order o
left join app_schema.t_pit_service s
  on s.order_no = o.order_no
where s.service_status = 'Pending Review';

My expectation at the time was: all 5 orders present, only Order 1 would have values in the service_status and reason columns, and the other 4 rows should be NULL. Left join means the left table guarantees all rows, that's common sense.

The actual result was this:

Only 1 row. Orders 2, 3, 4, and 5 all disappeared, including orders 2, 4, and 5 that never applied for after-sales, and also Order 3 which has an after-sales record but with a status of "Completed". To put it bluntly, this SQL behaved exactly like an INNER JOIN; the LEFT keyword seemed to be written for nothing.

The first thought that popped into my head was "Did I insert the data wrong?", and the second was "Does KES have a compatibility issue with LEFT JOIN?". Both thoughts were later proven wrong, but I took many detours during the investigation. I'll record it following this line of thought.

First, Rule Out Data Issues: Is the Main Table Really 5 Rows?

The dumbest but most reliable method: first confirm the main table itself has no problems:

select count(*) as total_order from app_schema.t_pit_order;

select o.order_no, o.order_status
from app_schema.t_pit_order o
left join app_schema.t_pit_service s
  on s.order_no = o.order_no;

count(*) confirmed 5 rows. After removing WHERE and re-running the join, all 5 orders came back, not one missing. This step is crucial; it proves two things: the data itself is fine, and the join condition in ON is not wrong. The problem is 100% caused by adding WHERE s.service_status = 'Pending Review'.

At this point, the guess that "KES's LEFT JOIN has a bug" can basically be ruled out — if it were a syntax-level problem, removing WHERE should also have a problem, but it doesn't. The truly weird part is: only when that filter is added does the behavior change.

Locating: The Execution Plan Has No LEFT JOIN At All

After flipping through some previously saved technical materials, I remembered that this phenomenon of "LEFT JOIN turning into an inner join after adding WHERE" has a name: "Outer Join Elimination". Regardless of the name, let's directly use EXPLAIN ANALYZE to see what exactly happened in the execution plan:

explain analyze
select
  o.order_no,
  o.order_status,
  o.amount,
  s.service_status,
  s.reason
from app_schema.t_pit_order o
left join app_schema.t_pit_service s
  on s.order_no = o.order_no
where s.service_status = 'Pending Review';

Look at the first line of the execution plan: Hash Join (cost=1.04..2.12 rows=1 width=49). Note that it says Hash Join, not Hash Left Join. In KES's execution plan, if a join is actually executed as an outer join, it usually explicitly marks the word Left; it's not here, meaning that after this SQL was submitted, the optimizer never executed it as an outer join. It directly treated the entire query as a regular inner join.

Looking further into the details: the t_pit_service table is scanned by Seq Scan with a Filter: ((service_status)::text = 'Pending Review'::text), followed by a line Rows Removed by Filter: 1 — this table has a total of 2 rows, and after filtering, only 1 row remains (Order 1's "Pending Review" record; Order 3's "Completed" was filtered out). Then this 1 row is used for a Hash Join with the order table. The order table participates in the hash probe with all 5 rows, and ultimately only 1 row matches.

This is the truth of the problem: the optimizer understood the condition WHERE s.service_status = 'Pending Review' as "only rows in t_pit_service with a status of 'Pending Review' are eligible to participate in the join." Those rows that should have been preserved because of the left join, where the t_pit_service side is NULL (Orders 2, 4, 5 have no after-sales records; Order 3's after-sales record was filtered out), were all screened out in this step. The reason is simple: comparing NULL with 'Pending Review' for equality results in neither true nor false, but "unknown". In WHERE, "unknown" is equivalent to being discarded. Since these NULL rows produced by the left join would be killed by WHERE anyway, the optimizer did the math: an outer join with this filter produces exactly the same result set as a direct inner join with this filter, and the inner join has lower overhead. So it quietly replaced the outer join with an inner join. This conversion process doesn't report an error, doesn't give any hint; the SQL text still says LEFT JOIN, but what's actually executed is no longer that.

Behind this set of conversion rules, there is actually a clear set of judgment criteria, such as what kind of conditions will definitely trigger this rewrite and what kind won't. The details are many, and I won't lay them all out here. For now, firmly remember the phenomenon and location method from this pitfall — when encountering "LEFT JOIN results are fewer than expected", the first reaction is to check whether the join node in the execution plan is actually running as an outer join.

Fix: Move the Filter Condition into ON

Once the cause is clear, the fix is straightforward: to truly preserve the left join semantics, the condition for filtering the right table cannot be written in WHERE; it must be written into ON:

select
  o.order_no,
  o.order_status,
  o.amount,
  s.service_status,
  s.reason
from app_schema.t_pit_order o
left join app_schema.t_pit_service s
  on s.order_no = o.order_no
  and s.service_status = 'Pending Review';

This time, all 5 rows came back obediently. Only Order 1 has values in service_status/reason; Orders 2, 3, 4, and 5 are all empty. Pay special attention to Order 3 here — it clearly has an after-sales record ("Completed"), but because it doesn't satisfy the and s.service_status = 'Pending Review' condition, its after-sales fields are still empty. This point is very telling: it's not luck that the row count matches; the logic is truly correct. ON determines "how to join": first filter the right table by the "Pending Review" condition, then take the filtered result and do an outer join with the left table. WHERE determines "what to filter out after joining": adding it in WHERE means acting after the join is done, which is too late; the NULL rows have already been sentenced to death.

My own understanding is remembered like this: whenever it's a left join, for filter conditions related to the right table, unless the purpose is to "also filter out a batch from the left table", always consider putting them in ON first. Don't just lazily throw them into WHERE.

Correcting a Common Confusion: Querying Orders "Without Pending After-Sales"

While troubleshooting this SQL, a colleague casually asked: "So if I just want to check which orders don't have pending after-sales, can I just change the equals sign to !=?" — This is also a common misconception, so I quickly verified it:

select o.order_no, o.order_status
from app_schema.t_pit_order o
left join app_schema.t_pit_service s
  on s.order_no = o.order_no
  and s.service_status = 'Pending Review'
where s.service_id is null;

The result returns Orders 2, 3, 4, 5 — four rows. Note that Order 3 is also included — although it has an after-sales record, its status is "Completed". In this association based on the "Pending Review" condition, it effectively didn't match, so service_id is also NULL, and it's counted among those "without pending after-sales". The key to this SQL is that IS NULL checks whether the right table as a whole has a match, not whether a certain value equals something. Only by using it can you truly capture the "empty rows produced by the outer join". If you really did as the colleague said and changed the equals sign to a not-equals sign in WHERE, you'd fall back into the initial pitfall. I won't repeat the verification here; the logic is the same as before.

Takeaways from This Pitfall

Organized into a few items that can be directly copied into a Code Review checklist:

1. When you see LEFT/RIGHT JOIN followed by WHERE right_table_field = xxx, pause first. This kind of writing is very likely putting the filter condition in the wrong place. First ask yourself: is this condition meant to "filter out some data from the left table", or do you simply want to see a certain value from the right table? Only the former is suitable for WHERE.
2. For filter conditions on the right table, as long as the goal is to preserve outer join semantics, always push them down into ON. This is not a "depends on the situation" thing; it's the default action.
3. If you suspect an outer join has a problem, directly run EXPLAIN ANALYZE, don't guess. Check whether the join node in the execution plan explicitly carries the word Left. If it doesn't, it's very likely been rewritten by the optimizer.
4. To check for "missing", use IS NULL, not inequality operators. Inequality operators will also be tripped up by the NULL semantics of WHERE. IS NULL is the only reliable method to judge "no match".
5. People migrating from MySQL/PostgreSQL are especially prone to this pitfall, because the writing style is exactly the same as before. The SQL hasn't changed a single character, but the behavior has changed due to this optimizer action. Code review can't see it at all; you can only confirm it by running the execution plan.

The most disgusting part of this pitfall is right here: the syntax is completely legal, the SQL executes normally, no errors are reported, it just quietly throws away the data you thought should be there. The truly fatal problems are never the ones that report errors, but the ones where "it looks like nothing happened."