A NATURAL JOIN report returned rows until a migration added created_at to both tables — why, and how do you fix it?

## What actually changed The query text is unchanged and the data is unchanged, so the instinct is to look at the data. The right place to look is the schema. `NATURAL JOIN` is defined as `USING (<every column name present in both tables>)`. That list is not stored with the query; it is derived each time the statement is compiled, from the tables as they exist then. Before the migration the tables shared exactly one name and the derived condition was: ```sql l.order_id = r.order_id ``` After a migration that added an audit column `created_at` to both tables, the derived condition became: ```sql l.order_id = r.order_id AND l.created_at = r.created_at ``` Two timestamps written by two different inserts are equal only by coincidence — at microsecond precision, essentially never. The join therefore matches almost nothing, and the report returns zero rows without an error, without a warning, and without any change to the SQL under version control. The same failure has quieter variants. A `status`, `name`, `id`, `type` or `updated_at` column added to one side of a pair that already has it on the other produces a partial collapse rather than a total one: some rows still match, the totals are wrong, and nobody notices for a quarter. ## Diagnosing it The tell is that the query text and the data both look fine while the result changed. A quick sequence: 1. **Expand the natural join by hand.** List both tables' columns and intersect the names. The standard, portable way is the catalog: ```sql SELECT column_name FROM information_schema.columns WHERE table_name = 'order_lines' INTERSECT SELECT column_name FROM information_schema.columns WHERE table_name = 'orders'; ``` Any name in that result that is not part of the intended key is your bug. 2. **Rewrite the join explicitly and compare.** Run the same query with `USING (order_id)` and see the rows come back. That both confirms the diagnosis and is the fix. 3. **Check the migration.** The change that broke it is a `ALTER TABLE ... ADD COLUMN` on either side, quite possibly in a different service's migration set. It will not mention the report at all. ## Fixing it The immediate fix is to write the condition down: ```sql -- before SELECT order_id, SUM(line_total) AS order_total FROM orders NATURAL JOIN order_lines GROUP BY order_id; -- after: the join condition is now part of the query, not of the schema SELECT order_id, SUM(l.line_total) AS order_total FROM orders o JOIN order_lines l USING (order_id) GROUP BY order_id; ``` `USING` keeps the pleasant properties people liked about `NATURAL JOIN` — a short join clause and a single merged key column that `SELECT *` shows once — while pinning the condition. Where the two sides name the key differently, `ON o.order_id = l.order_ref` is the right answer instead. Note the crucial asymmetry: `USING (order_id)` **fails loudly** if `order_id` is later renamed or dropped, because the statement no longer compiles. `NATURAL JOIN` fails **silently**, because it just derives a different condition. In a system with migrations you want the failure mode that stops a deploy, not the one that changes a number on a dashboard. ## Preventing a repeat - **Ban `NATURAL JOIN` in reviewed code.** It is one grep (`NATURAL JOIN`) in a lint rule or a review checklist. Ad-hoc exploration against a schema you can see is the only fair use. - **Audit views and reports first.** A `NATURAL JOIN` inside a view is the worst case: the definition is re-derived, and consumers never see the join at all. - **Watch `SELECT *` in derived tables feeding a join.** If a subquery selects `*`, the columns it exposes — and therefore any natural join above it — depend on yet another table's shape. - **Add a row-count assertion to critical reports.** A report that can go to zero rows without failing should have a check that notices. - **Treat 'add an audit column everywhere' migrations as risky.** Adding the *same* name to many tables is precisely the change that maximises natural-join collateral damage. ## The general lesson An implicit join condition couples a query's meaning to the schema's naming, in a way no diff shows. Explicit `ON` or `USING` moves that meaning into the query text, where review, version control and compile-time errors can all see it.