Range joins, as-of joins and LATERAL

Most joins match equal keys. A range join matches a value to an interval instead: its ON clause compares with <, <=, >= or BETWEEN, as in “the fee band whose distance range contains 7.3 km”. An as-of join is a range join in time: for each event, the row that was valid at that instant, such as the price a product had when an order was placed. LATERAL lets a subquery in FROM use the columns of the row it is joined to, so a query can run a small search for every row, such as “this customer’s two latest orders”. These joins answer questions an equality join cannot, and they come with their own traps: boundaries that match two rows, periods that overlap, and engines that do not all accept the same syntax. PostgreSQL has LATERAL, MySQL has it from version 8.0.14, and SQLite does not, so the lesson also shows the correlated subquery that replaces it.

Run executes the examples in SQLite 3.49.1 here in the browser, with one exception: SQLite cannot parse the LATERAL example, so what PostgreSQL 18 and MySQL 8.4 return for it is described beside it.

Join on a range: delivery-fee bands

-- Delivery fee by distance band. Each band covers from_m up to, but not including, to_m.
CREATE TABLE fee_bands (from_m INTEGER NOT NULL, to_m INTEGER NOT NULL, fee_paise INTEGER NOT NULL);
INSERT INTO fee_bands VALUES (0, 2000, 1500), (2000, 5000, 2500), (5000, 10000, 4000), (10000, 15000, 6000);

CREATE TABLE deliveries (delivery_id INTEGER PRIMARY KEY, distance_m INTEGER NOT NULL);
INSERT INTO deliveries VALUES (1, 1200), (2, 2000), (3, 4990), (4, 5000), (5, 9800), (6, 14990), (7, 16500);

SELECT d.delivery_id,
       d.distance_m,
       b.from_m,
       b.to_m,
       b.fee_paise
FROM deliveries AS d
LEFT JOIN fee_bands AS b
       ON d.distance_m >= b.from_m
      AND d.distance_m <  b.to_m
ORDER BY d.delivery_id;

┌─────────────┬────────────┬────────┬───────┬───────────┐
│ delivery_id │ distance_m │ from_m │ to_m  │ fee_paise │
├─────────────┼────────────┼────────┼───────┼───────────┤
│ 1           │ 1200       │ 0      │ 2000  │ 1500      │
│ 2           │ 2000       │ 2000   │ 5000  │ 2500      │
│ 3           │ 4990       │ 2000   │ 5000  │ 2500      │
│ 4           │ 5000       │ 5000   │ 10000 │ 4000      │
│ 5           │ 9800       │ 5000   │ 10000 │ 4000      │
│ 6           │ 14990      │ 10000  │ 15000 │ 6000      │
│ 7           │ 16500      │        │       │           │
└─────────────┴────────────┴────────┴───────┴───────────┘

Each band covers the distances from from_m up to, but not including, to_m, so the join condition is two comparisons: d.distance_m >= b.from_m AND d.distance_m < b.to_m. A delivery of exactly 2,000 m falls in the second band, and one of exactly 5,000 m in the third. Every distance below 15,000 m finds exactly one band, because each band starts where the previous one ends. Delivery 7, at 16,500 m, lies beyond the last band; the LEFT JOIN keeps it with NULLs, which shows the gap in the bands instead of silently losing the delivery as an inner join would.

A range that includes its start and excludes its end is called half-open and is written [from, to). It is the form PostgreSQL’s built-in range types use by default, and it has a property worth the small oddness of an excluded end: ranges that follow each other meet without overlapping and without a gap, however precise the values are.

BETWEEN includes both ends

-- The same bands written with BETWEEN, which includes both ends: the boundaries match twice.
CREATE TABLE fee_bands (from_m INTEGER NOT NULL, to_m INTEGER NOT NULL, fee_paise INTEGER NOT NULL);
INSERT INTO fee_bands VALUES (0, 2000, 1500), (2000, 5000, 2500), (5000, 10000, 4000), (10000, 15000, 6000);

