{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"a-spine-to-join-against","title":"Generating the rows that are not there","url":"https://addaly.com/learn/data-and-sql/a-spine-to-join-against","position":22,"of":66,"minutes":8,"answer":"GROUP BY cannot produce a row for a period in which nothing happened; generate the spine of expected rows, LEFT JOIN the facts to it, and count the fact column rather than *.","keyIdea":"GROUP BY cannot produce a row for a period in which nothing happened; generate the spine of expected rows, LEFT JOIN the facts to it, and count the fact column rather than `*`."},"body":{"format":"markdown","text":"## The zero that never appears\n\nA grouped query can only report on rows that exist. In March, the Kochi branch made no sales. `GROUP BY branch, month` therefore produces no pile for Kochi in March, and no row.\n\nThe consequences are worse than a missing line in a table. A chart draws a straight line from February to April, implying steady trade through a month with none. A month-on-month growth calculation compares March to January without saying so. A report emailed to a regional manager lacks the row that would have prompted a question.\n\nMissing rows are much harder to notice than wrong numbers, because nothing is there to look wrong.\n\n## Generate the frame first\n\nThe fix is to build the complete set of rows you want (the **spine**) and left join the data onto it. In Postgres:\n\n> Figure: The month with no sales, and how to make it appear. GROUP BY branch, month: Kochi sold nothing in March, so there is no pile and no row; The chart draws a straight line from February to April; Month-on-month growth compares March with January without saying so; The regional manager never sees the row worth asking about. Spine LEFT JOIN facts: generate_series produces six months whether or not anything happened; CROSS JOIN branches gives every branch-month combination; The date test sits in ON, not WHERE, or the spine rows are discarded again; count(o.id), not count(*), so an empty month reports zero and not one. A grouped query can only report on rows that exist, so a branch with no sales in March produces no pile and therefore no row. Missing rows are much harder to notice than wrong numbers, because nothing is there to look wrong.\n\n```sql\nSELECT generate_series(date '2026-01-01', date '2026-12-01', interval '1 month')::date AS month;\n```\n\nTwelve rows, whether or not anything happened in them. Now cross join it with the branches to get every combination:\n\n```sql\nWITH months AS (\n  SELECT generate_series(date '2026-01-01', date '2026-06-01', interval '1 month')::date AS month\n),\nspine AS (\n  SELECT b.id AS branch_id, b.name, m.month\n  FROM branches b CROSS JOIN months m\n)\nSELECT\n  s.name,\n  s.month,\n  coalesce(sum(o.amount), 0) AS revenue,\n  count(o.id)                AS orders\nFROM spine s\nLEFT JOIN orders o\n       ON o.branch_id = s.branch_id\n      AND o.placed_at >= s.month\n      AND o.placed_at <  s.month + interval '1 month'\nGROUP BY s.name, s.month\nORDER BY s.name, s.month;\n```\n\nRead the three parts that make it work.\n\nThe `CROSS JOIN` is deliberate: this is the one place a Cartesian product is exactly what you want. Six months times four branches is twenty-four rows, guaranteed.\n\nThe date condition sits in the `ON` clause, not in `WHERE`. This is the LEFT JOIN trap from the joins lesson: a `WHERE o.placed_at >= ...` would discard the spine rows that matched nothing, undoing the entire exercise.\n\n`coalesce(sum(o.amount), 0)` turns the NULL from an empty pile into a zero. And note `count(o.id)`, not `count(*)`: the unmatched spine row still produces one output row, so `count(*)` would report one order in a month with none.\n\n## Portable ways to make a spine\n\n`generate_series` is Postgres and DuckDB. Elsewhere:\n\n**A recursive CTE** works in SQLite, MySQL 8 and SQL Server, each with its own date arithmetic. In SQLite:\n\n```sql\nWITH RECURSIVE months(m) AS (\n  SELECT date('2026-01-01')\n  UNION ALL\n  SELECT date(m, '+1 month') FROM months WHERE m < '2026-06-01'\n)\nSELECT * FROM months;\n```\n\n**A permanent calendar table** is the approach most data warehouses take, and it is better than either. One row per date from 2015 to 2035, with columns for the month, quarter, fiscal year, weekday, and whether it is a public holiday in each market you operate in. Ten minutes to build, and every date question afterwards becomes a join instead of an argument about fiscal quarters.\n\n```sql\nCREATE TABLE calendar AS\nSELECT d::date AS day,\n       date_trunc('month', d)::date AS month_start,\n       extract(isodow FROM d)       AS weekday,\n       extract(isodow FROM d) >= 6  AS is_weekend\nFROM generate_series(date '2015-01-01', date '2035-12-31', interval '1 day') d;\n```\n\nThe holiday column cannot be computed and has to be loaded, which is exactly why having the table matters: it gives holidays somewhere to live, instead of being hard-coded into whichever query needed them last.\n\n## The same trick for categories\n\nSpines are not only for dates. Any report that must show every category, including the empty ones, is the same shape: start from the dimension table, left join the facts.\n\n```sql\nSELECT c.title, count(e.student_id) AS students\nFROM courses c\nLEFT JOIN enrolments e ON e.course_id = c.id\nGROUP BY c.title;\n```\n\nCourses with nobody enrolled appear with zero, which is very often the row somebody actually needs to see.\n\n## The judgement call\n\nNot every gap should be filled with a zero. A missing month for a branch that opened in April is not zero revenue, it is *not applicable*, and printing zero implies a business that existed and sold nothing. Bound the spine by the entity's own lifetime:\n\n```sql\nFROM branches b\nCROSS JOIN months m\nWHERE m.month >= date_trunc('month', b.opened_on)\n  AND (b.closed_on IS NULL OR m.month <= date_trunc('month', b.closed_on))\n```\n\nThe distinction between \"zero happened\" and \"the question does not apply\" is real, and it is the same distinction as zero versus NULL from the earlier lesson. A spine gives you the power to assert one of them; use it to assert the one that is true."},"check":{"question":"A report LEFT JOINs a generated branch-by-month spine to `orders` and shows an order count of 1 in months where a branch had no orders at all, with revenue correctly shown as 0. What is wrong?","options":[{"text":"The LEFT JOIN should be a FULL OUTER JOIN so unmatched order rows also appear.","correct":false,"tempting":"Would add rows for orders with no matching spine entry, which is a different problem and does not affect the count in empty months."},{"text":"`coalesce` was applied to the revenue but not to the order count, so NULL is being displayed as 1.","correct":false,"tempting":"COALESCE turns NULL into a chosen value; without it the count would show NULL or 0, never 1."},{"text":"The count is `count(*)`, which counts the NULL-filled row from the unmatched spine entry, instead of `count(o.id)`, which counts only real orders.","correct":true},{"text":"The spine contains a duplicate row for each empty month, so one phantom order is being counted per duplicate even though nothing was sold in that month.","correct":false,"tempting":"A CROSS JOIN of distinct branches and distinct months cannot produce duplicates, and duplicates would inflate the revenue as well."}]},"prev":{"slug":"common-table-expressions","title":"CTEs: naming the steps","url":"https://addaly.com/learn/data-and-sql/common-table-expressions"},"next":{"slug":"group-by","title":"GROUP BY: making piles and asking about each one","url":"https://addaly.com/learn/data-and-sql/group-by"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Generating the rows that are not there', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/a-spine-to-join-against (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Generating the rows that are not there","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":2,"slug":"putting-tables-together","title":"Putting tables together"},"answer":null,"keyIdea":"GROUP BY cannot produce a row for a period in which nothing happened; generate the spine of expected rows, LEFT JOIN the facts to it, and count the fact column rather than `*`.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"left":{"title":"GROUP BY branch, month","items":["Kochi sold nothing in March, so there is no pile and no row","The chart draws a straight line from February to April","Month-on-month growth compares March with January without saying so","The regional manager never sees the row worth asking about"]},"type":"compare","right":{"title":"Spine LEFT JOIN facts","items":["generate_series produces six months whether or not anything happened","CROSS JOIN branches gives every branch-month combination","The date test sits in ON, not WHERE, or the spine rows are discarded again","count(o.id), not count(*), so an empty month reports zero and not one"]},"title":"The month with no sales, and how to make it appear","caption":"A grouped query can only report on rows that exist, so a branch with no sales in March produces no pile and therefore no row. Missing rows are much harder to notice than wrong numbers, because nothing is there to look wrong."},"alt":"Figure: The month with no sales, and how to make it appear. GROUP BY branch, month: Kochi sold nothing in March, so there is no pile and no row; The chart draws a straight line from February to April; Month-on-month growth compares March with January without saying so; The regional manager never sees the row worth asking about. Spine LEFT JOIN facts: generate_series produces six months whether or not anything happened; CROSS JOIN branches gives every branch-month combination; The date test sits in ON, not WHERE, or the spine rows are discarded again; count(o.id), not count(*), so an empty month reports zero and not one. A grouped query can only report on rows that exist, so a branch with no sales in March produces no pile and therefore no row. Missing rows are much harder to notice than wrong numbers, because nothing is there to look wrong."}},"blocks":[{"t":"h2","id":"the-zero-that-never-appears","runs":[{"text":"The zero that never appears"}]},{"t":"p","runs":[{"text":"A grouped query can only report on rows that exist. In March, the Kochi branch made no sales. "},{"text":"GROUP BY branch, month","code":true},{"text":" therefore produces no pile for Kochi in March, and no row."}]},{"t":"p","runs":[{"text":"The consequences are worse than a missing line in a table. A chart draws a straight line from February to April, implying steady trade through a month with none. A month-on-month growth calculation compares March to January without saying so. A report emailed to a regional manager lacks the row that would have prompted a question."}]},{"t":"p","runs":[{"text":"Missing rows are much harder to notice than wrong numbers, because nothing is there to look wrong."}]},{"t":"h2","id":"generate-the-frame-first","runs":[{"text":"Generate the frame first"}]},{"t":"p","runs":[{"text":"The fix is to build the complete set of rows you want (the "},{"text":"spine","b":true},{"text":") and left join the data onto it. In Postgres:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT generate_series(date '2026-01-01', date '2026-12-01', interval '1 month')::date AS month;"},{"t":"p","runs":[{"text":"Twelve rows, whether or not anything happened in them. Now cross join it with the branches to get every combination:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH months AS (\n  SELECT generate_series(date '2026-01-01', date '2026-06-01', interval '1 month')::date AS month\n),\nspine AS (\n  SELECT b.id AS branch_id, b.name, m.month\n  FROM branches b CROSS JOIN months m\n)\nSELECT\n  s.name,\n  s.month,\n  coalesce(sum(o.amount), 0) AS revenue,\n  count(o.id)                AS orders\nFROM spine s\nLEFT JOIN orders o\n       ON o.branch_id = s.branch_id\n      AND o.placed_at >= s.month\n      AND o.placed_at <  s.month + interval '1 month'\nGROUP BY s.name, s.month\nORDER BY s.name, s.month;"},{"t":"p","runs":[{"text":"Read the three parts that make it work."}]},{"t":"p","runs":[{"text":"The "},{"text":"CROSS JOIN","code":true},{"text":" is deliberate: this is the one place a Cartesian product is exactly what you want. Six months times four branches is twenty-four rows, guaranteed."}]},{"t":"p","runs":[{"text":"The date condition sits in the "},{"text":"ON","code":true},{"text":" clause, not in "},{"text":"WHERE","code":true},{"text":". This is the LEFT JOIN trap from the joins lesson: a "},{"text":"WHERE o.placed_at >= ...","code":true},{"text":" would discard the spine rows that matched nothing, undoing the entire exercise."}]},{"t":"p","runs":[{"text":"coalesce(sum(o.amount), 0)","code":true},{"text":" turns the NULL from an empty pile into a zero. And note "},{"text":"count(o.id)","code":true},{"text":", not "},{"text":"count(*)","code":true},{"text":": the unmatched spine row still produces one output row, so "},{"text":"count(*)","code":true},{"text":" would report one order in a month with none."}]},{"t":"h2","id":"portable-ways-to-make-a-spine","runs":[{"text":"Portable ways to make a spine"}]},{"t":"p","runs":[{"text":"generate_series","code":true},{"text":" is Postgres and DuckDB. Elsewhere:"}]},{"t":"p","runs":[{"text":"A recursive CTE","b":true},{"text":" works in SQLite, MySQL 8 and SQL Server, each with its own date arithmetic. In SQLite:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH RECURSIVE months(m) AS (\n  SELECT date('2026-01-01')\n  UNION ALL\n  SELECT date(m, '+1 month') FROM months WHERE m < '2026-06-01'\n)\nSELECT * FROM months;"},{"t":"p","runs":[{"text":"A permanent calendar table","b":true},{"text":" is the approach most data warehouses take, and it is better than either. One row per date from 2015 to 2035, with columns for the month, quarter, fiscal year, weekday, and whether it is a public holiday in each market you operate in. Ten minutes to build, and every date question afterwards becomes a join instead of an argument about fiscal quarters."}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE TABLE calendar AS\nSELECT d::date AS day,\n       date_trunc('month', d)::date AS month_start,\n       extract(isodow FROM d)       AS weekday,\n       extract(isodow FROM d) >= 6  AS is_weekend\nFROM generate_series(date '2015-01-01', date '2035-12-31', interval '1 day') d;"},{"t":"p","runs":[{"text":"The holiday column cannot be computed and has to be loaded, which is exactly why having the table matters: it gives holidays somewhere to live, instead of being hard-coded into whichever query needed them last."}]},{"t":"h2","id":"the-same-trick-for-categories","runs":[{"text":"The same trick for categories"}]},{"t":"p","runs":[{"text":"Spines are not only for dates. Any report that must show every category, including the empty ones, is the same shape: start from the dimension table, left join the facts."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT c.title, count(e.student_id) AS students\nFROM courses c\nLEFT JOIN enrolments e ON e.course_id = c.id\nGROUP BY c.title;"},{"t":"p","runs":[{"text":"Courses with nobody enrolled appear with zero, which is very often the row somebody actually needs to see."}]},{"t":"h2","id":"the-judgement-call","runs":[{"text":"The judgement call"}]},{"t":"p","runs":[{"text":"Not every gap should be filled with a zero. A missing month for a branch that opened in April is not zero revenue, it is "},{"text":"not applicable","i":true},{"text":", and printing zero implies a business that existed and sold nothing. Bound the spine by the entity’s own lifetime:"}]},{"t":"code","lang":"sql","label":"SQL","text":"FROM branches b\nCROSS JOIN months m\nWHERE m.month >= date_trunc('month', b.opened_on)\n  AND (b.closed_on IS NULL OR m.month <= date_trunc('month', b.closed_on))"},{"t":"p","runs":[{"text":"The distinction between “zero happened” and “the question does not apply” is real, and it is the same distinction as zero versus NULL from the earlier lesson. A spine gives you the power to assert one of them; use it to assert the one that is true."}]}],"check":{"question":[{"text":"A report LEFT JOINs a generated branch-by-month spine to "},{"text":"orders","code":true},{"text":" and shows an order count of 1 in months where a branch had no orders at all, with revenue correctly shown as 0. What is wrong?"}],"options":[{"text":[{"text":"The LEFT JOIN should be a FULL OUTER JOIN so unmatched order rows also appear."}],"correct":false,"tempting":[{"text":"Would add rows for orders with no matching spine entry, which is a different problem and does not affect the count in empty months."}]},{"text":[{"text":"coalesce","code":true},{"text":" was applied to the revenue but not to the order count, so NULL is being displayed as 1."}],"correct":false,"tempting":[{"text":"COALESCE turns NULL into a chosen value; without it the count would show NULL or 0, never 1."}]},{"text":[{"text":"The count is "},{"text":"count(*)","code":true},{"text":", which counts the NULL-filled row from the unmatched spine entry, instead of "},{"text":"count(o.id)","code":true},{"text":", which counts only real orders."}],"correct":true,"tempting":null},{"text":[{"text":"The spine contains a duplicate row for each empty month, so one phantom order is being counted per duplicate even though nothing was sold in that month."}],"correct":false,"tempting":[{"text":"A CROSS JOIN of distinct branches and distinct months cannot produce duplicates, and duplicates would inflate the revenue as well."}]}],"order":[3,1,0,2],"why":null},"prev":{"slug":"common-table-expressions","title":"CTEs: naming the steps","minutes":7},"next":{"slug":"group-by","title":"GROUP BY: making piles and asking about each one","minutes":7}}}