Join / keyed · All operations

Join more than two tables

A variant of Join tables on a key

Join three or more tables on their shared keys in one step.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Join four tables on a shared key in one statement

SQL

CREATE TEMP TABLE a AS SELECT * FROM (VALUES ('a1', 'a'), ('a2', NULL), ('a3', 'b'), ('a4', 'c')) t(av, k);
CREATE TEMP TABLE b AS SELECT * FROM (VALUES ('a', 'b1'), ('a', 'b2'), (NULL, 'b3'), ('b', 'b4')) t(k, bv);
CREATE TEMP TABLE c AS SELECT * FROM (VALUES ('a', 'c1'), ('a', 'c2'), ('b', 'c3'), (NULL, 'c4'), ('c', 'c5')) t(k, cv);
CREATE TEMP TABLE d AS SELECT * FROM (VALUES ('a', 'd1'), ('c', 'd2'), ('d', 'd3')) t(k, dv);
-- key a is in all four tables (1 x 2 x 2 x 1 = 4 rows); b is missing from d and c from b, so each drops out at its missing table; a NULL key matches nothing
CREATE TEMP TABLE result AS
SELECT a.k, a.av, b.bv, c.cv, d.dv FROM a JOIN b ON a.k = b.k JOIN c ON a.k = c.k JOIN d ON a.k = d.k;
SELECT * FROM result ORDER BY ALL;
SELECT CASE WHEN ((SELECT count(*) = 4 AND count(*) FILTER (WHERE k = 'a') = 4
      AND count(*) FILTER (WHERE k IN ('b', 'c') OR k IS NULL) = 0
      AND list(bv || cv ORDER BY bv, cv) FILTER (WHERE k = 'a') = ['b1c1', 'b1c2', 'b2c1', 'b2c2'] FROM result)) THEN 'as expected' ELSE error('four tables joined on one key should give 4 rows for the key found twice in two tables, and none for the keys b and c, each missing from one table, or for a NULL key') 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: “four tables joined on one key should give 4 rows for the key found twice in two tables, and none for the keys b and c, each missing from one table, or for a NULL key”.
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).

Alteryx Designer (1)

Join Multipleread 2026-09-27
Combine two or more inputs on a common field or position; full outer join by default

Apache Hop (1)

Multiway Merge Joinread 2026-09-27
Inner or full outer join of more than two key-sorted streams

Ibis (1)

JoinChainread 2026-09-27
A first table joined to a sequence of further tables, with the output columns selected

Pentaho Data Integration (1)

Multiway Merge Joinread 2026-09-27
Inner or full outer join of several streams

Polars (1)

align_framesread 2026-09-27
Align several frames on shared key values, adding null rows for missing keys, sorted by key

VisiData (1)

join-sheets-allread 2026-09-23
Join all sheets on the stack on their key columns with a prompted jointype

Background

In PostgreSQL, every join in the executor has two input tables, so a query over several tables is built up by joining two at a time.^[1] Because inner joins are commutative and associative, the order in which tables are joined does not change the result, though it can change the cost enormously.^[2] In PostgreSQL, a query with outer joins leaves the planner less freedom over the join order than plain inner joins do.^[1]

When to use it

In Alteryx Designer, the Join Multiple tool combines two or more inputs on a common field or by record position, and outputs a full outer join by default.^[3] The Join Multiple option Only Output Records that Join from All Inputs passes only records that meet every join criterion, and otherwise all records come out with NULL where a join failed.^[3] Decide first whether a row must match in every table or only in some of them, and write each join in the chain to match that choice. (Meridian's view)

In PostgreSQL, a query of the form a LEFT JOIN (b JOIN c) must join b to c first, because a row has to be emitted for each row of a with no matching row in the join of b and c.^[1] SQLite handles all joins from left to right, and since inner joins are associative its difference from standard Structured Query Language (SQL) shows only when the FROM clause has outer joins.^[4] MySQL allows at most 61 tables to be referenced in a single join.^[5] Alteryx's Join Multiple can warn, or stop with an error, on multidimensional joins of more than 16 records, where every row of one table is joined to every row of another.^[3] Check the row count after each join in a chain, so a key that repeats is caught at the step that brought it in. (Meridian's view)

The method

Query plans with several joins are classed as left-deep, right-deep or bushy, by whether each join takes a base table or another join's result as its operands.^[2] Worst-case optimal join algorithms are asymptotically faster than binary join algorithms for joins between more than two relations in the worst case.^[2]

Compared with the alternatives

Polars' align_frames does not return a joined frame: it returns the frames passed in, each aligned on the key values, with rows injected where a key is missing and sorted by the key.^[6] Align the tables when they should stay separate but line up row for row, and chain joins when you want one wide table. (Meridian's view)

Sources

[1]PostgreSQL: Documentation: 18: 14.3. Controlling the Planner with Explicit JOIN Clausesdocumentation
[2]Join (SQL) - Wikipediasecondary source
[3]Join Multiple Tooldocumentation
[4]SELECTdocumentation
[5]MySQL :: MySQL 8.4 Reference Manual :: 15.2.13.2 JOIN Clausedocumentation
[6]polars.align_frames — Polars documentationdocumentation