CREATE TABLE deliveries (delivery_id INTEGER PRIMARY KEY, distance_m INTEGER NOT NULL);
INSERT INTO deliveries VALUES (1, 1200), (2, 2000), (3, 4990), (4, 5000), (5, 9800), (6, 14990), (7, 16500);

SELECT d.delivery_id, d.distance_m, b.fee_paise
FROM deliveries AS d
JOIN fee_bands AS b ON d.distance_m BETWEEN b.from_m AND b.to_m
ORDER BY d.delivery_id, b.from_m;

-- A check worth keeping: every delivery must match exactly one band.
SELECT d.delivery_id, d.distance_m, count(b.fee_paise) AS bands
FROM deliveries AS d
LEFT JOIN fee_bands AS b ON d.distance_m BETWEEN b.from_m AND b.to_m
GROUP BY d.delivery_id, d.distance_m
HAVING count(b.fee_paise) <> 1
ORDER BY d.delivery_id;

┌─────────────┬────────────┬───────────┐
│ delivery_id │ distance_m │ fee_paise │
├─────────────┼────────────┼───────────┤
│ 1           │ 1200       │ 1500      │
│ 2           │ 2000       │ 1500      │
│ 2           │ 2000       │ 2500      │
│ 3           │ 4990       │ 2500      │
│ 4           │ 5000       │ 2500      │
│ 4           │ 5000       │ 4000      │
│ 5           │ 9800       │ 4000      │
│ 6           │ 14990      │ 6000      │
└─────────────┴────────────┴───────────┘
┌─────────────┬────────────┬───────┐
│ delivery_id │ distance_m │ bands │
├─────────────┼────────────┼───────┤
│ 2           │ 2000       │ 2     │
│ 4           │ 5000       │ 2     │
│ 7           │ 16500      │ 0     │
└─────────────┴────────────┴───────┘

x BETWEEN a AND b means x >= a AND x <= b in all three engines, so bands written with BETWEEN share their boundary points. Deliveries 2 and 4, at exactly 2,000 and 5,000 m, match two bands each and appear twice, so a sum of fees would charge them twice: the fan-out of the previous lesson, caused this time by the data. The second query is a check worth keeping for any table of bands. Every value must match exactly one band, and the check lists the values that match two (a shared boundary or an overlap) or none (a gap).

Closed ranges can be made to fit by ending each band one unit before the next starts, at 1,999 and 4,999, but that holds only while every value is a whole number of metres: a distance of 1,999.5 m would fall between two bands. Half-open ranges never have that problem.

As-of joins: the price at the time of the order

-- The list price of each order line when its order was placed, from a price history with valid_from and valid_to.
CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_paise INTEGER NOT NULL);  -- today's price
CREATE TABLE price_history (
  product_id  INTEGER NOT NULL REFERENCES products (product_id),
  valid_from  TEXT    NOT NULL,      -- the price applies from this instant ...
  valid_to    TEXT,                  -- ... up to, but not including, this one (NULL: still the price)
  price_paise INTEGER NOT NULL
);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, ordered_at TEXT NOT NULL);
CREATE TABLE order_lines (
  line_id    INTEGER PRIMARY KEY,
  order_id   INTEGER NOT NULL REFERENCES orders (order_id),
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  qty        INTEGER NOT NULL
);

INSERT INTO products VALUES (1, 'Basmati rice 1 kg', 15999), (2, 'Toor dal 500 g', 9450);
INSERT INTO price_history VALUES
  (1, '2026-01-01 00:00:00', '2026-03-01 00:00:00', 14999),
  (1, '2026-03-01 00:00:00', NULL,                  15999),
  (2, '2026-01-01 00:00:00', '2026-02-15 09:00:00',  8950),
  (2, '2026-02-15 09:00:00', NULL,                   9450);
INSERT INTO orders VALUES
  (101, '2026-02-15 08:59:59'), (102, '2026-02-28 23:59:59'), (103, '2026-03-01 00:00:00'), (104, '2026-03-10 18:45:00');
INSERT INTO order_lines VALUES (1, 101, 2, 3), (2, 102, 1, 2), (3, 102, 2, 1), (4, 103, 1, 1), (5, 104, 2, 2);

