Join relationships and preserve missing matches

Estimated study time: 75–90 minutes. Read, predict, run, explain, then attempt the independent work.

Orders contain customer identifiers, while customer names live in customers. A join lets us combine these facts without storing the name repeatedly on every order. An INNER JOIN produces a row for each pair that satisfies its ON condition. In this relationship, each order's customer_id matches at most one customer because customers.customer_id is a primary key.

Use short table aliases such as o and c, and qualify columns as o.customer_id or c.customer_id. This makes the relationship readable and avoids ambiguity when both inputs contain the same column name. The ON condition compares the intended keys. Matching on names or cities would connect different people who happen to share a label.

Joining orders to customers is many-to-one. It normally preserves the order grain because each order has one matching customer. Joining customers to orders is one-to-many and can create multiple rows for a customer. The SQL text alone does not tell you which grain you wanted; your question does. Write the intended output grain beside the query and check the number of matches.

Use a left join when absence is evidence

The manager asks for every customer's completed-order count, including customers who have not bought. An inner join would omit unmatched customers, so start from customers and LEFT JOIN completed orders. A left join preserves each left-hand row and fills right-hand columns with nulls when no match exists. Gita has no order at all. Hari has a cancelled order but no completed one. Both should appear with zero completed orders.

The status condition belongs inside ON for this question. It controls which order rows may match while retaining every customer. If you write WHERE o.status='completed' after the left join, the synthetic unmatched rows contain a null status and fail the filter. The query effectively loses the customers you intended to keep. Neither placement is universally right; they express different populations.

COUNT(o.order_id) counts real matching orders and ignores the null placeholder. COUNT(*) would count the preserved left row even when no order matched, returning one for a customer who has zero completed orders. This is why you should count a known non-null key from the child table when measuring matches after a left join.

Find the absence rather than hiding it

To find customers with no order of any status, left join all orders and filter WHERE o.order_id IS NULL. You can trust this test because a real order_id is non-null. Testing a nullable descriptive field instead could confuse a matched row with missing detail and a genuinely unmatched row. Module 8 will show NOT EXISTS as another way to express absence.

An inner join's omitted rows can be desirable, but they can also conceal data quality problems. The curated fixture enforces its foreign keys, while the raw intake table deliberately does not. Later we will look for raw customer identifiers that fail to match a known customer. Do not assume that data imported from a CSV has the same guarantees as a constrained table.

A join should be checked in both directions: can a left row disappear, and can it multiply? This module handles disappearance. The next handles multiplication across independent child tables, a frequent source of misleading dashboards and AI-generated SQL.

Worked example 06 join

Each selected order matches one customer, so the four-order result stays at order grain. Customer Asha appears twice because she has two selected orders, not because customers contains duplicate rows.

SELECT o.order_id,c.customer_name,o.channel
FROM aabha_sql.orders AS o JOIN aabha_sql.customers AS c
 ON c.customer_id=o.customer_id
WHERE o.order_id<=1004 ORDER BY o.order_id;

Expected result from the bundled dataset:

The runnable file is sql/examples/06_join.sql.

Worked example 06 left

Read the status filter as part of the matching rule. Notice that Gita and Hari survive, and that counting the right-hand order key gives zero for both.

SELECT c.customer_id,c.customer_name,COUNT(o.order_id) AS completed_orders
FROM aabha_sql.customers AS c
LEFT JOIN aabha_sql.orders AS o
 ON o.customer_id=c.customer_id AND o.status='completed'
GROUP BY c.customer_id,c.customer_name ORDER BY c.customer_id;

Expected result from the bundled dataset:

The runnable file is sql/examples/06_left.sql.

Independent practice

06a

For order 1001 join order_items to products and return order_id, line_no, product_name and quantity, sorted by line_no.

Hint: Use product_id for the match; do not substitute the catalogue price for historical unit_price.

06b

Return customer_id and customer_name for customers with no order of any status.

Hint: Start from all customers, preserve unmatched rows, and test the non-null order key for absence.

Check before moving on

Explain the different questions “no orders” and “no completed orders.” Describe exactly why moving the completion filter from ON to WHERE changes the second worked result.