How to use CTE in MariaDB

How do I create and use CTEs in MariaDB?

A Common Table Expression (CTE) defines a temporary, named result set you can reference within a single MariaDB statement, simplifying complex queries and enabling recursion.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is a Common Table Expression in MariaDB?

A CTE is a temporary, named result set created with the WITH clause and available only for the statement that follows. It helps you break large queries into readable blocks and can be recursive.

Why choose a CTE over subqueries?

CTEs improve readability, allow multiple references to the same derived data, enable recursion, and make debugging easier.They seldom change performance but greatly aid maintenance.

How do I write a basic non-recursive CTE?

Define the CTE with WITH, list column names if needed, supply a SELECT, then query it like a table.

WITH recent_customers AS ( SELECT id, name, created_at FROM Customers WHERE created_at > NOW() - INTERVAL 30 DAY ) SELECT * FROM recent_customers ORDER BY created_at DESC;

How do I chain multiple CTEs?

Separate CTE blocks with commas.Each CTE can refer to those defined before it.

WITH recent_orders AS ( SELECT id, customer_id, total_amount FROM Orders WHERE order_date > NOW() - INTERVAL 7 DAY ), high_value AS ( SELECT * FROM recent_orders WHERE total_amount > 500 ) SELECT c.name, hv.total_amount FROM high_value hv JOIN Customers c ON c.id = hv.customer_id;

How do I create a recursive CTE?

Add the RECURSIVE keyword, supply an anchor SELECT, then a UNION ALL with the recursive member that references the CTE name.

WITH RECURSIVE date_series AS ( SELECT DATE(NOW()) AS d UNION ALL SELECT d + INTERVAL 1 DAY FROM date_series WHERE d < DATE(NOW()) + INTERVAL 6 DAY ) SELECT d FROM date_series;

What are best practices for CTEs?

Keep each CTE focused, name them descriptively, avoid unnecessary recursion, and always test performance with EXPLAIN.

Do CTEs persist or require indexes?

No.A CTE’s data exists only during the statement, inherits indexes from source tables, and cannot be indexed itself.

Why How to use CTE in MariaDB is important

How to use CTE in MariaDB Example Usage


WITH top_products AS (
    SELECT p.id, p.name, SUM(oi.quantity) AS units_sold
    FROM OrderItems oi
    JOIN Products p ON p.id = oi.product_id
    GROUP BY p.id, p.name
    ORDER BY units_sold DESC
    LIMIT 5
)
SELECT * FROM top_products;

How to use CTE in MariaDB Syntax


WITH [RECURSIVE] cte_name [(column1, column2, …)] AS (
    select_statement
) [, cte_name2 AS (select_statement2) …]
SELECT … FROM cte_name [JOIN …]

Example with ecommerce data:
WITH overdue_orders AS (
    SELECT id, customer_id, total_amount
    FROM Orders
    WHERE order_date < CURRENT_DATE - INTERVAL 30 DAY
)
SELECT c.name, o.total_amount
FROM overdue_orders o
JOIN Customers c ON c.id = o.customer_id;

Common Mistakes

Using SELECT * inside large CTEs. It returns unnecessary columns, bloats memory, and confuses readers. Fix by selecting only required columns.
Forgetting to add an anchor SELECT in recursive CTEs, causing infinite loops. Always include a non-recursive base query before UNION ALL.