# Keep every row of one table

Join / keyed

A variant of [Join tables on a key](https://meridian.online/operations/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.

Page: <https://meridian.online/operations/join-tables-on-a-key/keep-every-row-of-one-table>

## Status

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.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Apache DataFusion: [`LEFT OUTER JOIN`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- Apache DataFusion: [`RIGHT OUTER JOIN`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- Tableau Prep: [`Left`](https://help.tableau.com/current/prep/en-us/prep_combine.htm) (read 2026-09-27)
- Tableau Prep: [`Right`](https://help.tableau.com/current/prep/en-us/prep_combine.htm) (read 2026-09-27)
- VisiData: [`join-cols-outer`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/join.py#L423) (read 2026-09-23)
- VisiData: [`join-selected-outer`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/join.py#L420) (read 2026-09-23)
- VisiData: [`join-sheets-all-outer`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/join.py#L422) (read 2026-09-23)
- VisiData: [`join-sheets-top2-outer`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/join.py#L421) (read 2026-09-23)