SELECT l.line_id,
       o.ordered_at,
       p.name,
       h.price_paise           AS price_then,
       p.price_paise           AS price_today,
       l.qty * h.price_paise   AS line_paise
FROM order_lines AS l
JOIN orders   AS o ON o.order_id   = l.order_id
JOIN products AS p ON p.product_id = l.product_id
JOIN price_history AS h
  ON h.product_id = l.product_id
 AND h.valid_from <= o.ordered_at
 AND (h.valid_to IS NULL OR o.ordered_at < h.valid_to)
ORDER BY l.line_id;

┌─────────┬─────────────────────┬───────────────────┬────────────┬─────────────┬────────────┐
│ line_id │     ordered_at      │       name        │ price_then │ price_today │ line_paise │
├─────────┼─────────────────────┼───────────────────┼────────────┼─────────────┼────────────┤
│ 1       │ 2026-02-15 08:59:59 │ Toor dal 500 g    │ 8950       │ 9450        │ 26850      │
│ 2       │ 2026-02-28 23:59:59 │ Basmati rice 1 kg │ 14999      │ 15999       │ 29998      │
│ 3       │ 2026-02-28 23:59:59 │ Toor dal 500 g    │ 9450       │ 9450        │ 9450       │
│ 4       │ 2026-03-01 00:00:00 │ Basmati rice 1 kg │ 15999      │ 15999       │ 15999      │
│ 5       │ 2026-03-10 18:45:00 │ Toor dal 500 g    │ 9450       │ 9450        │ 18900      │
└─────────┴─────────────────────┴───────────────────┴────────────┴─────────────┴────────────┘

A product’s price changes over time, and an order must be valued at the price in effect when it was placed, not at today’s price. A price history stores each price with the instant it started, valid_from, and the instant it stopped, valid_to, which is NULL for the price that still applies. The as-of join is a range join on time, together with the product: the order’s time must be at or after valid_from, and before valid_to unless valid_to is NULL.

The boundaries behave as half-open ranges should. The order at 2026-02-28 23:59:59 gets the old rice price, 14,999 paise, and the order at 2026-03-01 00:00:00 gets the new one, 15,999: the instant of a change belongs to the new price only. The dal price changed at 2026-02-15 09:00:00, and the order placed one second earlier still gets the old price. The price_today column shows what a report gets wrong when it joins today’s prices instead: two of the five lines would be valued at prices they were not sold at.

Timestamps stored as text compare as text, so they must all use one format. '2026-03-01T00:00:00' sorts after '2026-03-01 00:00:00', because the letter T comes after a space, and a history that mixes the two formats moves its boundaries. Keep every timestamp in one time zone as well, as the lesson on time zones and timestamps explains.

Only the moments of change

-- As-of lookup with only the moments prices changed: the latest change at or before the order.
CREATE TABLE price_changes (
  product_id  INTEGER NOT NULL,
  changed_at  TEXT    NOT NULL,
  price_paise INTEGER NOT NULL,
  PRIMARY KEY (product_id, changed_at)   -- also the index each lookup searches
);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, ordered_at TEXT NOT NULL);
CREATE TABLE order_lines (line_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, qty INTEGER NOT NULL);

INSERT INTO price_changes VALUES
  (1, '2026-01-01 00:00:00', 14999), (1, '2026-03-01 00:00:00', 15999),
  (2, '2026-01-01 00:00:00',  8950), (2, '2026-02-15 09:00:00',  9450);
INSERT INTO orders VALUES
  (100, '2025-12-31 23:00:00'), (101, '2026-02-15 08:59:59'), (102, '2026-02-28 23:59:59'),
  (103, '2026-03-01 00:00:00'), (104, '2026-03-10 18:45:00');
INSERT INTO order_lines VALUES (1, 101, 2, 3), (2, 102, 1, 2), (3, 102, 2, 1), (4, 103, 1, 1), (5, 104, 2, 2), (6, 100, 1, 1);

SELECT l.line_id,
       o.ordered_at,
       (SELECT pc.price_paise
        FROM price_changes AS pc
        WHERE pc.product_id = l.product_id
          AND pc.changed_at <= o.ordered_at
        ORDER BY pc.changed_at DESC
        LIMIT 1) AS price_then
