MariaDB CTE

Summary: in this tutorial, you will learn how to use a MariaDB common table expression or CTE to form a query.

Introduction to MariaDB common table expression or CTE

A common table expression or CTE allows you to create a temporary result set within a query.

A CTE is like a derived table in that it is not stored as a database object and exists only during the execution of a query.

Unlike a derived table, you can reference a CTE within a query multiple times. In addition, you can reference a CTE within itself. This CTE is known as a recursive CTE.

A CTE can be used to

• Reference a result set multiple times in the same statement.
• Substitute a view to avoiding creating a view.
• Create a recursive query.
• Simplify a complex query by breaking it into multiple simple and logical building blocks.

The following illustrates the syntax of common table expression:

with cte_name as (
    cte_body
)
cte_usage;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the CTE between the with and as keywords.
• Second, form the body of the CTE within the parentheses, which can be a select statement.
• Third, specify a statement that uses the CTE.

Note that SQL standard introduced CTE in 1999 and MariaDB implemented it since version 10.2.2

MariaDB CTE examples

We’ll use the tables countries and country_stats from the sample database for the demonstration.

The following example uses a common table expression to return the top 10 largest economies in 2018:

with largest_economies as (
    select 
        country_id, 
        gdp 
    from 
        country_stats
    where 
        year = 2018
    order by gdp desc
    limit 10
)
select 
    name, 
    gdp
from 
    countries 
inner join largest_economies 
    using (country_id);
Code language: SQL (Structured Query Language) (sql)

In this example:

• The name of the CTE is largest_economies. The CTE body has a query that selects the top 10 countries that has the biggest GDP in 2018.
• The outer query joins the countries table with the CTE to get the country names.

The above CTE is similar to the following query that uses a derived table:

select 
    name, 
    gdp
from ( 
    select 
        country_id,
        gdp 
    from 
        country_stats
    where 
        year = 2018
    order by 
        gdp desc
    limit 10
) t 
inner join countries  
    using (country_id);
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you learned how to use a MariaDB common table expression or CTE to form a query.