Acquire / database · All operations

Query a database for each input row

A variant of Read from a database

Run a query once per input row, or with keys taken from an input table, and return the matching database rows.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today, with the sqlite_scanner extension.

The DuckDB step

Run a query for each input row and look up the rows for a table of keys

It reads the orders table the proofs share. Create it first:

SQL

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL

INSTALL sqlite; LOAD sqlite;
ATTACH '<your_scratch_dir>/store.sqlite' AS store (TYPE sqlite);
CREATE TABLE store.orders AS SELECT order_id, customer, amount::DOUBLE AS amount FROM orders;
DETACH store;
ATTACH '<your_scratch_dir>/store.sqlite' AS store (TYPE sqlite, READ_ONLY);
CREATE TEMP TABLE keys AS SELECT * FROM (VALUES ('Alice Ng'), ('Dev Patel'), ('Nobody')) t(customer);
-- the matching rows of the database, for keys taken from an input table
CREATE TEMP TABLE matching AS
SELECT s.order_id, s.customer, s.amount FROM store.orders s WHERE s.customer IN (SELECT customer FROM keys);
-- a query run once for each input row, with that row's key in it
CREATE TEMP TABLE per_row AS
SELECT k.customer, r.n, r.total
FROM keys k, LATERAL (SELECT count(*) AS n, sum(amount) AS total FROM store.orders s WHERE s.customer = k.customer) r;
SELECT * FROM matching ORDER BY order_id; SELECT * FROM per_row ORDER BY customer;
SELECT CASE WHEN ((SELECT list(order_id ORDER BY order_id) FROM matching) = [1, 6, 8, 10] AND (SELECT list(n ORDER BY customer) FROM per_row) = [2, 2, 0] AND (SELECT total FROM per_row WHERE customer = 'Alice Ng') = 340 AND (SELECT total FROM per_row WHERE customer = 'Nobody') IS NULL) THEN 'as expected' ELSE error('the keys did not return orders 1, 6, 8 and 10, or the per-row query did not count 2, 2 and 0 orders') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

The statement writes store.sqlite itself, in <your_scratch_dir>.
It needs the sqlite_scanner extension.
The statement checks its own result and raises an error when it is wrong: “the keys did not return orders 1, 6, 8 and 10, or the per-row query did not count 2, 2 and 0 orders”.
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).

EasyMorph (1)

Select matching database rowsread 2026-09-27
Read database rows whose key fields match, or do not match, keys held in an EasyMorph table

KNIME Analytics Platform (2)

DB Looperread 2026-09-27
Run a query repeatedly with IN-lists filled from input table values and collect the rows
DB Parameterized Query Readerread 2026-09-27
Run a parameterised query once per input row and return the combined results

Talend (1)

Database lookup input components (family)read 2026-09-27
11 components feeding lookup flows in Spark Streaming Jobs: tMysqlLookupInput, tOracleLookupInput, tJDBCLookupInput

Background

In KNIME Analytics Platform, the DB Looper node runs SQL queries restricted by values from an input table, so only matching database rows are retrieved.^[1] EasyMorph's Select matching database rows action selects rows of a database table whose key fields match the key fields of an EasyMorph table.^[2]

When to use it

EasyMorph's Select matching database rows action can be used for pre-filtering: a few fields and their keys are imported and filtered first, and the remaining keys are used to fetch full records.^[2] In EasyMorph, pre-filtering can be helpful when the source table is large and does not entirely fit in memory.^[2] EasyMorph's Select matching database rows action also serves cross-source filtering, importing database records whose keys come from another source, such as a text file or a spreadsheet.^[2]

In KNIME Analytics Platform, the DB Looper node sends the values of a set number of input rows with each query, set by its No of values per query parameter.^[1] When the input rows do not fill the last query, the KNIME Analytics Platform DB Looper node uses NULL for the missing values in that query.^[1] The KNIME Analytics Platform DB Looper node also has a Read all option that executes a single query with all values of the selected columns from the input table.^[1] Send keys in batches rather than one query per row where the database allows it, and keep each batch within what the database accepts in one statement. (Meridian's view)

In the KNIME Analytics Platform DB Parameterized Query Reader node, the Include empty results option keeps all input rows, representing an empty result as a missing cell.^[3] With Fail on error unchecked, the KNIME Analytics Platform DB Parameterized Query Reader node keeps executing and collects all errors in its second output table.^[3] Decide whether a key with no match should drop out or stay as an empty row, and check the error output before trusting the row count. (Meridian's view)

The method

In KNIME Analytics Platform, the DB Looper node is usually used for IN queries such as SELECT * FROM table WHERE Col1 IN ($Col1_values$), where the placeholder becomes a comma-separated list of input values.^[1] EasyMorph's Select matching database rows action creates a series of SQL requests, combines their results into one table, and can work with read-only access to the database.^[2]

In DuckDB, the LATERAL keyword allows a subquery in the FROM clause to refer to previous subqueries, a feature also known as a lateral join.^[4] A DuckDB lateral join can be thought of as a loop over the rows of the first subquery, each used as input to the LATERAL subquery.^[4] In DuckDB, lateral joins are a generalisation of correlated subqueries, because they can return multiple values per input value rather than only one.^[4] DuckDB detects when a lateral join should be used, which makes the LATERAL keyword optional.^[4]

Compared with the alternatives

In DuckDB, a semi join returns the rows of the left table that have at least one match in the right table, the same logic as the IN operator.^[4] In DuckDB, a semi join never returns more rows than its left-hand table.^[4]

Sources

[1]DB Looperdocumentation
[2]SELECT MATCHING DATABASE ROWS [EasyMorph Help]documentation
[3]DB Parameterized Query Readerdocumentation
[4]FROM and JOIN Clauses – DuckDBdocumentation