FROM order_lines AS l
JOIN orders AS o ON o.order_id = l.order_id
ORDER BY l.line_id;

-- How SQLite finds each price: one search of the primary key's index per line.
EXPLAIN QUERY PLAN
SELECT l.line_id,
       (SELECT pc.price_paise
        FROM price_changes AS pc
        WHERE pc.product_id = l.product_id
          AND pc.changed_at <= o.ordered_at
        ORDER BY pc.changed_at DESC
        LIMIT 1) AS price_then
FROM order_lines AS l
JOIN orders AS o ON o.order_id = l.order_id;

┌─────────┬─────────────────────┬────────────┐
│ line_id │     ordered_at      │ price_then │
├─────────┼─────────────────────┼────────────┤
│ 1       │ 2026-02-15 08:59:59 │ 8950       │
│ 2       │ 2026-02-28 23:59:59 │ 14999      │
│ 3       │ 2026-02-28 23:59:59 │ 9450       │
│ 4       │ 2026-03-01 00:00:00 │ 15999      │
│ 5       │ 2026-03-10 18:45:00 │ 9450       │
│ 6       │ 2025-12-31 23:00:00 │            │
└─────────┴─────────────────────┴────────────┘
┌────┬────────┬─────────┬────────────────────────────────────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                                         detail                                         │
├────┼────────┼─────────┼────────────────────────────────────────────────────────────────────────────────────────┤
│ 3  │ 0      │ 216     │ SCAN l                                                                                 │
│ 5  │ 0      │ 45      │ SEARCH o USING INTEGER PRIMARY KEY (rowid=?)                                           │
│ 10 │ 0      │ 0       │ CORRELATED SCALAR SUBQUERY 1                                                           │
│ 20 │ 10     │ 51      │ SEARCH pc USING INDEX sqlite_autoindex_price_changes_1 (product_id=? AND changed_at<?) │
└────┴────────┴─────────┴────────────────────────────────────────────────────────────────────────────────────────┘

Many systems store only when a price changed, not when it stopped. The as-of lookup is then “the latest change at or before the order”: a correlated subquery takes the changes of the line’s product up to the order’s time, sorts them newest first and keeps one. Order 100, placed before the first price existed, finds no change and gets NULL, which is better than a wrong price. The lookup itself runs unchanged in SQLite, PostgreSQL and MySQL. The plan at the end shows how SQLite finds each price: SEARCH pc USING INDEX with product_id=? AND changed_at<?, one short search of the primary key’s index for each line instead of a scan of the whole history. A history keyed on (product_id, changed_at) also cannot hold two changes of one product at the same instant, which would make the answer ambiguous.

Overlapping periods count twice

-- A new price was added without closing the old one: two prices overlap, and the line is counted twice.
CREATE TABLE price_history (
  price_id    INTEGER PRIMARY KEY,
  product_id  INTEGER NOT NULL,
  valid_from  TEXT    NOT NULL,
  valid_to    TEXT,
  price_paise INTEGER NOT NULL
);
INSERT INTO price_history VALUES
  (1, 1, '2026-01-01 00:00:00', NULL,                  14999),   -- valid_to was never set
  (2, 1, '2026-03-01 00:00:00', NULL,                  15999),
  (3, 2, '2026-01-01 00:00:00', '2026-02-15 09:00:00',  8950),
  (4, 2, '2026-02-15 09:00:00', NULL,                   9450);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, ordered_at TEXT NOT NULL);
CREATE TABLE order_lines (line_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, qty INTEGER NOT NULL);
INSERT INTO orders VALUES (103, '2026-03-01 00:00:00');
INSERT INTO order_lines VALUES (4, 103, 1, 1);

SELECT l.line_id, h.price_id, h.price_paise
FROM order_lines AS l
JOIN orders AS o ON o.order_id = l.order_id
JOIN price_history AS h
  ON h.product_id = l.product_id
 AND h.valid_from <= o.ordered_at
 AND (h.valid_to IS NULL OR o.ordered_at < h.valid_to);

