How to Perform JOINs in BigQuery

Why How to Perform JOINs in BigQuery is important

-- Show each customer, their latest order date, and total amount
SELECT
  c.id,
  c.name,
  o.order_date,
  o.total_amount
FROM `myshop.customers`  AS c
LEFT JOIN `myshop.orders` AS o
  ON c.id = o.customer_id
QUALIFY o.order_date = (
  SELECT MAX(order_date)
  FROM `myshop.orders` o2
  WHERE o2.customer_id = c.id
);

-- Basic syntax
SELECT [column_list]
FROM table1 [AS t1]
JOIN_TYPE JOIN table2 [AS t2]
  ON t1.column = t2.column
[JOIN_TYPE JOIN table3 ON ...]
[WHERE conditions];

-- JOIN_TYPE options
INNER JOIN              -- matched rows only
LEFT JOIN / LEFT OUTER   -- all left rows + matches
RIGHT JOIN / RIGHT OUTER -- all right rows + matches
FULL JOIN / FULL OUTER   -- all rows + matches
CROSS JOIN               -- cartesian product (use cautiously)

-- Ecommerce example: find each customer’s latest order total
SELECT c.id, c.name, o.total_amount
FROM `Customers` AS c
LEFT JOIN `Orders`   AS o
  ON c.id = o.customer_id;