WITH clause syntax in PostgreSQL

Introduction

The WITH clause helps you organize complex queries by breaking them into smaller parts. It makes your query easier to read and reuse.

When you want to use the result of a query multiple times in one main query.

When you need to simplify a big query by dividing it into smaller, named steps.

When you want to improve query readability for yourself or others.

When you want to avoid repeating the same subquery in different places.

When you want to prepare data first before using it in the main query.

Syntax

PostgreSQL

WITH cte_name AS (
    subquery
)
SELECT * FROM cte_name;

CTE stands for Common Table Expression, which is a temporary named result set.

You can define multiple CTEs by separating them with commas inside the WITH clause.

Examples

This example creates a CTE named recent_orders that holds orders after January 1, 2024, then selects all from it.

PostgreSQL

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 per product, another to get product names, then joins them.

PostgreSQL

WITH sales AS (
    SELECT product_id, SUM(quantity) AS total_sold FROM order_items GROUP BY product_id
),
products AS (
    SELECT id, name FROM products
)
SELECT p.name, s.total_sold
FROM sales s
JOIN products p ON s.product_id = p.id;

Sample Program

This query finds the top 3 customers who spent the most by using a CTE to calculate total spending first.

PostgreSQL

WITH top_customers AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM sales
    GROUP BY customer_id
    ORDER BY total_spent DESC
    LIMIT 3
)
SELECT c.customer_id, c.total_spent
FROM top_customers c;

Important Notes

WITH clause queries run the subqueries first, then use their results in the main query.

CTEs are temporary and only exist during the execution of the main query.

Using WITH can improve readability but may not always improve performance.

Summary

WITH clause creates temporary named query results called CTEs.

It helps break complex queries into simpler parts.

You can use multiple CTEs separated by commas.

CTEs make queries easier to read and maintain.