Reconcile Customers and Orders — FULL OUTER JOIN in SQL

The problem

Brightlane's customer success team needs a full reconciliation of customers and orders, covering both directions:

Customers with no orders (order columns will be missing).
Orders with no matching customer (customer name will be missing).

Write a query to return the customer name, order ID, and order total for every row in the combined view.

Assumptions:

The customers table contains every customer Brightlane has on file.
The orders table contains every order Brightlane has processed.
The reconciliation should surface gaps on both sides: customers without orders and orders without a matching customer must each appear in the result.

Output:

One row per matched pair, plus one row per customer with no orders, plus one row per order with no matching customer, with columns customer_name, order_id, and total_amount.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  c.name AS customer_name,
  o.id AS order_id,
  o.total_amount
FROM
  customers c
  FULL OUTER JOIN orders o ON c.id = o.customer_id

The shape

A FULL OUTER JOIN between customers and orders keeps every row from both sides. A customer with five orders appears five times, one per order. A customer with no orders appears once with the order columns as NULL. An order whose customer_id doesn't resolve to any customer appears once with customer_name as NULL. The one-to-many relationship is what makes the matched side multiply; the join itself just preserves everyone.

Clause by clause

SELECT c.name AS customer_name, o.id AS order_id, o.total_amount pulls one column from the customers side and two from the orders side. On matched rows all three values are real. On a customer-without-orders row, both order columns come back NULL. On an orphan-order row, the customer name comes back NULL. Reading those NULL patterns is how the customer-success team tells the three categories apart.
FROM customers c FULL OUTER JOIN orders o ON c.id = o.customer_id is the reconciliation. The ON condition matches a customer to each of their orders. Where it matches, the row is assembled from both sides. Where it doesn't, the outer join keeps the row anyway and NULL-pads the missing side — and because FULL makes that guarantee in both directions, gaps in either table surface in the same result.
No WHERE. The team asked for the combined view, so every row the join produces belongs in the output.

You practiced a FULL OUTER JOIN between a dimension and a fact table. The recurring use case: data-quality reconciliation, where the orphan records on the fact side are usually the more interesting finding (orders that lost their customer reference).

Return the customer name, order ID, and order total for every row in the combined view

The shape

Clause by clause

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.