WITH clause in BigQuery

The WITH clause in BigQuery, also known as a Common Table Expression (CTE), allows us to define a temporary result set that can be referenced within a single SQL statement.

CTEs help make complex queries more readable, modular, and maintainable by breaking them into logical steps.

SQL Syntax

WITH cte_name AS (
    -- SQL query
)
SELECT column1, column2
FROM cte_name;

Defining Multiple CTEs

We can also define multiple CTEs by separating them with commas:

SQL

WITH cte1 AS (
    SELECT ...
),
cte2 AS (
    SELECT ...
)
SELECT ...
FROM cte1
JOIN cte2 ON ...;

Key Notes

The CTE exists only during query execution.
It is not stored as a table or view.
Each CTE must have a unique name.
A CTE can be referenced multiple times in the same query.

Example: Create the Employees table.

SQL

CREATE TABLE `ashishcoder.Coding_Dataset.employees` (
  employee_id INT64,
  name STRING,
  age INT64,
  salary INT64,
  department STRING,
  gender STRING
);

Load the data into the Employees table.

SQL

INSERT INTO `ashishcoder.Coding_Dataset.employees` VALUES
(1, 'Ashish', 24, 25000, 'Electrical', 'M'),
(2, 'Kirshan', 32, 30000, 'Electrical', 'M'),
(3, 'Anjali', 20, 25000, 'Electronics', 'F'),
(4, 'Manjulika', 30, 15000, 'Electrical', 'F'),
(5, 'Katrina', 37, 18000, 'Computer', 'F'),
(6, 'Esha', 37, 20000, 'Computer', 'F'),
(7, 'Ankita', 20, 9000, 'Electronics', 'M'),
(8, 'Meenakshi', 30, 15000, 'Computer', 'F'),
(9, 'Alia', 37, 16000, 'Electronics', 'F');

To see the data in the Employees table, execute the following query:

SQL

SELECT *
FROM `ashishcoder.Coding_Dataset.employees`;

The above query returns the following result:

By using the WITH clause, we define a temporary result set and then use it in the following SELECT query.

SQL

WITH MyQuery AS (
  SELECT EmployeeID,
         Name,
         Age,
         LENGTH(Name) AS NameLength
  FROM `ashishcoder.Coding_Dataset.employees`
)
SELECT EmployeeID, Name, NameLength
FROM MyQuery;

The above query returns the following result:

Why use WITH (CTEs)?

Improves readability of complex queries.
Helps break down large queries into smaller parts.
Can reference the same CTE multiple times in a query.
Supports recursive queries.
CTEs are temporary and exist only during query execution.

Example: Find employees with the highest salary in each department.

SQL

WITH DepartmentMaxSalaries AS (
  SELECT
    department,
    MAX(salary) AS max_salary
  FROM `ashishcoder.Coding_Dataset.employees`
  GROUP BY department
)
SELECT e.*
FROM `ashishcoder.Coding_Dataset.employees` e
JOIN DepartmentMaxSalaries d
  ON e.department = d.department
 AND e.salary = d.max_salary;

The above query returns the following result:

Placement Rules for WITH Clause

The WITH clause must appear at the very beginning of the SQL statement.
It must come before SELECT, INSERT, UPDATE, or DELETE.

SQL

-- Correct
WITH cte AS (
  SELECT * FROM table
)
SELECT * FROM cte;

-- Incorrect
SELECT * FROM table
WITH cte AS (...); -- This will cause a syntax error