LEFT JOIN becomes INNER JOIN: the tenant filter trap

A LEFT JOIN becomes an INNER JOIN the moment your WHERE clause puts a condition on the joined table. Rows with no match get NULLs on the right side, the condition compares against NULL, and those rows disappear. Every SQL developer meets this once. I met it repeatedly on a multi-tenant compliance platform, because the tenancy layer wrote that WHERE condition for me on every query.

This post covers how the bug shows up, why multi-tenant apps are especially prone to it, and the three fixes I used.

Why does my LEFT JOIN act like an INNER JOIN?

Because a filter on the right-hand table sits in the WHERE clause. The join keeps unmatched left rows and fills the right side with NULLs, then WHERE runs, and any comparison with NULL is not true, so those rows are removed. Move the condition into the ON clause, or filter the right-hand table in a subquery, and the unmatched rows stay.

The PostgreSQL docs on table expressions show this with a two-table example and put it plainly: a restriction in the ON clause is processed before the join, a restriction in the WHERE clause after it, and with outer joins that "matters a lot."

Why a LEFT JOIN becomes an INNER JOIN under tenant filters

The platform runs a query layer that adds a tenant predicate for every table in the FROM clause. That is a sound safety rule: nobody can forget the tenant filter on a table, because the layer adds it. It adds it to the WHERE clause, though, and that is exactly where an outer join breaks.

My first attempt at finding controls whose owner had been deactivated outer-joined the users table. A simplified version of what ran:

SELECT c.id, c.title
FROM controls c
LEFT JOIN users u ON u.id = c.owner_id
WHERE c.tenant_id = :tenant
  AND u.tenant_id = :tenant;  -- added by the tenancy layer

A control with no owner has no matching user, so u.tenant_id is NULL, the second condition is not true, and the row is gone. The query hid every control that had no owner at all, which are arguably the controls that most need attention. An unowned row has no tenant on its missing side, so a tenant filter on that side can never pass.

The same bug, five more times

Once I knew the shape, it kept turning up in other readers:

• Evidence expiry. A join here would have turned inner, dropped every file whose evidence row was missing, and raised a duplicate request for it. I used a scoped subquery instead.
• The control register. The reader ran as one outer-joined statement, so a control nobody had assessed vanished from the list, "precisely the row somebody opens the register to deal with." It now reads separate scoped queries and merges them.
• A deactivated-owner sweep across three owner columns uses three statements instead of one join, because the layer "silently drops rows".
• The gap reader runs four statements instead of one join. An unowned gap had vanished from the list, because its missing owner has no tenant.
• The register view reads gap counts and control counts as separate statements. My commit note: a join there "is a bug this codebase has shipped twice."

The action queue was the clearest measure of the stakes. A join would have dropped every item with no gap, "which is 12 of the 19 rows on the demo tenant." That is most of the list, gone without an error.

Three ways to keep unmatched rows

1. Put the right-hand condition in the ON clause. If you write the SQL yourself, this is the textbook fix: LEFT JOIN users u ON u.id = c.owner_id AND u.tenant_id = :tenant. The filter now limits which users can match, and controls without a match survive. It only works when you control where the predicate goes, which I did not.

2. Filter the right-hand table in a scoped subquery. The subquery is its own SELECT, so the tenancy layer scopes it on its own, and the outer query never filters on a nullable side.

SELECT c.id, c.title
FROM controls c
WHERE c.tenant_id = :tenant
  AND (c.owner_id IS NULL
       OR c.owner_id IN (SELECT u.id FROM users u
                         WHERE u.tenant_id = :tenant AND NOT u.is_active));

3. Run separate scoped queries and merge in code. This is what most of the readers above do now. Each statement touches one table, each gets its tenant filter, and the application joins the results. It costs an extra round trip or two, a trade I'll take over a list that silently loses rows. If a reader needs heavy aggregation, I'd rather push that work into one well-tested SQL statement, as in moving aggregation into Postgres, and check its row counts.

Postgres row-level security is worth knowing here as well. A policy filters each table's rows before the join sees them, so an outer join keeps its unmatched rows. The row security docs cover how policies apply. I wrote more about tenancy models in multi-tenant SaaS architecture.

Other things a strict tenancy layer refuses

The same layer refused a few other statement shapes, and each needed a workaround:

• UPDATE ... RETURNING is refused. Sweeps now select the ids first, then update with the claim predicate repeated in the UPDATE, so a row another worker claimed in between is left alone.
• A SELECT with no table is refused too. That broke a textual advisory lock, and approval returned 500. I removed the lock and let a unique constraint carry the guarantee. A constraint is the stronger tool anyway, as I found when fixing race condition duplicate rows with a unique index.

None of this argues against the layer. A tenant filter that cannot be forgotten is worth some awkward SQL. It does mean every reader that joins needs a test with an unmatched row.

Where I'd start

• Search your codebase for LEFT JOINs followed by a WHERE condition on the joined table. Each one is a candidate.
• If a framework or tenancy layer adds predicates for you, find out whether it puts them in WHERE or ON.
• For every list that joins, add a test fixture with a row that has no match: an unowned record, a record never assessed, an item with no parent.
• Compare row counts with and without the join on real data. A list that shrinks when you add a join is the signal.

The same habit of checking real counts on a production copy is how I approach a Postgres index audit.