As junções no BigQuery funcionam como em qualquer banco de dados SQL. JOIN ... ON mantém as linhas que têm correspondência nas duas tabelas, e LEFT JOIN mantém todas as linhas da primeira tabela, com NULL onde não há correspondência. Acrescente WHERE right.id IS NULL para encontrar o que está faltando.
Dê a cada tabela um alias curto e use-o nos nomes de colunas compartilhados: FROM shop.orders AS o JOIN shop.customers AS c ON c.customer_id = o.customer_id. Quando as colunas têm o mesmo nome nos dois lados, USING (customer_id) é uma forma mais curta de escrever.
Uma tabela pode ser unida a ela mesma. customers.referred_by guarda o ID do cliente que o indicou, então juntar a tabela de clientes com ela mesma mostra quem indicou cada cliente.
Cada pedido com o nome do cliente que o fez.