# Keep every row of both tables

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 both tables, with nulls wherever either side has no match.

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

## Status

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.

## What other tools call it

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

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