LEFT JOIN in BigQuery

Joins in BigQuery are used to combine rows from two or more tables based on a related column between them.

The most common types of joins are:

A LEFT JOIN in SQL is used to retrieve data from two or more tables based on a related column between them. It returns all rows from the left table (table1) and the matching rows from the right table (table2). If there is no match found, NULL values are returned for the columns of the right table.

SQL Syntax

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column
WHERE condition_expression
GROUP BY expression;

Example: Create Practice Dataset in BigQuery

Dataset name:

SQL

CREATE SCHEMA IF NOT EXISTS join_practice;

a) Create customers table

SQL

CREATE OR REPLACE TABLE join_practice.customers (
  customer_id INT64,
  customer_name STRING,
  city STRING
);

INSERT INTO join_practice.customers VALUES
  (1, 'Rahul', 'Delhi'),
  (2, 'Anita', 'Mumbai'),
  (3, 'Vikram', 'Bangalore');

The output of the above code is shown below:

Left join in BigQuery

b) Create sales table (LEFT table)

SQL

CREATE OR REPLACE TABLE join_practice.sales (
  sale_id INT64,
  customer_id INT64,
  sale_date DATE,
  amount INT64
);

INSERT INTO join_practice.sales VALUES
  (101, 1, DATE '2025-01-01', 5000),
  (102, 2, DATE '2025-01-02', 7000),
  (103, 4, DATE '2025-01-03', 6000), -- customer does NOT exist
  (104, 1, DATE '2025-01-04', 8000);

The output of the above code is shown below:

Left join in BigQuery

Apply LEFT JOIN (Core Example)

SQL

SELECT
  s.sale_id,
  s.sale_date,
  s.amount,
  c.customer_name,
  c.city
FROM join_practice.sales AS s
LEFT JOIN join_practice.customers AS c
  ON s.customer_id = c.customer_id;

The output of the above code is shown below:

Left join in BigQuery