Unlock Faster Queries: Master Window Functions and CTEs in MySQL & MariaDB

Window functions and Common Table Expressions (CTE) have become mainstream features in many popular database products. With the release of MySQL 8 and MariaDB 10.2.0, the two DBMS vendors have entered a competitive era.

Every MySQL DBA or developer should learn and apply the newly added window functions and CTEs in daily work. These features solve many query problems that were difficult in earlier versions, offering more robust, faster, and easier‑to‑maintain solutions.

Window Functions

Unlike aggregate functions that operate on the whole table, window functions compute a value over a set of rows (the "window") and return a single aggregate value for each row.

The main advantage is that rows retain their individual identity; the aggregate value is added to each row without collapsing the result set.

Window Functions in MariaDB

Window functions were added to the ANSI/ISO SQL standard in 2003 and extended in 2008. They have been fully implemented in DB2, Oracle, PostgreSQL, and later in MySQL (since 8.0) and MariaDB (since 10.2.0).

MariaDB 10.2.0 includes functions such as ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, COUNT, SUM, AVG, BIT_OR, BIT_AND, and BIT_XOR.

Syntax

A window function query uses the OVER clause to define the window. By default the window is the entire dataset, but it can be narrowed with PARTITION BY and ordered with ORDER BY.

SELECT name, test, score,
       AVG(score) OVER (PARTITION BY test) AS average_by_test
FROM test_scores;

The query returns the original rows together with the average score per test.

Supported Window Functions in MySQL 8

CUME_DIST()

– cumulative distribution value. DENSE_RANK() – rank without gaps. FIRST_VALUE() – value of the first row in the window. LAG() – value from a preceding row. LAST_VALUE() – value of the last row in the window. LEAD() – value from a following row. NTH_VALUE() – value of the N‑th row. NTILE() – bucket number. PERCENT_RANK() – percentile rank. RANK() – rank with gaps. ROW_NUMBER() – sequential row number.

These functions can be used with OVER together with aggregate functions such as AVG, COUNT, MAX, MIN, SUM, etc.

Common Table Expressions (CTE)

A CTE defines a temporary result set that exists only for the duration of a single statement (SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW). It improves readability and allows the same result set to be referenced multiple times within the statement.

Typical uses include recursive queries, replacing views, enabling grouping from scalar subqueries, and reusing result sets.

Recursive CTE

Recursive CTEs implement hierarchical queries defined in the SQL:1999 standard. They are supported by MySQL 8, MariaDB, PostgreSQL, SQLite, Oracle, and others.

WITH RECURSIVE cte (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

This query generates the numbers 1 through 5.

CTE in MariaDB

In MariaDB, a non‑recursive CTE acts like a local view, offering clearer syntax than nested subqueries.

WITH engineers AS (
    SELECT * FROM employees WHERE dept = 'Engineering'
)
SELECT * FROM engineers WHERE ...;

CTE in MySQL 8

MySQL 8.0 adds the standard WITH clause. Multiple CTEs can be defined and referenced in a subsequent SELECT.

WITH cte1 AS (SELECT a, b FROM table1),
     cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2 ON cte1.a = cte2.c;

Conclusion

Window functions and CTEs have become standard features in modern relational databases. The release of MySQL 8.0 and MariaDB 10.2.0 brings both systems up to date with the most popular trends, offering developers powerful tools for writing efficient, readable, and maintainable SQL.