Match every order to its customer

Question

The finance team reconciles a payment export against the customer master. Orders whose customer record was purged should not appear.

Join `orders_ledger` to `customer_master` and return `order_id`, `customer_name`, `segment` and `amount` for orders where the customer still exists.

Accepted Answer

Step 1: The two tables are linked by `customer_id`, which is the primary key on the master and a foreign key on the ledger. Step 2: Use `INNER JOIN` because rows without a surviving customer record must be dropped. Step 3: The join condition `c.customer_id = o.customer_id` automatically discards orders with unmatched IDs (99, 98) and the order whose `customer_id` is `NULL`, since `NULL` never equals anything. Step 4: Alias the tables (`o`, `c`) so the projected columns are unambiguous, and select the four requested fields.

Match every order to its customer

More Joins — Inner Join questions

Asked at PayPal