Join / lookup · All operations

Look up values from a reference table

Enrich from a reference table.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one table from two or more
Level
one clause of SQL

The DuckDB step

Enrich with a default when the key is missing

It reads the orders and customers tables the proofs share. Create them 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);

CREATE TABLE customers AS SELECT * FROM (VALUES
  ('Alice Ng',    'retail',    DATE '2025-06-01', 52.52, 13.40),
  ('Bob Okafor',  'wholesale', DATE '2025-09-15', 48.85,  2.35),
  ('Carla Diaz',  'retail',    DATE '2025-11-30', 40.42, -3.70),
  ('Frank Moore', 'retail',    DATE '2026-01-01', 51.51, -0.13)
) t(customer, segment, joined, lat, lon);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT o.order_id, o.customer, coalesce(c.segment, 'unassigned') AS segment FROM orders o LEFT JOIN customers c USING (customer);
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 10 AND count(*) FILTER (WHERE segment = 'unassigned') = 6 AND max(segment) FILTER (WHERE order_id = 6) = 'unassigned' FROM result)) THEN 'as expected' ELSE error('an order with no match did not take the default') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “an order with no match did not take the default”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Add columns from a reference table by key

Match each row to a reference table on a key and append the matching row's columns, keeping every input row.

Append data from a reference service

Add columns from a vendor dataset, reconciliation service or reference index matched to each row.

Replace text using a dictionary table

Find text from a second table in each value and replace it with that table's entry.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (5)
  • Allocate Appendread 2026-09-27

    Append demographic variables from an installed Allocate dataset to records by geography

  • Calgary Cross Count Appendread 2026-09-27

    Append Calgary database counts to the input records that join to it

  • Cluster Coderead 2026-09-27

    Append a segmentation cluster code to records using a cluster level id

  • Find Replaceread 2026-09-27

    Find a string in one table and replace it, or append columns, from a second lookup table

  • Marketo Appendread 2026-09-27

    Look up the Marketo records matching keys in the incoming stream and append them

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 (4)
  • Database Joinread 2026-09-27

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

  • Database Lookupread 2026-09-27

    Look up columns from a database table by key for each row

  • Dynamic SQL rowread 2026-09-27

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

  • Stream Lookupread 2026-09-27

    Look up values from another stream in the pipeline by key, held in memory

Apache NiFi (6)
  • FetchDistributedMapCacheread 2026-09-27

    Fetch values from a map cache by keys computed from attributes, into content or attributes

  • ISPEnrichIPread 2026-09-27

    Look up ISP information for an IP address in a MaxMind ISP database and add it to attributes

  • JoinEnrichmentread 2026-09-27

    Join records of an original FlowFile with records of its paired enrichment FlowFile

  • Lookup services (family)read 2026-09-27

    15 services, reference sources for LookupRecord and LookupAttribute: CSV, database, REST, MongoDB, Elasticsearch, …

  • LookupAttributeread 2026-09-27

    Look up attribute values in a lookup service

  • LookupRecordread 2026-09-27

    Look up record field values in a lookup service and add the result to the record

AWS Glue (1)
  • Lookupread 2026-09-27

    Add columns from a catalog table where keys match; equivalent to a left outer join

Azure Data Factory (2)
  • Cached lookup functionsread 2026-09-27

    4 functions: lookup, mlookup, output, outputs, …

  • Lookupread 2026-09-27

    Add columns of matching rows from another stream

Duckle (2)
  • Lookupread 2026-09-25

    left join a main input to a reference input

  • Mapread 2026-09-25

    visual mapper: join a main input to lookups, per-output expressions, filter

EasyMorph (2)
  • Lookupread 2026-09-27

    Fetch a value from another table for each row by matching a key column

  • Matchread 2026-09-27

    Replace text values with entries of a lookup table that they contain

KNIME Analytics Platform (1)
  • Value Lookupread 2026-09-27

    Look up each row's value in a dictionary table and append the matching row's cells

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

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

  • Database lookupread 2026-09-27

    Look up values in a database table using field values as keys

  • Dynamic SQL rowread 2026-09-27

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

  • Stream lookupread 2026-09-27

    Look up values from another stream of the transformation

RapidMiner Studio (1)
  • Replace (Dictionary)read 2026-09-27

    Replace substrings in nominal values using a from/to dictionary given as a second table

