Join / keyed · All operations

Keep every row of one table

A variant of Join tables on a key

Join on equal keys and keep every row of the left or right table, with nulls where the other table 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 the left table, or every row of the right

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

-- key a is on both sides twice (2 x 2 = 4 pairs) and b once; l4 and r4 have a NULL key; l5 and r5 have no partner
CREATE TEMP TABLE left_join AS SELECT l.id, l.k, l.lv, r.rv FROM l LEFT JOIN r ON l.k = r.k;
CREATE TEMP TABLE right_join AS SELECT l.lv, r.k, r.rv FROM l RIGHT JOIN r ON l.k = r.k;
SELECT * FROM left_join ORDER BY id, rv;
SELECT * FROM right_join ORDER BY rv, lv;
SELECT CASE WHEN ((SELECT count(*) = 7 AND count(*) FILTER (WHERE k = 'a') = 4 AND count(*) FILTER (WHERE rv IS NULL) = 2
      AND list(id ORDER BY id) FILTER (WHERE rv IS NULL) = [4, 5] AND count(*) FILTER (WHERE rv = 'r4') = 0 FROM left_join)
  AND (SELECT count(*) = 7 AND count(*) FILTER (WHERE k = 'a') = 4 AND count(*) FILTER (WHERE lv IS NULL) = 2
      AND list(rv ORDER BY rv) FILTER (WHERE lv IS NULL) = ['r4', 'r5'] AND count(*) FILTER (WHERE lv = 'l4') = 0 FROM right_join)) THEN 'as expected' ELSE error('a left join should keep all 5 left rows and fan the duplicated key out to 7 rows, a right join keep all 5 right rows and give 7, and a NULL key should match nothing on either side') 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 left join should keep all 5 left rows and fan the duplicated key out to 7 rows, a right join keep all 5 right rows and give 7, and a NULL key should match nothing on either side”.
  • 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 (2)
  • LEFT OUTER JOINread 2026-09-27

    All left rows, with NULLs where the right has no match

  • RIGHT OUTER JOINread 2026-09-27

    All right rows, with NULLs where the left has no match

Tableau Prep (2)
  • Leftread 2026-09-27

    All rows of the left table with matches from the right; nulls where none

  • Rightread 2026-09-27

    All rows of the right table with matches from the left; nulls where none

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

    Join on selected columns: keep all rows of the first sheet (left outer)

  • join-selected-outerread 2026-09-23

    Join selected sheets: keep all rows of the first sheet (left outer)

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

    Join all stacked sheets: keep all rows of the first sheet (left outer)

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

    Join top two sheets: keep all rows of the first sheet (left outer)

Background

A left outer join keeps every row of the left table, even where no row of the right table matches, with NULL in each column from the right.[1] A right outer join closely resembles a left outer join with the tables reversed, so every row of the right table appears at least once.[1] Left and right outer joins can replace each other as long as the table order is switched.[1]

When to use it

In Power Query, a left outer join keeps all the rows from the left table and brings in any matching rows from the right, with null where a row has no match.[2] Reach for a left join to add columns to a table whose rows must all survive, such as orders gaining their customer's segment. (Meridian's view) A left outer join can usually be substituted for an inner join when the join columns in one table may contain NULL values.[1]

In PostgreSQL, a left join always has at least one row for each row of the first table, and one row per match where several rows of the second table satisfy the condition.[3] If the right table can repeat a key, deduplicate or aggregate it first, so the left table keeps one row per row. (Meridian's view) DuckDB's single join type returns every left row paired with its matching right row or NULL, and throws an error when a left row has more than one match.[4] In pandas, merge with validate="many_to_one" checks that the merge keys are unique in the right dataset.[5]

In PostgreSQL, a restriction placed in the ON clause is processed before the join and one in the WHERE clause after it, which matters a lot with outer joins.[3] In SQLite, the extra NULL rows of an outer join are added after the ON clause is processed but before the WHERE clause, so a WHERE condition on the other table can exclude them.[6] Put conditions on the right table's columns in the ON clause when every left row must survive the join. (Meridian's view)

The method

PostgreSQL defines a LEFT OUTER JOIN as an inner join, followed by a row with nulls in the second table's columns for each row of the first table that matches none.[3] In relational algebra the left outer join is written R ⟕ S and the right outer join R ⟖ S.[7] Oracle supports a deprecated outer join syntax in the WHERE clause, written as employee.DepartmentID = department.DepartmentID(+).[1]

Compared with the alternatives

In MySQL, a LEFT JOIN filtered with WHERE right_tbl.id IS NULL finds all rows in the left table with no corresponding row in the right table.[8] In DuckDB, an anti join returns the rows from the left table that have no matches in the right table.[4] Where your engine has an anti join, write it directly: a left join with an IS NULL filter asks the same question less plainly. (Meridian's view)

Sources

  1. [1]Join (SQL) - Wikipediasecondary source
  2. [2]Left outer join - Power Query | Microsoft Learndocumentation
  3. [3]PostgreSQL: Documentation: 18: 7.2. Table Expressionsdocumentation
  4. [4]FROM and JOIN Clauses – DuckDBdocumentation
  5. [5]pandas.merge — pandas 3.1.1 documentationdocumentation
  6. [6]SELECTdocumentation
  7. [7]Relational algebra - Wikipediasecondary source
  8. [8]MySQL :: MySQL 8.4 Reference Manual :: 15.2.13.2 JOIN Clausedocumentation