A correlated subquery uses a value from the outer query, so it's worked out again for every outer row:
SELECT c.first_name,
(SELECT MAX(o.order_date) FROM orders AS o WHERE o.customer_id = c.customer_id) AS last_order
FROM customers AS c;
For each customer, the inner query finds that customer's orders and returns the latest date. The link is the condition o.customer_id = c.customer_id, which refers to the outer alias c.
Two details decide the result when nothing matches. An aggregate like MAX or AVG over no rows returns NULL, so a customer with no orders gets NULL. COUNT over no rows returns 0. Wrap the subquery in COALESCE if the task wants a value instead of NULL.
Correlated subqueries read clearly, but they run once per outer row. On big tables, the same result often comes faster from a join to a grouped query or a window function.