lesson 24 Joins & sets · intermediate

FULL OUTER JOIN & unmatched records

Compare both sides of a relationship, including unmatched rows.

the idea

A FULL OUTER JOIN keeps unmatched rows from both sides: matched pairs appear once, left-only rows get NULL on the right, and right-only rows get NULL on the left. It is the natural shape for reconciling two feeds that should describe the same entities.

Dataset: Logistics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Reconcile two event lists. Run this. FULL OUTER JOIN shows packages in pickup only, delivery only, or both.the pattern

   example.sql Run

   sql›

   SELECT p.package_id AS picked_up, d.package_id AS delivered
   FROM (
     SELECT DISTINCT package_id
     FROM tracking_events
     WHERE event_type = 'picked_up'
   ) p
   FULL OUTER JOIN (
     SELECT DISTINCT package_id
     FROM tracking_events
     WHERE event_type = 'delivered'
   ) d ON d.package_id = p.package_id
   LIMIT 20;

2. 2

   Limit both sides to one week. Run this. Scoping shipments to a date window keeps the feed comparison manageable.

   step-2.sql Run

   sql›

   WITH pkg AS (
     SELECT p.package_id
     FROM packages p
     JOIN shipments s ON s.shipment_id = p.shipment_id
     WHERE s.created_at >= '2025-01-06' AND s.created_at < '2025-01-13'
   )
   SELECT COUNT(*) AS packages_in_week
   FROM pkg;

common mistake

A LEFT JOIN from either feed hides the rows that exist only in the other feed.