liveBook · Manning

Outer join

As explained in DuckDB in Action

A full outer join preserves unmatched rows from both input relations and supplies NULLs on the missing sides.

An outer join preserves rows without matches and fills the missing side with NULL. A full outer join preserves unmatched rows from both relations. section 3.4.4

Keep every key from both inputs

SELECT * FROM (VALUES (1, 'a1'), (2, 'a2'), (3, 'a3')) l(id, nameA) FULL OUTER JOIN (VALUES (1, 'b1'), (2, 'b2'), (4, 'b4')) r(id, nameB) USING (id) ORDER BY id; The result retains matching IDs 1 and 2, left-only ID 3 with a missing right value, and right-only ID 4 with a missing left value.

How DuckDB in Action covers it

How deep it goes
Derives joins from Cartesian products and filtering, then demonstrates full outer join behavior with a small VALUES dataset.
Examples
Demonstrates joins over two VALUES relations keyed by id, producing matches and NULL-filled unmatched rows.
What you can do after
Apply it via DuckDB SQL to combine relations without discarding keys appearing on only one side.
How it connects
Connects full outer joins with inner joins, left and right outer joins, cross joins, and the USING clause.
Cover of DuckDB in Action
liveBookmanning.com
Read DuckDB in Action on liveBook Get the book at manning.com

Also explained in

Compare how 2 books explain outer join →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of DuckDB in Action. The section links point to the parts of the book it draws on.