SQL FULL OUTER JOIN: Reconcile Missing Rows on Both Sides

The practice task

Compare fictional planned and observed workshop IDs. Include a planned-only workshop and an observed-only workshop so a one-sided join cannot accidentally appear to solve the entire reconciliation.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE planned(id INTEGER, label TEXT);
CREATE TABLE observed(id INTEGER, label TEXT);
INSERT INTO planned VALUES (1,'Writing'),(2,'SQL');
INSERT INTO observed VALUES (2,'SQL'),(3,'Design');

SELECT COALESCE(p.id,o.id) AS workshop_id, p.label AS planned_label, o.label AS observed_label
FROM planned AS p FULL OUTER JOIN observed AS o ON o.id = p.id
ORDER BY workshop_id;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

Workshop 1 exists only in planned, workshop 2 matches and workshop 3 exists only in observed. COALESCE supplies a display ID from whichever side is present. The two label columns preserve which source supplied the information.

A left join starting from planned would omit workshop 3. Starting from observed would omit workshop 1. That asymmetry is the reason to preserve both sides for this particular reconciliation rather than choose one list as the complete authority.

The test uses SQLite 3.51.0, which supports this join syntax. Older SQLite versions and other engines may require a different formulation. Also verify key uniqueness: repeated IDs can create several match combinations, which is a separate issue from missing records.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Test a match plus an unmatched record from each input.
• Preserve source columns instead of hiding all missing-side information.
• Verify that each source key has the expected uniqueness.