LATERAL Joins & generate_series

Two PostgreSQL features solve problems that are awkward in plain SQL. A LATERAL subquery can look at the current row of the table before it, so "for each customer, their two latest orders" becomes one clear query. generate_series creates rows out of nothing: a list of numbers or dates that you can join to, so months with no sales still appear in a report.

In this lesson you will use both, and combine them for a gap-free monthly report and quick test data.

What LATERAL does

A normal subquery in FROM is calculated on its own; it cannot refer to other tables in the same FROM list. Put LATERAL before it, and the subquery runs once for every row of the tables to its left, and can use that row's columns. Think of it as a for each loop inside SQL:

SELECT c.first_name, recent.order_id, recent.order_date, recent.status
FROM customers AS c
CROSS JOIN LATERAL (
  SELECT o.order_id, o.order_date, o.status
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
  ORDER BY o.order_date DESC
  LIMIT 2
) AS recent
WHERE c.customer_id IN (1, 2, 3)
ORDER BY c.first_name, recent.order_date DESC;

first_name | order_id | order_date | status
-----------+----------+------------+----------
Aarav      | 115      | 2025-05-20 | pending
Aarav      | 104      | 2025-02-02 | delivered
Priya      | 109      | 2025-03-22 | delivered
Priya      | 102      | 2025-01-12 | delivered
Rohan      | 113      | 2025-05-02 | shipped
Rohan      | 103      | 2025-01-20 | delivered
(6 rows)

For each customer, the subquery returns up to two of their newest orders. LIMIT inside the subquery works per customer, which is something you cannot do with a plain join. With a suitable index on orders (customer_id, order_date), this pattern is also very fast on big tables, because each lookup reads only a few index entries.

Keeping rows with LEFT JOIN LATERAL

CROSS JOIN LATERAL drops customers for whom the subquery returns nothing. To keep them, use LEFT JOIN LATERAL ... ON true; the ON true is needed because the join condition is already inside the subquery:

SELECT c.first_name, c.city, last_order.order_date AS last_order_date, last_order.total
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT o.order_date, sum(oi.quantity * oi.unit_price) AS total
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  WHERE o.customer_id = c.customer_id
  GROUP BY o.order_id, o.order_date
  ORDER BY o.order_date DESC
  LIMIT 1
) AS last_order ON true
WHERE c.state = 'Maharashtra'
ORDER BY c.first_name;

first_name | city   | last_order_date | total
-----------+--------+-----------------+--------
Divya      | Pune   | NULL            | NULL
Rohan      | Mumbai | 2025-05-02      | 8999.00
(2 rows)

Divya from Pune has never ordered, so her columns from the subquery are NULL, but she is still listed. A LATERAL subquery can return several columns at once, which a subquery in the SELECT list cannot.

generate_series

generate_series(start, stop, step) is a set-returning function: it returns many rows, so you use it in FROM like a table. It works with numbers and with dates or timestamps (the step is then an interval):

SELECT n, n * n AS square
FROM generate_series(1, 9, 2) AS n;

SELECT d::date AS month_start
FROM generate_series(date '2025-01-01', date '2025-06-01', interval '1 month') AS d;

n | square
--+-------
1 | 1
3 | 9
5 | 25
7 | 49
9 | 81
(5 rows)

month_start
-----------
2025-01-01
2025-02-01
2025-03-01
2025-04-01
2025-05-01
2025-06-01
(6 rows)

The date version returns timestamps, so ::date turns them back into plain dates. Adding WITH ORDINALITY after a set-returning function adds a row-number column, which is useful with unnest on arrays.

Filling gaps in a report

The practice orders run from January to May 2025. A report for January to June built only from orders would simply have no June row, and a chart drawn from it would hide the drop to zero. Generate the months first, then LEFT JOIN the data to them:

SELECT m::date AS month,
       count(o.order_id) AS orders,
       count(o.order_id) FILTER (WHERE o.status = 'delivered') AS delivered
FROM generate_series(date '2025-01-01', date '2025-06-01', interval '1 month') AS m
LEFT JOIN orders AS o
  ON date_trunc('month', o.order_date) = m
GROUP BY m
ORDER BY m;

month      | orders | delivered
-----------+--------+----------
2025-01-01 | 3      | 3
2025-02-01 | 3      | 2
2025-03-01 | 3      | 2
2025-04-01 | 3      | 2
2025-05-01 | 3      | 1
2025-06-01 | 0      | 0
(6 rows)

June appears with 0 orders. Note count(o.order_id) rather than count(*): count(*) counts the joined row even when no order matched, giving 1 for June, while count(column) ignores NULLs. For money columns, remember that sum() over no rows is NULL, not 0, so wrap it as coalesce(sum(...), 0).

Making test data

To test an index or a slow query you need many rows, and generate_series makes thousands in one statement. Use arithmetic on the number to create varied, repeatable values:

CREATE TABLE test_students AS
SELECT g AS student_id,
       'Student ' || g AS name,
       (ARRAY['Rohtak', 'Jaipur', 'Pune', 'Kochi', 'Lucknow'])[1 + g % 5] AS city,
       40 + (g * 37) % 61 AS marks
FROM generate_series(1, 100000) AS g;

This creates 100,000 students spread evenly over five cities with marks from 40 to 100. You could use random() instead, but then every run gives different data, which makes results harder to compare. CREATE TABLE ... AS (often called CTAS) creates a table from a query's result.

Common mistakes

• Forgetting LATERAL. Without it, the subquery cannot see c.customer_id and you get "invalid reference to FROM-clause entry".
• Using CROSS JOIN LATERAL when rows must stay. Use LEFT JOIN LATERAL ... ON true.
• Counting with count(*) after a gap-filling LEFT JOIN. Empty periods show 1. Count a column from the joined table.
• Comparing a date with a timestamp series. Truncate both sides to the same type or period.

Key points

• LATERAL lets a subquery in FROM use columns from tables to its left, running once per row.
• CROSS JOIN LATERAL drops rows with no results; LEFT JOIN LATERAL ... ON true keeps them.
• LATERAL with ORDER BY ... LIMIT n is the classic top-N-per-row pattern.
• generate_series creates rows of numbers, dates or timestamps.
• Generate a calendar and LEFT JOIN data to it to show empty periods as zero.
• generate_series with arithmetic makes large, repeatable test tables.

SELECT d::date AS day, count(o.order_id) AS orders
FROM generate_series(date '2025-03-01', date '2025-03-07', interval '1 day') AS d
LEFT JOIN orders AS o ON o.order_date = d::date
GROUP BY d
ORDER BY d;