A WHERE clause demotes your LEFT JOIN

A lesson from Successful 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 cabinet nobody has visited comes out of the join as its own row from the cabinets table, so its visit cabinet_id still reads the cabinet's id and only the visit details are empty.

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

  The row did not come from either table. The documentation defines the outer join in two steps: first an inner join is performed, then for each row in the left-hand table (cabinets) that does not satisfy the join condition with any row in the right-hand table (site_visits), a joined row is added with null values in the right-hand table's columns. Every site_visits column on avenida do porto's row is NULL, including cabinet_id, which reads NULL although the cabinet's id is 2.

  The query feeds the ISP's maintenance backlog report: one row per street cabinet, at the grain of the cabinet rather than the visit, with the last visit attached where there is one. Field ops counts the rows and calls the count coverage. When the count drops, the ticket goes to whoever loaded the visits, and the two engineers who reviewed the query never read its join condition closely.

• ON decides what matches. WHERE decides which rows are kept.

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

  The two clauses do not run at the same moment. The documentation says 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 this does not matter with inner joins but 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.

  After the join, three-valued logic applies. The documentation says each row of the table produced by FROM is checked against the search condition: if the result is true the row is kept, and if it is false or null the row is discarded. On avenida do porto's row outcome is NULL, NULL = 'fault' is unknown, and WHERE discards unknown exactly as it discards false. The row the outer join was written to keep is discarded by a clause that never mentions that row.

  The documentation states outright that the ON or USING clause of an outer join is *not* equivalent to a WHERE condition. The two clauses accept the same expressions and have opposite effects on unmatched rows. After the move the query returns the row count of an inner join and 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 meant fewer recorded visits.

• IS NULL is a test an added 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 does not matter. What matters is whether the predicate can return true while that column is NULL. Ordinary comparison operators yield null when either input is null. IS NULL returns true on a NULL and false on any other value, never unknown, so in WHERE it keeps only the added rows and discards the matches. That is the anti-join, the standard way to find left rows with no match, and it follows from this lesson's rule rather than breaking it.

  IS DISTINCT FROM, met in an earlier lesson, acts as though null were a normal data value rather than unknown. On these three cabinets where v.outcome <> 'clear' returns one row: on the unvisited cabinet's row a negation evaluates to unknown, as an equality does. where v.outcome is distinct from 'clear' returns two, because it returns a definite boolean, and WHERE discards only false and unknown.

  The negation is the version most likely to pass review. A reviewer reads <> 'clear' as everything that is not clear, which matches the words but not the rows returned, and the cabinet nobody has visited is the entry a maintenance backlog exists to show. When the quarterly compliance extract built on that clause comes up short, the analyst who built it is blamed, never 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'.

  c.street <> 'travessa da luz' returns two rows and leaves avenida do porto's NULL half intact: row loss the author asked for, and 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 written longhand, true only on matches. v.outcome <> 'clear' returns one row, because a negation against NULL is unknown. coalesce(v.outcome, 'none') <> 'clear' returns two rows: 'none' is definite and is not 'clear', so the predicate is true on the added row.

  Neither the column's side nor the operator predicts the result. Ask one question of the whole expression, never of the operator alone: can it evaluate to true while every right-hand column on the row is NULL? is null can, and so can is distinct from a non-null value. A coalesce can only when the substituted default satisfies the comparison: with the same wrapper and column, coalesce(v.outcome, 'none') = 'fault' returns one row, the same one the inner join returns.

  Against a non-null value, =, <>, <, >, like or in cannot, and neither can is not distinct from, the null-safe =, which demotes exactly where = does. is not null cannot either, and a query that uses it asks for the demotion on purpose. The coalesce case usually arrives in a commit that makes a display column read "none" instead of blank and touches the WHERE clause. The analyst who sees the row count change files it against field ops because the cabinet count went up.