-- Two periods of one product overlap when each one starts before the other ends.
SELECT a.product_id, a.price_id AS first_price, b.price_id AS second_price
FROM price_history AS a
JOIN price_history AS b
  ON b.product_id = a.product_id
 AND a.price_id < b.price_id
 AND a.valid_from < coalesce(b.valid_to, '9999-12-31 23:59:59')
 AND b.valid_from < coalesce(a.valid_to, '9999-12-31 23:59:59');

┌─────────┬──────────┬─────────────┐
│ line_id │ price_id │ price_paise │
├─────────┼──────────┼─────────────┤
│ 4       │ 1        │ 14999       │
│ 4       │ 2        │ 15999       │
└─────────┴──────────┴─────────────┘
┌────────────┬─────────────┬──────────────┐
│ product_id │ first_price │ second_price │
├────────────┼─────────────┼──────────────┤
│ 1          │ 1           │ 2            │
└────────────┴─────────────┴──────────────┘

An as-of join assumes that a product has one price at each instant. When a new price is added without closing the old one, both periods contain the order’s time, the line matches twice and its revenue doubles, the same fan-out as two bands that share a boundary. The second query finds such pairs with the standard overlap test: two periods overlap when each one starts before the other ends, a.valid_from < b.valid_to AND b.valid_from < a.valid_to, with an open end read as a time far in the future. Periods that only touch, where one ends at the instant the next begins, correctly pass the test.

Finding overlaps is the minimum; refusing them is better. PostgreSQL can reject overlapping rows with an exclusion constraint and, in PostgreSQL 18, with WITHOUT OVERLAPS on a range column of a primary key or unique constraint, which the lesson on range and exclusion constraints covers. MySQL and SQLite have no such constraint, so there the check runs as a test or a trigger.

LATERAL: a subquery for each row

-- Each customer's two latest orders with LATERAL (PostgreSQL, and MySQL 8.0.14 or later).
-- SQLite has no LATERAL, so in this track's browser engine the query stops with a syntax error.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, placed_at TEXT NOT NULL);
INSERT INTO customers VALUES (1, 'Asha Kulkarni'), (2, 'Vikram Nair'), (3, 'Meera Rathore'), (4, 'Tenzin Dorjee');
INSERT INTO orders VALUES
  (101, 1, '2026-01-04 10:15:00'), (102, 2, '2026-01-19 19:40:00'), (103, 1, '2026-02-02 08:05:00'),
  (104, 3, '2026-02-27 21:30:00'), (105, 1, '2026-03-08 12:00:00'), (106, 3, '2026-03-09 09:45:00');

SELECT c.name, last_two.order_id, last_two.placed_at
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT o.order_id, o.placed_at
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
  ORDER BY o.placed_at DESC
  LIMIT 2
) AS last_two ON TRUE
ORDER BY c.customer_id, last_two.placed_at DESC;

Parse error near line 10: near "SELECT": syntax error

A subquery in FROM normally cannot see the other tables of the same FROM: it is computed once, on its own. With LATERAL in front, it may use the columns of the tables to its left, and it is computed again for each of their rows. That turns a per-row search into a join. For each customer, the subquery above sorts that customer’s orders newest first and keeps two, and LEFT JOIN … ON TRUE keeps the customers whose subquery returns nothing.

SQLite has no LATERAL: it reads the word as the name of a table and stops with a syntax error at the SELECT that follows, as the recorded output shows. PostgreSQL 18 and MySQL 8.4 run the query as written and return six rows: Asha’s orders 105 and 103, Vikram’s 102, Meera’s 106 and 104, and one row for Tenzin with NULL in the order columns. MySQL has supported lateral derived tables since version 8.0.14, and there, as for every derived table, the alias AS last_two is required.

A LATERAL subquery can return several columns and several rows for each outer row, and that sets it apart from a subquery in the SELECT list, which gives one value per row. All three engines refuse a second column there: PostgreSQL says the subquery “must return only one column”, MySQL stops with error 1241, and SQLite reports that the sub-select returns 2 columns. With a second row, PostgreSQL and MySQL stop with an error (error 1242 in MySQL) and SQLite quietly keeps the first row. The as-of price above, written with LATERAL, could return the price and the instant it was set together, from one search.

