Las uniones en BigQuery funcionan como en cualquier base de datos SQL. JOIN ... ON conserva las filas que coinciden en ambas tablas, y LEFT JOIN conserva todas las filas de la primera tabla, con valores NULL donde no hay coincidencias. Añade WHERE right.id IS NULL para encontrar lo que falta.
Asigna un alias corto a cada tabla y úsalo con los nombres de columnas compartidos: FROM shop.orders AS o JOIN shop.customers AS c ON c.customer_id = o.customer_id. Cuando las columnas tienen el mismo nombre en ambos lados, USING (customer_id) permite escribirlo de forma más breve.
Una tabla puede unirse consigo misma. customers.referred_by contiene el ID del cliente que hizo la recomendación, por lo que unir la tabla de clientes consigo misma permite obtener quién recomendó a cada cliente.
Cada pedido con el nombre del cliente que lo realizó.