Return each customer's `id`, name, and `pending_order_id` — the `id` of any pending order they have on record, reported as a missing value for customers with no pending order

The shape

The status = 'pending' filter sits inside the ON clause, not in a WHERE, because the marketing report has to keep customers who have no pending order. With the filter in the ON clause, a customer with no pending order fails the join condition; the LEFT JOIN preserves the customer row with o.id as NULL. With the filter in the WHERE clause, that same NULL fails the equality test and the customer disappears.

Clause by clause

• SELECT c.id AS customer_id, c.name AS customer_name, o.id AS pending_order_id returns the three columns the report needs. o.id is the pending order's id when one exists and NULL when the join found no match.
• FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'pending' pairs each customer with their pending order. The join condition has two parts joined by AND: the standard customer-to-order link, plus the status restriction. Both must be true for an order row to attach; a non-pending order fails the second part and is treated as no match.
• ORDER BY c.name, o.id sorts the result by customer name, with pending order id as the tiebreaker.

The trap

The trap is in where the status = 'pending' test lives. Two queries that look almost identical produce different result sets:

-- Right: customers with no pending order are kept
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'pending'

-- Wrong: customers with no pending order are silently dropped
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'pending'

The wrong version performs the LEFT JOIN first, preserves the orderless customer with o.status set to NULL, then runs the WHERE clause and discards that row because NULL = 'pending' evaluates to NULL, which WHERE treats as false. The LEFT JOIN is silently converted to an inner join. Any restriction on the right-hand table of a LEFT JOIN that must not eliminate unmatched left-side rows belongs inside the ON clause. The two clauses look interchangeable, and they are not.