• Nothing you would spot-check changed.

  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

  The row is identical. Rua dos ferreiros has an open fault, matches in both spellings, and comes back with the same cabinet, visit id and outcome. The demotion alters no matched row. It removes the rows whose right-hand side was empty, which are the rows nobody opens a maintenance report to look at. A spot check tests a row you can describe, and the removed rows are distinguished only by a missing match.

  The diff is one line, and the two outputs differ only in their row count, for which the report has no expected value. So the change passes review, the smoke test, and a visual check by the person who wrote it. An audit finds it eleven months later: the regulator asks for the list of cabinets, the list is shorter than the asset register, and the auditors blame field ops for not inspecting cabinets that were on nobody's list to inspect.

• RIGHT JOIN swaps the sides. When the predicate cannot be true on a NULL, a FULL JOIN keeps unmatched rows only on the 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 remove one.

  The documentation defines RIGHT OUTER JOIN as the converse of a left join: each right-hand row with no match gets a joined row with null values in the left-hand table's columns. Here a RIGHT JOIN returns three rows; where c.street like 'rua%' reduces them to one, and where c.street is null keeps only the orphaned visit. Two of the three joins in the reconciliation pipeline are RIGHT JOINs, and the audit script written after the last finding greps only for left join.

  A FULL OUTER JOIN adds NULL-filled rows on both sides, so each side is demoted separately. where v.outcome = 'fault' keeps the matched fault and the orphaned visit, discards the cabinet nobody has visited, and turns four rows into two, which is what a RIGHT JOIN returns under the same predicate. When the predicate cannot be true on a NULL, the side that loses its unmatched rows is the side the predicate does not name, which is the side you wrote the outer join for.

• 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.

  LEFT JOIN and LEFT OUTER JOIN are one construct: OUTER is optional, both return three rows here, and a grep for one spelling misses the other. Four changes to the demoted query: spell the join with using (cabinet_id); move the predicate into the ON clause with AND; filter site_visits in a derived table and join to that; add or v.visit_id is null to the WHERE clause. Decide which of the four leave the outer join intact, then read which do.

  using (cabinet_id) still returns one row. 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. USING changes which columns appear in the output, not when the filter runs. The predicate in the ON clause with AND returns three rows, because it is evaluated before the join. The derived table returns three rows for the same reason, and is the better choice when the predicate would make the join condition hard to read. or v.visit_id is null returns two rows, not three. It restores the cabinet never visited and still discards travessa da luz, a real match with a real visit_id and an outcome that is not fault.

  The fourth change is the one that gets approved. On a fixture where every cabinet has either a fault visit or no visit at all, it returns the same rows as a full repair. It closes the ticket, the person who raised it confirms their cabinet is back, and the rows still missing are cabinets whose last visit was clean, which nobody files a bug about.

• 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 has to be fixed, because nothing is broken. The LEFT JOIN with the predicate in WHERE and the inner join with the same predicate are the same query and return the same number, because the word LEFT stopped having any effect when the predicate moved. A fixture where every left row has a match cannot tell the two apart, and most fixtures are like that. Every ticket here was filed against a loader or an inspector. Run these checks tonight:

  Grep for every outer join: left join, left outer join, right join, right outer join, full join and full outer join, because OUTER is optional. Read each statement it finds and check whether a predicate in its WHERE clause names a column from the table the join sets to NULL on the rows it adds: the right-hand table of a LEFT JOIN, the left-hand table of a RIGHT JOIN, either table of a FULL JOIN. If one does, check whether the whole predicate, not its operator, can be true when the column is NULL. is null can, and so can is distinct from a non-null value. A coalesce can only when its default satisfies the comparison: coalesce(v.outcome, 'none') <> 'clear' keeps the added rows and coalesce(v.outcome, 'none') = 'fault' discards them. Against a non-null value, an equality, an inequality, a range, a like, an in list and the null-safe is not distinct from cannot. When one of those stands alone in the WHERE clause or is joined to the rest with AND, the statement behaves as an inner join while its text says outer join.