lesson 24 Joins & sets · intermediate

FULL OUTER JOIN & unmatched records

Compare both sides of a relationship, including unmatched rows.

the idea

FULL OUTER JOIN keeps unmatched rows from both sides. Reconciliation often needs this to find missing records. This SQLite version supports FULL OUTER JOIN; older SQLite versions did not.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

invoices4 columns · 6 rows

idinteger · key	account_idinteger	periodtext	billed_amountreal
1	1	2025-01	20
2	3	2025-01	80
3	4	2025-01	20
4	6	2025-01	80
5	1	2025-02	20

showing 5 of 6 rows

payments3 columns · 7 rows

idinteger · key	invoice_idinteger	amountreal
1	1	10
2	1	10
3	2	80
4	3	15
5	4	80

showing 5 of 7 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT i.id AS invoice_id,p.id AS payment_id
FROM invoices i
FULL OUTER JOIN payments p ON p.invoice_id=i.id;

Watch for: Split payments make one invoice match more than one payment.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
See invoice and payment keys. Some invoices lack payments and some payments lack invoices. FULL OUTER JOIN keeps unmatched rows from both sides.
step-1.sql
sql›
```
SELECT i.id AS invoice_id, p.id AS payment_id
FROM invoices i
LEFT JOIN payments p ON p.invoice_id = i.id
ORDER BY i.id, p.id;
```
2
Retain unmatched rows on both sides. FULL OUTER JOIN returns matches plus leftovers from either table. Missing matches show NULL on the empty side.
step-2.sql
sql›
```
SELECT i.id AS invoice_id, p.id AS payment_id
FROM invoices i
FULL OUTER JOIN payments p ON p.invoice_id = i.id;
```