Join / positional · All operations

Put tables side by side

Put tables side by side, row by row.

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

Two tables side by side, row by row

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

SQL
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 * FROM (SELECT customer FROM customers ORDER BY customer) POSITIONAL JOIN (SELECT segment FROM customers ORDER BY customer);
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 4 FROM result) AND (SELECT count(*) = 4 FROM result JOIN customers USING (customer, segment))) THEN 'as expected' ELSE error('the rows did not line up by position') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the rows did not line up by position”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

Polars (2)
  • DataFrame.hstackread 2026-09-27

    Add Series as new columns beside the existing ones, matched by row position

  • LazyFrame.with_contextread 2026-09-27

    Let expressions read columns of other frames; deprecated in favour of a horizontal concat

RapidMiner Studio (1)
  • Merge Attributesread 2026-09-27

    Combine tables side by side by row position, padding shorter tables with missing values

Background

In DuckDB, a positional join connects two tables by the natural order of their rows, as imported data frames and files such as CSV or Parquet have.[1] RapidMiner's Merge Attributes merges the first row of each input with the first row of the others, the second with the second, and so on.[2] Alteryx's Join tool can join two data streams by record position as well as by common fields.[3]

When to use it

In DuckDB, positional joins are aimed at data frames or other embedded tables of the same size, whose rows may correspond by their physical order.[1] In Alteryx, joining by record position is the option for tables that have the same field structure.[3][4] Reach for a side-by-side join when the columns of one table arrive in separate pieces, row for row, and nothing but their order ties the pieces together. (Meridian's view) When the tables share a key, join on the key instead. (Meridian's view)

In DuckDB, FROM with multiple tables or subqueries, JOIN and GROUP BY do not guarantee that the original row order is preserved.[5] In DuckDB, ORDER BY does not guarantee that the original row order is preserved, because it may not use a stable sorting algorithm.[5] In DuckDB, row_number() OVER () turns the original row order into an explicit column that later operations can reference.[5] Numbering each table's rows and keeping the number in the result leaves the pairing visible and checkable. (Meridian's view) Compare the row counts of the two sides before joining, since a difference in length is a sign that the rows may no longer line up. (Meridian's view)

The method

In DuckDB, positional joins are always FULL OUTER joins: the result has the length of the longer input, and missing entries are filled with NULL values.[1] When the inputs of RapidMiner's Merge Attributes differ in size, the result holds missing values for all attributes that come from the smaller inputs.[2] In Polars, concat with how set to horizontal requires all input frames to have the same height and raises a ShapeError otherwise.[6] In Polars, the horizontal_extend strategy of concat pads shorter frames with null when the lengths do not match.[6]

RapidMiner's Merge Attributes either renames attributes that share a name, to the attribute name followed by the input's number, or keeps only the first of them.[2] Whatever the tool, settle what happens to a column name that appears on both sides before the join, not after it. (Meridian's view)

Compared with the alternatives

In Alteryx's Join tool, joining by specific field is the option when the two tables have one or more fields in common, such as an ID.[3] In Polars, the align strategies of concat also combine frames horizontally, but they find the common key columns and align rows on them.[6]

Sources

  1. [1]FROM and JOIN Clauses – DuckDBdocumentation
  2. [2]Merge Attributes - Rapidminer Documentationdocumentation
  3. [3]Join Tooldocumentation
  4. [4]Join Multiple Tooldocumentation
  5. [5]Order Preservation – DuckDBdocumentation
  6. [6]polars.concat — Polars documentationdocumentation