相関サブクエリは外側のクエリの値を使うため、外側の行ごとに計算し直されます。
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;
内側のクエリは、顧客ごとにその顧客の注文を見つけ、最も新しい日付を返します。結び付けているのはo.customer_id = c.customer_idという条件で、これは外側のエイリアスcを参照しています。
一致するものがないときの結果は、2つの点で決まります。行がないときのMAXやAVGなどの集計関数はNULLを返すため、注文のない顧客はNULLになります。行がないときのCOUNTは0を返します。課題がNULLではなく値を求めている場合は、サブクエリをCOALESCEで囲んでください。
相関サブクエリは読みやすいものの、外側の行ごとに1回実行されます。大きなテーブルでは、グループ化したクエリとの結合やウィンドウ関数を使ったほうが、同じ結果を速く得られることがよくあります。