A WHERE clause demotes your LEFT JOIN

A lesson from Green Pipelines, Wrong Numbers. Play it above, or read it through below.

• A LEFT JOIN adds rows. Every right-hand column on them is NULL.

  The common read: A LEFT JOIN preserves the left side, so once the word LEFT is in the query every cabinet reaches the report.

  Name what is in the visit columns of a cabinet nobody has ever visited, then name where that row came from.

  Not from either table. The documentation defines the outer join as a two-step construction: first an inner join is performed, then for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Avenida do porto's row was manufactured after the inner join ran out of matches, and every site_visits column on it is NULL: outcome, visit_id, and cabinet_id, which reads NULL on a row whose cabinet id is 2.

  The join key comes back empty, so every right-hand value on a manufactured row is invented by the join, not stored by either table. The report they feed is the ISP's maintenance backlog: one row per street cabinet, at the grain of the cabinet, not the visit, with the last visit attached where there is one. Field ops counts the rows and calls it coverage, so when it drops the ticket goes to whoever loaded the visits, and neither engineer who reviewed the query read the clause underneath it.

• ON decides what matches. WHERE decides what survives.

  The common read: A filter is a filter, so a predicate does the same work whether it sits in ON or in WHERE.

  The two clauses do not run at the same moment. The documentation states the difference and its consequence in one sentence: a restriction placed in the ON clause is processed before the join, while a restriction placed in the WHERE clause is processed after the join, and that does not matter with inner joins, but it matters a lot with outer joins. Before the join, a predicate decides only what counts as a match, and a left row that matches nothing is added afterwards with NULLs.

  Three-valued logic takes over from there. The documentation says what WHERE does to a row: after the processing of the FROM clause is done, each row of the derived virtual table is checked against the search condition, and if the result of the condition is true the row is kept in the output table, otherwise, if the result is false or null, it is discarded. On avenida do porto's row outcome is NULL, NULL = 'fault' is unknown, and unknown is discarded on the same line as false.

  So the row the outer join was written to protect is deleted by a clause that never mentions it. The documentation is not neutral about this: it says the ON or USING clause of an outer join is *not* equivalent to a WHERE condition, because it results in the addition of rows for unmatched input rows as well as the removal of rows in the final result. Same expression language, opposite powers.

  The row count after the move is the row count an inner join returns, and the query still says LEFT JOIN, which is why the diff was approved. Field ops filed the drop in cabinet coverage against the inspections importer, reasoning that fewer cabinets means fewer visits were recorded.

• IS NULL is a test a manufactured row can pass. So is IS DISTINCT FROM.

  The common read: Any predicate on a right-hand column in the WHERE clause demotes the outer join, so the anti-join pattern must be broken too.

  Which table the column came from decides nothing. What decides is whether the predicate can return true while that column is NULL, and exactly two of the predicates a working engineer reaches for can.

  IS NULL is the first. The documentation gives it a return type rather than a caveat: it tests whether a value is null and returns boolean, true or false, never unknown. It answers true on manufactured rows and false on matches, so the clause position that deleted every addition four screens ago now deletes the matches and keeps the additions. That inversion is the anti-join, the standard way to find left rows with no match, and it is this lesson's rule read correctly rather than an exception to it.

  IS DISTINCT FROM is the second, met at null-propagates as the comparison that acts as though null were a normal data value rather than unknown, and graded nowhere. Over this fixture the pair is one word apart and one row apart. where v.outcome <> 'clear' returns one row: the cabinet nobody has visited answers unknown to a negation exactly as it does to an equality. where v.outcome is distinct from 'clear' returns two: a definite boolean cannot be discarded for being neither true nor false.

  The negation is what costs money, because it reads as a catch-all. A reviewer reads <> 'clear' as everything that is not clear, which is what the words say, not what the row set does, and the cabinet nobody has visited is the entry a maintenance backlog exists to surface. The quarterly compliance extract is built on that clause, and when the count comes up light the gap is raised against the analyst who built it, never against the clause that treats an unvisited cabinet like a clear one.

