Join / keyed · All operations

Keep every row of both tables

A variant of Join tables on a key

Join on equal keys and keep every row of both tables, with nulls wherever either side has no match.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Keep every row of both tables

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);

CREATE TEMP TABLE result AS SELECT l.id, l.k AS l_key, l.lv, r.k AS r_key, r.rv FROM l FULL JOIN r ON l.k = r.k;
SELECT * FROM result ORDER BY id NULLS LAST, rv;
SELECT CASE WHEN ((SELECT count(*) = 9 AND count(*) FILTER (WHERE lv IS NOT NULL AND rv IS NOT NULL) = 5
      AND count(*) FILTER (WHERE l_key = 'a') = 4
      AND list(lv ORDER BY lv) FILTER (WHERE rv IS NULL) = ['l4', 'l5'] AND list(rv ORDER BY rv) FILTER (WHERE lv IS NULL) = ['r4', 'r5']
      AND count(*) FILTER (WHERE lv = 'l4' AND rv = 'r4') = 0 FROM result)) THEN 'as expected' ELSE error('a full join should give 5 matched pairs (the duplicated key fans out to 4), 2 left rows and 2 right rows with no partner, and the two NULL-key rows should stay apart') 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: “a full join should give 5 matched pairs (the duplicated key fans out to 4), 2 left rows and 2 right rows with no partner, and the two NULL-key rows should stay apart”.
  • 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).

Apache DataFusion (1)
  • FULL OUTER JOINread 2026-09-27

    All rows of both sides, with NULLs where either has no match

Tableau Prep (1)
  • Fullread 2026-09-27

    All rows of both tables; nulls where a row has no match

VisiData (4)
  • join-cols-fullread 2026-09-23

    Join on selected columns: keep all rows from all sheets (full outer)

  • join-selected-fullread 2026-09-23

    Join selected sheets: keep all rows from all sheets (full outer)

  • join-sheets-all-fullread 2026-09-23

    Join all stacked sheets: keep all rows from all sheets (full outer)

  • join-sheets-top2-fullread 2026-09-23

    Join top two sheets: keep all rows from all sheets (full outer)

Background

A full outer join keeps every row of both tables, with NULL in every column of the table that lacks a matching row.[1][2] Conceptually, a full outer join combines the effect of applying both a left and a right outer join.[1] In a full outer join, each matching pair of rows still gives a single row, with columns filled from both tables.[1]

When to use it

A full outer join shows the rows that match and also each table's rows without a partner, such as employees in no department and departments with no employee.[1] Reach for it when reconciling two sources that should agree, where the rows missing from either side are the finding. (Meridian's view) In pandas, merge with indicator=True adds a _merge column that marks each row left_only, right_only or both.[3]

In PostgreSQL, a join written with ON returns all columns of both tables, and a full join fills the other table's columns, its key included, with nulls on each unmatched row.[4] With ON, merge the two key columns with COALESCE so that every row of the result carries its key. (Meridian's view) Some database systems do not support the full outer join directly, but can emulate it with an inner join and UNION ALL selects of each table's unmatched rows.[1]

The method

PostgreSQL defines a FULL OUTER JOIN as an inner join, followed by a row with nulls for each row of either table that satisfies the join condition with no row of the other.[4] In relational algebra the full outer join is written R ⟗ S, and it can be simulated using the left and right outer joins and set union.[5] In pandas, an outer merge uses the union of keys from both frames and sorts the keys lexicographically.[3]

Compared with the alternatives

Tableau Prep's notInner join includes only the values of both tables that do not match, while its Full join includes all values from both tables.[6] Keep the full outer join when the matched rows are also worth comparing column by column, and filter to the unmatched rows only when the gaps are the whole question. (Meridian's view)

Sources

  1. [1]Join (SQL) - Wikipediasecondary source
  2. [2]FROM and JOIN Clauses – DuckDBdocumentation
  3. [3]pandas.merge — pandas 3.1.1 documentationdocumentation
  4. [4]PostgreSQL: Documentation: 18: 7.2. Table Expressionsdocumentation
  5. [5]Relational algebra - Wikipediasecondary source
  6. [6]Aggregate, Join, or Union Data - Tableaudocumentation