The same result without LATERAL

-- The same two latest orders per customer without LATERAL: a correlated subquery picks the orders to keep.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, placed_at TEXT NOT NULL);
INSERT INTO customers VALUES (1, 'Asha Kulkarni'), (2, 'Vikram Nair'), (3, 'Meera Rathore'), (4, 'Tenzin Dorjee');
INSERT INTO orders VALUES
  (101, 1, '2026-01-04 10:15:00'), (102, 2, '2026-01-19 19:40:00'), (103, 1, '2026-02-02 08:05:00'),
  (104, 3, '2026-02-27 21:30:00'), (105, 1, '2026-03-08 12:00:00'), (106, 3, '2026-03-09 09:45:00');

SELECT c.name, o.order_id, o.placed_at
FROM customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.customer_id
      AND o.order_id IN (SELECT o2.order_id
                         FROM orders AS o2
                         WHERE o2.customer_id = c.customer_id
                         ORDER BY o2.placed_at DESC
                         LIMIT 2)
ORDER BY c.customer_id, o.placed_at DESC;

┌───────────────┬──────────┬─────────────────────┐
│     name      │ order_id │      placed_at      │
├───────────────┼──────────┼─────────────────────┤
│ Asha Kulkarni │ 105      │ 2026-03-08 12:00:00 │
│ Asha Kulkarni │ 103      │ 2026-02-02 08:05:00 │
│ Vikram Nair   │ 102      │ 2026-01-19 19:40:00 │
│ Meera Rathore │ 106      │ 2026-03-09 09:45:00 │
│ Meera Rathore │ 104      │ 2026-02-27 21:30:00 │
│ Tenzin Dorjee │          │                     │
└───────────────┴──────────┴─────────────────────┘

Without LATERAL, the subquery moves into the join condition: keep an order when its id is among the two latest ids of its customer. IN (SELECT … ORDER BY … LIMIT 2) runs in SQLite and PostgreSQL, but MySQL refuses LIMIT inside an IN subquery with error 1235, so in MySQL use LATERAL, or the window function ROW_NUMBER(), which the lesson on top-N per group teaches and which all three engines support.

Mind ties. If two orders of one customer have the same placed_at, ORDER BY placed_at DESC LIMIT 2 picks between them arbitrarily, and the result can change from one run to the next. Add a unique tie-breaker, such as ORDER BY placed_at DESC, order_id DESC, whenever the order of the rows decides which rows you get.

Months as ranges of days

-- Sales joined to their month by the month's first day and the next month's first day.
CREATE TABLE months (month TEXT PRIMARY KEY, starts_on TEXT NOT NULL, next_starts_on TEXT NOT NULL);
INSERT INTO months VALUES
  ('2026-01', '2026-01-01', '2026-02-01'), ('2026-02', '2026-02-01', '2026-03-01'), ('2026-03', '2026-03-01', '2026-04-01');

CREATE TABLE sales (order_id INTEGER, placed_on TEXT NOT NULL, qty INTEGER NOT NULL, price_paise INTEGER NOT NULL);
CREATE INDEX sales_by_day ON sales (placed_on);
INSERT INTO sales VALUES
  (201, '2026-01-04', 2, 14999), (202, '2026-01-31', 1, 8950), (203, '2026-02-01', 1, 6800),
  (204, '2026-02-28', 3, 8950), (205, '2026-03-31', 1, 12000), (206, '2026-04-01', 2, 21500);

SELECT m.month,
       count(s.order_id) AS sales,
       coalesce(sum(s.qty * s.price_paise), 0) AS revenue_paise
FROM months AS m
LEFT JOIN sales AS s
       ON s.placed_on >= m.starts_on
      AND s.placed_on <  m.next_starts_on
GROUP BY m.month
ORDER BY m.month;