• The clause is not the test. The test is whether the predicate can be true on a NULL.

  The common read: The rule is that a WHERE clause must not touch the right-hand table, so moving my filter onto a left-hand column is always safe and wrapping it in coalesce is cosmetic.

  Sort four predicates into the two that keep the outer join and the two that do not, before reading which is which. c.street <> 'travessa da luz'. v.visit_id is not null. v.outcome <> 'clear'. coalesce(v.outcome, 'none') <> 'clear'.

  Neither the column's side nor the operator predicts anything. where c.street <> 'travessa da luz' returns two rows and leaves avenida do porto's NULL half intact: row loss the author asked for, the only one of the four that changes no join semantics. v.visit_id is not null returns two rows and is the inner join longhand, true only on matches. v.outcome <> 'clear' returns one, for S6's reason. coalesce(v.outcome, 'none') <> 'clear' returns two: 'none' is definite and is not 'clear', so the manufactured row answers true.

  So the property is one question asked of the predicate rather than of the clause: can this expression evaluate to true while every right-hand column on the row is NULL. Ask it of the whole expression and never of the operator on its own, because the same operator answers differently depending on what sits on the other side of it. is null can. is distinct from a non-null value can. A coalesce can only when the substituted default itself satisfies the comparison.

  That is why coalesce(v.outcome, 'none') <> 'clear' returns two rows and coalesce(v.outcome, 'none') = 'fault' returns one, the same one the inner join returns, on the same fixture, same wrapper, same column. A bare =, <>, <, >, like or in against a non-null value cannot, and neither can is not distinct from a non-null value, the null-safe spelling of =, which demotes exactly where = does. is not null cannot either; it is on that list because the demotion is asked for on purpose.

  The coalesce case is where this arrives in a pull request. Someone adds it to make a display column read "none" instead of blank, the diff touches the SELECT list and the WHERE clause in the same commit, and the row count moves for a reason that is nowhere in the ticket. The analyst who notices files it as a data-quality issue with the field-ops team, because the number of cabinets went up.

• Nothing you would spot-check moved.

  The common read: If the change had broken the report I would have seen it, because I opened the report and checked a row.

  Pick the row you would check to verify the change, then check it in both versions.

  Move the predicate from ON to WHERE and the LEFT JOIN returns

  It is identical. Rua dos ferreiros has an open fault, matches on both sides of the join condition in both spellings, and comes back byte for byte the same: same cabinet, same visit id, same outcome. Every row that survives the demotion survives it unchanged, because the demotion does not alter a single matched row. What it alters is the set, and the rows it removes are the rows whose right-hand side was empty, which is the set nobody opens a maintenance report to look at.

  That is the whole reason this failure ships. A spot check tests the row you can describe, and the rows that changed are the ones you cannot describe, because the thing that makes them interesting is an absence. A diff of the two queries is one line. A diff of the two outputs is not visible in any row, only in how many of them there are, and the report has no expected count.

  So it clears review, it clears the smoke test, and it clears the eyeball check by the person who wrote it. The audit finds it eleven months later, from the other side: the regulator asks for the estate and the estate is smaller than the asset register, and the finding is written against the field-ops team for not inspecting cabinets that were on nobody's list to inspect.