Talend (2)
  • tFirstnameMatchread 2026-09-27

    Match a first-name column against an embedded reference index and output the matching first names

  • tSynonymSearchread 2026-09-27

    Look up input values in a synonym index and output the matching reference entries

VisiData (4)
  • join-cols-extendread 2026-09-23

    Join on selected columns: keep first sheet's rows and type; add columns from the others by key

  • join-selected-extendread 2026-09-23

    Join selected sheets: keep first sheet's rows and type; add columns from the others by key

  • join-sheets-all-extendread 2026-09-23

    Join all stacked sheets: keep first sheet's rows and type; add columns from the others by key

  • join-sheets-top2-extendread 2026-09-23

    Join top two sheets: keep first sheet's rows and type; add columns from the others by key

Background

In Azure Data Factory, a lookup appends columns from matched rows of another source, and every row of the primary stream stays in the output.[1] The AWS Glue Lookup transform adds columns from a catalog table where keys match, which is equivalent to a left outer join on the matching columns.[2] A left outer join always keeps every row of the left table, putting NULL in each column from the right table where no row matches.[3]

When to use it

Reference data is data used to classify or categorise other data, and is typically static or slowly changing over time.[4] Examples of reference data include units of measurement, country codes, corporate codes and fixed conversion rates.[4]

When a result set joins several tables, including lookup tables of code descriptions, a NULL in any one of the foreign keys can remove the entire row with no indication of error.[3] Prefer a lookup that keeps every row, so a missing code shows up as a gap in the new columns rather than as a row that silently disappeared. (Meridian's view)

In AWS Glue, the columns brought from the lookup table are set to NULL when no match is found.[2] Apache Hop's Database Lookup can return a default value instead of NULL when a lookup fails.[5] In Apache Hop's Database Lookup, dropping the rows whose lookup fails makes the step an INNER JOIN in SQL terms; otherwise it is an OUTER JOIN.[5] Azure Data Factory's isMatch() function and KNIME's optional Match Found column each record whether a row found a match.[1][6] Decide what an unmatched row should carry, a NULL or a stated default such as unassigned, and count the unmatched rows before you trust the result. (Meridian's view)

In AWS Glue, a Lookup whose match columns do not cover the lookup table's whole composite key can match several lookup rows, adding an extra row for each match.[2] Apache Hop's Database Lookup can be set to fail when a lookup returns multiple results.[5] Check that the key is unique in the reference table before you look anything up, and compare the row count before and after the lookup. (Meridian's view)

In Apache Hop, loading a whole lookup table into memory can give different results from the database, which may compare text case-insensitively or ignore trailing spaces.[5] KNIME's Value Lookup treats missing values as ordinary values, valid as lookup values.[6] Matching rules travel with the tool, so check how case, trailing spaces and missing keys are compared before you move a lookup from one system to another. (Meridian's view)

The method

In DuckDB, a LEFT join returns every row of the left relation at least once, and an unpaired row has the other table's attributes set to NULL.[7] A left outer join returns all the rows of an inner join plus the rows of the left table that match nothing in the right table.[3]

Apache Hop's Stream Lookup reads the lookup data into memory first, so the entire lookup data set needs to fit in the available memory.[8] Apache Hop's Database Lookup can cache looked-up values, and caching increases performance substantially because database lookups are relatively slow.[5] In Apache Hop, a Database Lookup should not cache values when other processes are changing the table it looks up.[5]

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, and never more rows than the left table has.[7] A semi join answers whether a row has a match; a lookup answers what the match says, by bringing its columns across. (Meridian's view) A left outer join can usually be substituted for an inner join when the join columns in one table may contain NULL values.[3] An inner join is a filter as well as a lookup, so use it only when dropping the unmatched rows is what you want. (Meridian's view)

Sources

  1. [1]Lookup transformations in mapping data flow - Azure Data Factory & Azure Synapse | Microsoft Learndocumentation
  2. [2]Using the Lookup transform to add matching data from a catalog table - AWS Gluedocumentation
  3. [3]Join (SQL)secondary source
  4. [4]Reference datasecondary source
  5. [5]Database Lookup | Apache Hopdocumentation
  6. [6]Value Lookupdocumentation
  7. [7]FROM and JOIN Clauses – DuckDBdocumentation
  8. [8]Stream Lookup | Apache Hopdocumentation

Found by dovetail