Uma subconsulta correlacionada usa um valor da consulta externa, então é recalculada para cada linha externa:
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;
Para cada cliente, a consulta interna encontra os pedidos desse cliente e retorna a data mais recente. O vínculo é a condição o.customer_id = c.customer_id, que faz referência ao alias externo c.
Dois detalhes decidem o resultado quando nada corresponde. Uma função de agregação como MAX ou AVG sobre nenhuma linha retorna NULL, então um cliente sem pedidos recebe NULL. COUNT sobre nenhuma linha retorna 0. Envolva a subconsulta em COALESCE se a tarefa pedir um valor em vez de NULL.
As subconsultas correlacionadas são fáceis de ler, mas são executadas uma vez para cada linha externa. Em tabelas grandes, o mesmo resultado costuma sair mais rápido com uma junção com uma consulta agrupada ou com uma função de janela.
Pedidos com valor acima do valor médio dos pedidos desse cliente.