Shape · Combine tables

Cross Join

Pairs every row of one table with every row of the other, so 10 × 5 rows gives 50. For example, every store with every month.

Also known as: Append fields, every combination

How it works

Wire the two tables into left and right; there is nothing to set. The output has every column of both tables and one row per pair, so the row count is the product of the two inputs. Use it to build a complete grid and then fill it with a Join or Lookup, so that combinations with no data still appear as rows.

Inputs and outputs

Settings

Example

A report must show every store for every month, including months a store sold nothing. Cross join a 10-row stores table with a 12-row months table to get the 120-row grid, then left join the actual sales onto it.

More in Combine tables

• Join — Matches rows from two tables on one or more key columns: inner, left, right, full, anti or semi. Keys can also compare with <, > or between, for range joins.
• Lookup — Adds columns from a reference table to every row by matching one key, like Excel's VLOOKUP. Rows without a match stay, with blanks.
• Union — Stacks the rows of two or more tables one under another. Columns line up by name or by position.
• Zip — Puts two tables side by side, row 1 with row 1, row 2 with row 2, with no key needed. Both tables must have the same number of rows.
• As-of Join — Matches each row to the closest row in another table by a sorted key such as a time. For example, attach the price in effect at the time of each trade.
• Fuzzy Match — Joins two tables on keys that are similar but not identical, such as customer names with typos. A score column shows how close each match is.