Join / cross · All operations

Cross join tables

Cross join, cartesian product.

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

Every combination

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
CREATE TEMP TABLE result AS
SELECT r.region, p.product FROM (SELECT DISTINCT region FROM orders WHERE region IS NOT NULL) r CROSS JOIN (SELECT DISTINCT product FROM orders) p;
SELECT * FROM result ORDER BY ALL;
SELECT CASE WHEN ((SELECT count(*) = 12 AND count(DISTINCT (region, product)) = 12 FROM result)) THEN 'as expected' ELSE error('four regions and three products did not give twelve pairs') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “four regions and three products did not give twelve pairs”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Pair every row with every row

Return every combination of a row from one table with a row from another, optionally limited by a condition.

Append columns by position

Put the columns of several tables side by side, row by row.

Add a single value to every row

Take one value from another table and add it as a column on every row.

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 (1)
  • Append Fieldsread 2026-09-27

    Append the fields of a small source to every record of a target: a Cartesian join

Apache DataFusion (1)
  • CROSS JOINread 2026-09-27

    Cartesian product of the two inputs

Apache Hop (1)
  • Join Rowsread 2026-09-27

    Cartesian product of the input streams, optionally limited by a condition

Duckle (1)
EasyMorph (2)
  • Cross mergeread 2026-09-27

    Produce every combination of rows from two tables

  • Peekread 2026-09-27

    Take a single value from another table and add it as a new column on every row

Ibis (1)
  • ScalarSubqueryread 2026-09-27

    Use a one-row, one-column subquery as a scalar value in an expression

KNIME Analytics Platform (2)
  • Column Appenderread 2026-09-27

    Put two or more tables side by side, appending their columns in input order

  • Cross Joinerread 2026-09-27

    Pair every row of one table with every row of another

Pentaho Data Integration (1)
RapidMiner Studio (1)
  • Cartesian Productread 2026-09-27

    Pair every row of one table with every row of another

Background

A cross join returns the Cartesian product of the rows of the tables in the join, combining each row of the first table with each row of the second.[1] The number of rows in a Cartesian product of two relations is the product of the two relations' row counts.[2] In DuckDB, a CROSS JOIN has no join condition and returns all the possible pairs of rows.[3]

When to use it

Reach for a cross join when you want every combination on purpose: every region against every product, say, so that a pair with no sales still gets a row of its own. (Meridian's view) In EasyMorph, a Cross Merge can produce extremely large tables, and the number of rows in the result can be estimated by multiplying the row counts of the two inputs.[4] In Alteryx Designer, Append Fields performs a Cartesian join, so a table of 100 rows joined to a table of 1,000 rows gives 100,000 rows.[5] Alteryx Designer's Append Fields tool can be set to warn, or to stop with an error, when its Source input holds more than 16 records.[5] In relational algebra, the cross product is the costliest operator to evaluate, so it is important to shrink both operands before applying it.[2] Count both sides before you run a cross join, and filter each side down to the rows you need first. (Meridian's view)

In Apache Hop, the Join Rows transform writes no rows at all when one of its input streams has no rows.[6] In Apache DataFusion, a NATURAL JOIN between tables that share no column names behaves like a CROSS JOIN.[7] Name the join columns rather than lean on NATURAL JOIN, so that a renamed column cannot quietly turn a join into a cross join. (Meridian's view)

In EasyMorph, the Peek action takes a value from another table and creates a new column populated with that value.[8] In Apache DataFusion, a subquery in the SELECT clause supports only scalar subqueries that return a single value per execution of the inner query, and SELECT clause subqueries can be used as an alternative to JOIN operations.[9] When you add one value to every row, check first that the other side really holds exactly one row. (Meridian's view)

In DuckDB, a POSITIONAL JOIN connects tables by the natural order of their rows, which imported tables such as data frames, comma-separated values (CSV) files and Parquet files have.[3] DuckDB's positional joins are always FULL OUTER joins, so the result has the length of the longer table and the missing entries are filled with NULL values.[3] When KNIME's Column Appender takes its RowIDs from one chosen input table, tables with fewer rows are filled with missing values and tables with more rows are truncated.[10] Put columns side by side by position only when both tables come from the same source in the same order; otherwise join them on a key. (Meridian's view)

The method

In set theory, the Cartesian product of two sets A and B is the set of all ordered pairs (a, b) where a is an element of A and b is an element of B.[11] The Cartesian product is named after René Descartes, whose formulation of analytic geometry gave rise to the concept.[11] Relational algebra was introduced by Edgar F. Codd as part of his relational model of data in 1970.[2] Relational algebra uses the Cartesian product from set theory, alongside set union and set difference.[2] In relational algebra, the Cartesian product is defined only for two relations with no attribute name in common.[2] In relational algebra, the Cartesian product of a set of n-tuples with a set of m-tuples yields a set of flattened (n+m)-tuples, rather than pairs of tuples.[2]

In the SQL:2011 standard, cross joins are part of the optional F401, Extended joined table, package.[1]

Apache Hop's Join Rows transform first reads all the rows of the streams other than the main stream, keeping them in memory or in temporary files.[6] Apache Hop's Join Rows transform then reads the main stream one row at a time and never keeps its rows, so the largest stream should be the main stream.[6] Apache Hop does not support Join Rows on the Beam or native Spark engines, because a cartesian product needs every row of every input in one place.[6]

Compared with the alternatives

The result of an inner join can be defined as first taking the Cartesian product of the tables' rows and then returning the rows that satisfy the join predicate.[1] SQL implementations normally compute inner joins in other ways, such as hash joins or sort-merge joins, since computing the Cartesian product is slower and would often require a prohibitively large amount of memory.[1] A cross join can be replaced with an inner join on an always-true condition, such as ON 1=1.[1] A CROSS JOIN applies no predicate of its own, and filtering its results with a WHERE clause may produce the equivalent of an inner join.[1]

Sources

  1. [1]Join (SQL)secondary source
  2. [2]Relational algebrasecondary source
  3. [3]FROM and JOIN Clausesdocumentation
  4. [4]CROSS MERGE [EasyMorph Help]documentation
  5. [5]Append Fields Tooldocumentation
  6. [6]Join Rowsdocumentation
  7. [7]SELECT syntaxdocumentation
  8. [8]PEEK [EasyMorph Help]documentation
  9. [9]Subqueriesdocumentation
  10. [10]Column Appenderdocumentation
  11. [11]Cartesian productsecondary source