Join / keyed · All operations

Nest the matching rows in a column

A variant of Join tables on a key

Join on equal keys and put each row's matches from the other table into one nested-table column.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Nest the matching rows in a column

SQL
CREATE TEMP TABLE l AS SELECT * FROM (VALUES
  (1, 'a', 'l1'), (2, 'a', 'l2'), (3, 'b', 'l3'), (4, NULL, 'l4'), (5, 'z', 'l5')) t(id, k, lv);
CREATE TEMP TABLE r AS SELECT * FROM (VALUES
  ('a', 'r1'), ('a', 'r2'), ('b', 'r3'), (NULL, 'r4'), ('y', 'r5')) t(k, rv);

-- one row for each left row; its matches are a list of structs in one column, empty where nothing matches
CREATE TEMP TABLE result AS
SELECT l.id, l.k, l.lv, coalesce(list(r ORDER BY r.rv) FILTER (WHERE r.k IS NOT NULL), []) AS matches
FROM l LEFT JOIN r ON l.k = r.k GROUP BY l.id, l.k, l.lv;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT count(*) = 5 AND list(len(matches) ORDER BY id) = [2, 2, 1, 0, 0] FROM result)
  AND (SELECT matches FROM result WHERE id = 1) = [{'k': 'a', 'rv': 'r1'}, {'k': 'a', 'rv': 'r2'}]
  AND (SELECT matches FROM result WHERE id = 4) = []
  AND (SELECT typeof(matches) FROM result LIMIT 1) = 'STRUCT(k VARCHAR, rv VARCHAR)[]') THEN 'as expected' ELSE error('the result should keep one row for each of the 5 left rows, with 2, 2, 1, 0 and 0 matches in a list of structs, and the NULL-key row should match nothing') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the result should keep one row for each of the 5 left rows, with 2, 2, 1, 0 and 0 matches in a list of structs, and the NULL-key row should match nothing”.
  • 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).

Power Query M (2)
  • Table.AddJoinColumnread 2026-09-27

    Left outer join to another table on equal keys, with the matching rows put in a new nested-table column

  • Table.NestedJoinread 2026-09-27

    Join two tables on equal keys and put the matching rows in a new nested-table column

Background

In Power Query, Table.NestedJoin joins two tables on the equality of their key columns and enters each row's matches into a new column.[1] Table.NestedJoin performs a left outer join when no join kind is specified.[1] A group join in Language Integrated Query (LINQ) pairs each element of the first collection with a set of correlated elements from the second.[2][3]

When to use it

After a merge in Power Query, the query has all the columns of the left table and a new column, named after the right table, holding its matching values row by row.[4] In Power Query, the fields of the merged column can be expanded or aggregated, though Power Query Online offers only the expand operation.[4] Nest the matches when you want a count, a total or a first value for each row of the left table without changing its grain. (Meridian's view)

Table.ExpandTableColumn expands the nested tables into multiple rows and columns, so one row whose nested table holds three rows becomes three rows.[5] Aggregate the nested column, rather than expand it, when the left table's row count must stay as it is. (Meridian's view) In a Power Query merge the key columns must be of the same data type, otherwise the merge might not yield correct results.[4]

The method

Power Query's Table.AddJoinColumn behaves identically to Table.NestedJoin with joinKind set to JoinKind.LeftOuter.[6] In a LINQ group join, each element of the first collection appears in the result whether or not correlated elements are found, and one with none gets an empty sequence.[2] LINQ's GroupJoin has no direct equivalent in relational database terms, but implements a superset of inner joins and left outer joins.[2] Before .NET 10, a left outer join in LINQ is performed by calling DefaultIfEmpty on the results of a group join.[2]

Compared with the alternatives

Power Query's Table.Join returns a row for each pair of matching rows, so a customer with three orders appears on three rows.[7][8]

Sources

  1. [1]Table.NestedJoin - PowerQuery M | Microsoft Learndocumentation
  2. [2]Join Operations - C# | Microsoft Learndocumentation
  3. [3]Language Integrated Query (LINQ) - C# | Microsoft Learndocumentation
  4. [4]Merge Queries Overview - Power Query | Microsoft Learndocumentation
  5. [5]Table.ExpandTableColumn - PowerQuery M | Microsoft Learndocumentation
  6. [6]Table.AddJoinColumn - PowerQuery M | Microsoft Learndocumentation
  7. [7]Table.Join - PowerQuery M | Microsoft Learndocumentation
  8. [8]JoinKind.Type - PowerQuery M | Microsoft Learndocumentation