# Look up with a query for each row

Join / lookup

A variant of [Look up values from a reference table](https://meridian.online/operations/look-up-values-from-a-reference-table).

Run a subquery or parameterised query for each input row and attach what it returns.

Page: <https://meridian.online/operations/look-up-values-from-a-reference-table/look-up-with-a-query-for-each-row>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

look up a price with a query run for each row

It reads the `prices` table the proofs share. Create it first:

```sql
CREATE TABLE prices AS SELECT * FROM (VALUES
  ('widget', DATE '2026-01-01', 10.0),
  ('widget', DATE '2026-01-10', 11.0),
  ('gadget', DATE '2026-01-01', 20.0),
  ('gizmo',  DATE '2026-01-15', 33.0)
) t(product, valid_from, unit_price);
```

Then run the statement:

```sql
CREATE TEMP TABLE o AS SELECT * FROM (VALUES
  (1, 'widget', DATE '2026-01-05'), (2, 'widget', DATE '2026-01-12'), (3, 'gizmo', DATE '2026-01-09'),
  (4, NULL, DATE '2026-01-12'), (5, 'gadget', DATE '2026-02-01')) t(id, product, order_date);
-- a subquery run for each row: the latest price in force, one at most
CREATE TEMP TABLE by_lateral AS
SELECT o.id, p.unit_price FROM o
LEFT JOIN LATERAL (SELECT unit_price FROM prices WHERE prices.product = o.product AND prices.valid_from <= o.order_date
                   ORDER BY valid_from DESC LIMIT 1) p ON true;
-- the same lookup as a correlated scalar subquery
CREATE TEMP TABLE by_scalar AS
SELECT o.id, (SELECT unit_price FROM prices WHERE prices.product = o.product AND prices.valid_from <= o.order_date
              ORDER BY valid_from DESC LIMIT 1) AS unit_price FROM o;
-- a parameterised query (a table macro) called for each row; it may return several rows and each one is kept
CREATE TEMP MACRO prices_up_to(p_product, p_day) AS TABLE
  SELECT unit_price FROM prices WHERE product = p_product AND valid_from <= p_day;
CREATE TEMP TABLE by_macro AS
SELECT o.id, m.unit_price FROM o LEFT JOIN LATERAL prices_up_to(o.product, o.order_date) m ON true;
SELECT * FROM by_lateral ORDER BY id;
SELECT CASE WHEN ((SELECT list(unit_price ORDER BY id) IS NOT DISTINCT FROM [10.0, 11.0, NULL, NULL, 20.0] FROM by_lateral)
  AND (SELECT list(unit_price ORDER BY id) IS NOT DISTINCT FROM [10.0, 11.0, NULL, NULL, 20.0] FROM by_scalar)
  AND (SELECT count(*) = 6 AND count(*) FILTER (WHERE id = 2) = 2 AND list(unit_price ORDER BY unit_price) FILTER (WHERE id = 2) = [10.0, 11.0]
       AND count(*) FILTER (WHERE id IN (3, 4) AND unit_price IS NULL) = 2 FROM by_macro)) THEN 'as expected' ELSE error('each order should take the latest price in force on its date (10, 11, none for the gizmo ordered before its first price, none for the order with no product, 20), by LATERAL and by a scalar subquery alike, and the macro should keep both prices in force for order 2') END AS verdict;
```

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Apache DataFusion: [`Correlated subqueries`](https://datafusion.apache.org/user-guide/sql/subqueries.html) (read 2026-09-27)
- Apache DataFusion: [`LATERAL JOIN`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- Apache DataFusion: [`Scalar subqueries`](https://datafusion.apache.org/user-guide/sql/subqueries.html) (read 2026-09-27)
- Apache Hop: [`Database Join`](https://hop.apache.org/manual/latest/pipeline/transforms/databasejoin.html) (read 2026-09-27)
- Apache Hop: [`Dynamic SQL row`](https://hop.apache.org/manual/latest/pipeline/transforms/dynamicsqlrow.html) (read 2026-09-27)
- Pentaho Data Integration: [`Database join`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)
- Pentaho Data Integration: [`Dynamic SQL row`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)