WITH clause syntax in SQL

Introduction

The WITH clause helps you organize complex queries by creating temporary named results you can use inside your main query.

When you want to break a big query into smaller, easier parts.

When you need to reuse the same subquery multiple times in one query.

When you want to improve readability of your SQL code.

When you want to avoid repeating the same calculation or filtering in multiple places.

When you want to write queries step-by-step like a recipe.

Syntax

SQL

WITH temporary_name AS (
  subquery
)
SELECT columns FROM temporary_name;

The WITH clause defines a temporary named result called a Common Table Expression (CTE).

You can have multiple CTEs separated by commas before the main SELECT.

Examples

This example creates a temporary table named recent_orders with orders after January 1, 2024, then selects all from it.

SQL

WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date > '2024-01-01'
)
SELECT * FROM recent_orders;

This example uses two CTEs: one to sum sales by product, another to get product names, then joins them.

SQL

WITH sales AS (
  SELECT product_id, SUM(amount) AS total_sales FROM orders GROUP BY product_id
),
products AS (
  SELECT id, name FROM products
)
SELECT p.name, s.total_sales
FROM sales s
JOIN products p ON s.product_id = p.id;

Sample Program

This query finds customers who spent more than 1000 in total orders and lists their IDs and total spent.

SQL

WITH high_value_customers AS (
  SELECT customer_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total) > 1000
)
SELECT customer_id, total_spent FROM high_value_customers;

Important Notes

CTEs exist only during the execution of the query; they do not create permanent tables.

You can reference CTEs multiple times in the main query for clarity and efficiency.

Not all databases support recursive CTEs, but many support basic WITH clauses.

Summary

WITH clause creates temporary named results called CTEs.

CTEs help simplify and organize complex queries.

You can use multiple CTEs separated by commas before the main query.