CTE as readable subquery replacement in SQL

Introduction

CTEs make complex queries easier to read and understand by naming parts of the query clearly instead of nesting subqueries.

When you have a complex query with multiple nested subqueries that are hard to read.

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

When you want to break down a big query into smaller, understandable pieces.

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

When debugging a query to isolate parts of the logic.

Syntax

SQL

WITH cte_name AS (
  SELECT column1, column2
  FROM table_name
  WHERE condition
)
SELECT * FROM cte_name;

CTE stands for Common Table Expression.

The CTE is defined once and can be used like a temporary table in the main query.

Examples

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

SQL

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

This example uses a CTE to find customers who spent more than 1000, then selects their IDs and total spent.

SQL

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

Sample Program

This query uses a CTE to find the top 3 best-selling products by total quantity sold, then selects their IDs and totals.

SQL

WITH top_products AS (
  SELECT product_id, SUM(quantity) AS total_sold
  FROM sales
  GROUP BY product_id
  ORDER BY total_sold DESC
  LIMIT 3
)
SELECT product_id, total_sold FROM top_products;

Important Notes

CTEs improve readability but do not always improve performance.

You can define multiple CTEs separated by commas.

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

Summary

CTEs replace nested subqueries with named, readable blocks.

They help break complex queries into smaller parts.

Use CTEs to make your SQL easier to read and maintain.