-- How SQLite finds each month's sales: with the range, a search in the index on placed_on ...
EXPLAIN QUERY PLAN
SELECT m.month, count(s.order_id)
FROM months AS m
LEFT JOIN sales AS s ON s.placed_on >= m.starts_on AND s.placed_on < m.next_starts_on
GROUP BY m.month;

-- ... and with substr(), which the index cannot serve, a scan of every sale for every month.
EXPLAIN QUERY PLAN
SELECT m.month, count(s.order_id)
FROM months AS m
LEFT JOIN sales AS s ON substr(s.placed_on, 1, 7) = m.month
GROUP BY m.month;

┌─────────┬───────┬───────────────┐
│  month  │ sales │ revenue_paise │
├─────────┼───────┼───────────────┤
│ 2026-01 │ 2     │ 38948         │
│ 2026-02 │ 2     │ 33650         │
│ 2026-03 │ 1     │ 12000         │
└─────────┴───────┴───────────────┘
┌────┬────────┬─────────┬───────────────────────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                                  detail                                   │
├────┼────────┼─────────┼───────────────────────────────────────────────────────────────────────────┤
│ 9  │ 0      │ 224     │ SCAN m USING INDEX sqlite_autoindex_months_1                              │
│ 12 │ 0      │ 165     │ SEARCH s USING INDEX sales_by_day (placed_on>? AND placed_on<?) LEFT-JOIN │
└────┴────────┴─────────┴───────────────────────────────────────────────────────────────────────────┘
┌────┬────────┬─────────┬───────────────────────────────────────────────────────┐
│ id │ parent │ notused │                        detail                         │
├────┼────────┼─────────┼───────────────────────────────────────────────────────┤
│ 7  │ 0      │ 212     │ SCAN m USING COVERING INDEX sqlite_autoindex_months_1 │
│ 9  │ 0      │ 216     │ SCAN s LEFT-JOIN                                      │
└────┴────────┴─────────┴───────────────────────────────────────────────────────┘

The report grid of the lesson on cross joins matched each sale to its month with substr(s.placed_on, 1, 7) = m.month. Giving every month its first day and the first day of the next month turns that into a range join, s.placed_on >= m.starts_on AND s.placed_on < m.next_starts_on, with the same half-open rule as the fee bands: a sale on the last day of a month stays in it, and a sale on the first day of the next month goes to the next. The two plans at the end show why the form matters. With the range, SQLite searches the index on placed_on once for each month (SEARCH s USING INDEX sales_by_day); with substr(), the index cannot help, and SQLite scans every sale for every month (SCAN s). On a table that holds years of sales, that is the difference between reading one quarter and reading all of them, as many times as there are months.

How the engines differ

Window functions give a third way to write these lookups without a correlated subquery: number the rows of each customer to keep the top two, or pair each price change with the start of the next one using LEAD() to build valid_to. The module on window functions covers both.

Your turn

-- The list price of each order line at the moment its order was placed.
SELECT l.line_id,
       h.price_paise
FROM order_lines AS l
JOIN price_history AS h ON h.product_id = l.product_id;

