Fill an ID Range Against Customers — generate

The problem

Brightlane's data migration team needs to verify which customer IDs from 1 through 10 are present in the customers table.

Write a query to return every integer from 1 through 10 alongside the customer name if a customers record with that id is on file, or a missing value if no record was found.

Assumptions:

The customers table has one row per customer with an id and a name.
Every integer from 1 through 10 must appear in the result regardless of whether a customer with that id is on file.
For each integer, the name column carries the matching customer's name if the integer equals an existing customers.id, or a missing value otherwise.

Output:

Ten rows, one per integer from 1 through 10, with columns customer_id and name.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

WITH
  id_range AS (
    SELECT
      GENERATE_SERIES(1, 10, 1) AS n
  )
SELECT
  ir.n AS customer_id,
  c.name
FROM
  id_range ir
  LEFT JOIN customers c ON c.id = ir.n

The shape

The id_range CTE generates the integers 1 through 10 as ten rows, and the LEFT JOIN against customers attaches a name to each integer that matches an existing id. Integers with no matching customer keep their row in the output with NULL in name, which is the missing-value the migration team is auditing for.

Clause by clause

WITH id_range AS (SELECT generate_series(1, 10, 1) AS n) builds the integer spine. generate_series with numeric arguments and a step of 1 produces one row per integer in the closed range [1, 10].
SELECT ir.n AS customer_id, c.name returns the generated integer as customer_id alongside the matched customer's name. The alias on ir.n keeps the output column named after its domain meaning, not the spine's internal column.
FROM id_range ir LEFT JOIN customers c ON c.id = ir.n pairs each generated integer with the customer record whose id matches. The LEFT JOIN keeps every spine row, so integers without a matching customer surface with NULL in c.name.

Why this and not querying `customers` directly

Selecting straight from customers returns only the rows that exist; integers with no matching customer would never appear at all, defeating the purpose of the audit. The integer spine is what makes "this id is missing" a visible row rather than an absence. The same shape underlies the date-spine pattern: generate the universe of expected keys, then LEFT JOIN to the fact table and read the NULLs as gaps.

You practiced an integer spine with LEFT JOIN against a primary key — gaps in the key sequence appear as rows with a missing related value, the same shape as a date spine over a fact table.

Return every integer from `1` through `10` alongside the customer name if a `customers` record with that `id` is on file, or a missing value if no record was found

The shape

Clause by clause

Why this and not querying `customers` directly

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why this and not querying customers directly

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why this and not querying `customers` directly