• RIGHT is the mirror. FULL is demoted from whichever side the predicate names.

  The common read: A FULL OUTER JOIN guarantees a row for every row on both sides, so a WHERE clause cannot take one back.

  The mechanism is not specific to LEFT. The documentation defines RIGHT OUTER JOIN as the converse: an inner join followed by a joined row with null values in the columns of T1 for each unmatched row of T2, so the result table will always have a row for each row in T2. Swap the sides and every sentence holds: a WHERE predicate naming a left-hand column cannot be true on the manufactured rows. Here a RIGHT JOIN returns three rows; where c.street like 'rua%' cuts it to one.

  FULL OUTER JOIN is both halves at once, so it demotes in halves. It manufactures NULL-filled right columns for unmatched left rows and NULL-filled left columns for unmatched right rows, so a predicate naming one side's column is unknown on that side's manufactured rows and definite elsewhere. where v.outcome = 'fault' keeps the matched fault and the orphaned visit whose cabinet no longer exists, and deletes the cabinet nobody has visited. Four rows become two, exactly what a RIGHT JOIN returns under the same predicate.

  The lost half is always the same half: the side the predicate does not name. That is the half you wrote the outer join for, because the side the predicate names is the side you already have rows for. Two of the three joins in the reconciliation pipeline are RIGHT JOINs, written that way by whoever needed the visits preserved, and the audit script that was written after the last finding greps for left join.

• Read the join that is written.

  The common read: I would catch this in review, because a demoted outer join looks different from a healthy one.

  Decide which of these four changes leaves the outer join intact, then read which do.

  LEFT JOIN and LEFT OUTER JOIN are one construct: OUTER is optional, both return three rows here, and a grep for one spelling reads half a repository. USING looks like it moves the predicate somewhere safer, and it does not: the documentation says USING forms a join condition, and that the ON or USING clause of an outer join is not equivalent to a WHERE condition. Spelled with USING, the demoted query still returns one row. USING changes which columns come out, not when the filter runs.

  Two changes leave the join intact and both move the predicate to where it is evaluated before the join: put it in the join condition with AND, or filter the right-hand table in a derived table and join to that. Both return three rows here, and the second is the one to reach for when the predicate is long enough that the join condition stops being readable.

  The third change gets approved. Someone told the outer join is being lost writes where v.outcome = 'fault' or v.visit_id is null, and it returns two rows rather than three. It rescues the cabinet never visited, whose visit_id is NULL, and still deletes the cabinet whose visit came back clear, a real match with a real visit_id and a real outcome that is not fault. Half a repair looks exactly like a repair on a fixture where every unmatched left row is unmatched for the same reason.

  That commit closes the ticket. The count goes up, the person who raised it confirms the cabinet they noticed is back, and the class of row that is still missing is the one that never appears in a bug report, because a cabinet whose last visit was clean is not a cabinet anyone is looking for.

• Nothing here was a bug. Go read your own outer joins.

  The common read: These numbers were wrong, so somewhere in the pipeline there is a bug to find.

  Name what would have to be fixed to make the backlog report show three cabinets again, then read the rest.

  Nothing, because nothing is broken. Every statement in this lesson is correct and returned exactly what the documentation says it returns, and two of them returned the identical number for the identical reason: the LEFT JOIN with the predicate in the WHERE clause and the plain inner join with the same predicate are the same query, written twice, one of them with a word in it that stopped meaning anything the moment the predicate moved. The word is still there. It passes review because it is there.

  The mechanism was never in the join. It was in one clause: whether a predicate can be true while its columns are NULL, a property of the predicate, not of the table, which is why no linter, schema or fixture full of matching rows will mention it. A fixture where every left row has a match cannot tell the two spellings apart. That is most fixtures. Every ticket here was filed against a data loader or an inspector, and the diff behind them moved one predicate down one line.

  So the list worth writing down tonight is two greps, and they are both mechanical. First: every outer join in the repository, which means left join, left outer join, right join, right outer join, full join and full outer join, because OUTER is optional and half a repository will be written the other way. Second: for each one, whether any predicate in that statement's WHERE clause names a column from the side the join was protecting.

  If one does, can it be true when that column is NULL? Ask it of the whole predicate, not the operator. is null can, and is distinct from a non-null value. A coalesce only when its default satisfies the comparison: coalesce(last_visit, 'none') <> 'clear' keeps the manufactured rows and coalesce(last_visit, 'none') = 'fault' deletes them. An equality, an inequality, a range, a like, an in list and the null-safe is not distinct from cannot: a statement with one of those is an inner join wearing the other one's name.