Join / lookup · All operations

Look up with a query for each row

A variant of Look up values from a reference table

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

Status

In arcform

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.
  • The statement checks its own result and raises an error when it is wrong: “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”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

Apache DataFusion (3)
  • Correlated subqueriesread 2026-09-27

    A subquery that depends on the current outer row; rewritten into joins internally

  • LATERAL JOINread 2026-09-27

    Evaluate the right-hand subquery once per left row, referencing its columns

  • Scalar subqueriesread 2026-09-27

    A subquery returning one value, usable as an expression

Apache Hop (2)
  • Database Joinread 2026-09-27

    Run a parameterised SQL query per input row and join the returned rows to it

  • Dynamic SQL rowread 2026-09-27

    Run the SQL statement held in a field for each row and join its result

Pentaho Data Integration (2)
  • Database joinread 2026-09-27

    Run a database query with stream values as parameters and join the result

  • Dynamic SQL rowread 2026-09-27

    Run the SQL statement built in an earlier field for each row

Background

A correlated subquery is a subquery that uses values from the outer query, and it may be recomputed for each row of the outer query.[1] In PostgreSQL, a LATERAL subquery in FROM is evaluated with each row's values of the columns it refers to, and the rows it returns are joined to that row.[2] In DuckDB, lateral joins generalise correlated subqueries, because they can return multiple values per input value rather than only a single value.[3]

When to use it

In DataFusion, a LATERAL subquery written after a comma drops the left rows for which the subquery returns no rows, which is inner join semantics.[4] In DataFusion, LEFT JOIN LATERAL preserves every row of the left table and fills the right-side columns with NULLs when the subquery returns no rows.[4] In PostgreSQL, a LEFT JOIN to a LATERAL subquery makes source rows appear in the result even when the subquery produces no rows for them.[2] For a lookup, write LEFT JOIN LATERAL with ON true, so a row with nothing to look up keeps its place with an empty value. (Meridian's view)

In DataFusion, a scalar subquery returns a single value, one column of one row, and returns NULL when no rows are returned.[5] DataFusion's SELECT clause supports only scalar subqueries, returning a single value per execution of the inner query.[5] When the subquery can find several candidates, such as several prices in force, name the ordering that picks one and keep only that row. (Meridian's view)

Apache Hop's Database Join runs a query against a database with question-mark parameters that are replaced by fields of each incoming row.[6] Apache Hop's Database Join gives more flexibility than its Database Lookup, and its query determines the step's performance.[6] Apache Hop's Database Join can limit the number of rows each query returns, and can be set to always return a result even when the query returns none.[6]

The method

In PostgreSQL, a subquery in FROM preceded by LATERAL can reference columns of the FROM items before it; without LATERAL, each subquery is evaluated independently.[2] In PostgreSQL, the key word LATERAL is optional for a table function in FROM, whose arguments can refer to columns of earlier FROM items in any case.[2] DuckDB detects when a lateral join should be used, which makes the LATERAL keyword optional.[3] DataFusion requires the LATERAL keyword and does not detect correlation in FROM clause subqueries implicitly.[4]

In Microsoft SQL Server, the keywords CROSS APPLY or OUTER APPLY in place of JOIN let a correlated subquery join to the tables listed before it.[1]

DataFusion internally rewrites correlated subqueries into joins to improve performance.[5] In DataFusion, correlated subqueries are in general less performant than non-correlated subqueries.[5]

Compared with the alternatives

DuckDB's as-of join finds the nearest event in a reference table such as prices, and joins each left row with at most one right row.[3] When the only rule is the latest row on or before a date, an as-of join says so directly; a query per row earns its place when the rule needs more, such as several rows or extra filters. (Meridian's view) In DataFusion, a subquery in the SELECT clause can be used as an alternative to a JOIN.[5]

Sources

  1. [1]Correlated subquerysecondary source
  2. [2]PostgreSQL: Documentation: 18: 7.2. Table Expressionsdocumentation
  3. [3]FROM and JOIN Clauses – DuckDBdocumentation
  4. [4]SELECT syntax — Apache DataFusion main documentationdocumentation
  5. [5]Subqueries — Apache DataFusion main documentationdocumentation
  6. [6]Database Join | Apache Hopdocumentation