# Sample tests: each one builds the three tables, runs your query on them and compares the rows it returns (in any
# order).
tests:
  - name: each line gets the price in effect when its order was placed
    setup: |
      CREATE TABLE orders (order_id INTEGER PRIMARY KEY, ordered_at TEXT NOT NULL);
      CREATE TABLE order_lines (line_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, qty INTEGER NOT NULL);
      CREATE TABLE price_history (product_id INTEGER NOT NULL, valid_from TEXT NOT NULL, valid_to TEXT, price_paise INTEGER NOT NULL);
      INSERT INTO price_history VALUES
        (1, '2026-01-01 00:00:00', '2026-02-01 00:00:00', 1000),
        (1, '2026-02-01 00:00:00', NULL, 1200),
        (2, '2026-01-01 00:00:00', NULL, 500);
      INSERT INTO orders VALUES (1, '2026-01-15 10:00:00'), (2, '2026-02-10 18:30:00');
      INSERT INTO order_lines VALUES (1, 1, 1, 2), (2, 1, 2, 1), (3, 2, 1, 1), (4, 2, 2, 3);
    expected:
      columns: [line_id, price_paise]
      rows:
        - [1, 1000]
        - [2, 500]
        - [3, 1200]
        - [4, 500]
  - name: an order placed at the exact moment of a change gets the new price
    setup: |
      CREATE TABLE orders (order_id INTEGER PRIMARY KEY, ordered_at TEXT NOT NULL);
      CREATE TABLE order_lines (line_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, qty INTEGER NOT NULL);
      CREATE TABLE price_history (product_id INTEGER NOT NULL, valid_from TEXT NOT NULL, valid_to TEXT, price_paise INTEGER NOT NULL);
      INSERT INTO price_history VALUES
        (7, '2026-01-01 00:00:00', '2026-03-01 00:00:00', 14999),
        (7, '2026-03-01 00:00:00', '2026-03-15 12:00:00', 15999),
        (7, '2026-03-15 12:00:00', NULL, 15499);
      INSERT INTO orders VALUES
        (1, '2026-02-28 23:59:59'), (2, '2026-03-01 00:00:00'), (3, '2026-03-15 11:59:59'), (4, '2026-03-15 12:00:00');
      INSERT INTO order_lines VALUES (1, 1, 7, 1), (2, 2, 7, 1), (3, 3, 7, 1), (4, 4, 7, 1);
    expected:
      columns: [line_id, price_paise]
      rows:
        - [1, 14999]
        - [2, 15999]
        - [3, 15999]
        - [4, 15499]
  - name: a line with no price in effect is listed once, with NULL
    setup: |
      CREATE TABLE orders (order_id INTEGER PRIMARY KEY, ordered_at TEXT NOT NULL);
      CREATE TABLE order_lines (line_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, qty INTEGER NOT NULL);
      CREATE TABLE price_history (product_id INTEGER NOT NULL, valid_from TEXT NOT NULL, valid_to TEXT, price_paise INTEGER NOT NULL);
      INSERT INTO price_history VALUES
        (3, '2026-02-01 00:00:00', NULL, 800),
        (4, '2026-01-01 00:00:00', '2026-01-31 00:00:00', 300),
        (4, '2026-02-01 00:00:00', NULL, 350);
      INSERT INTO orders VALUES (1, '2026-01-20 09:00:00'), (2, '2026-01-31 12:00:00');
      INSERT INTO order_lines VALUES (1, 1, 3, 1), (2, 2, 4, 2), (3, 1, 4, 1);
    expected:
      columns: [line_id, price_paise]
      rows:
        - [1, null]
        - [2, null]
        - [3, 300]

Interview questions

Fresher · What is a range join, and when can an equality join not answer the question?

A range join, also called a non-equi join, matches rows with a comparison other than equality, usually a value against the two ends of a range: ON d.distance_m >= b.from_m AND d.distance_m < b.to_m. An equality join needs both sides to hold the same key, so it cannot answer questions such as “which fee band contains a distance of 7,350 m”, “which price was in effect when this order was placed” or “which shift was running at this timestamp”, because the value you look up almost never equals a stored boundary. Write the ranges half-open, including the start and excluding the end, so that a value on a boundary matches exactly one range, and check that the ranges neither overlap nor leave gaps, since either one silently duplicates or drops rows.

Key takeaways

• A range join compares a value with the two ends of a range in ON; it answers questions that equality cannot.
• Write ranges half-open, [from, to): a boundary value then matches exactly one range, and ranges that follow each other neither overlap nor leave a gap.
• BETWEEN includes both ends, so ranges written with it share their boundaries and count those values twice.
• An as-of join finds the row valid at an instant: valid_from <= t AND (valid_to IS NULL OR t < valid_to), or the latest change at or before t, which an index on (key, changed_at) finds in one search.
• Overlapping periods duplicate rows. Find them with a.from < b.to AND b.from < a.to, and refuse them with a constraint where the engine has one.
• LATERAL runs a subquery for each row and may return several rows and columns. PostgreSQL and MySQL 8.0.14 and later have it; SQLite does not, so use a correlated subquery there, and remember that MySQL refuses LIMIT inside IN (…).