MariaDB e MySQL: Usando CTE e Tabela Derivada

CTE(common table expression) ou expressão de tabela comum nos permite usar o resultado(ou result set) de um select em outro select.

Veja também: Termos: O que é RESULT SET em Banco de Dados?

Esse resultado ou result set existe apenas durante a execução de uma consulta e não é armazenado como um objeto do banco de dados.

Podemos usar(referenciar) uma CTE em no SELECT várias vezes. Também podemos fazer referência a uma CTE dentro dela mesma. Esta CTE é conhecida como CTE recursiva.

Podemos usar um CTE nas seguintes situações:

•  referênciar a um resultado(result set) de uma query várias vezes na mesma instrução.
• Substituir uma visualização para evitar a criação de uma visualização.
• Criar uma consulta recursiva.
• Simplificar uma consulta complexa dividindo-a em vários blocos de construção simples e lógicos.

Sintaxe de uma CTE

É assim a forma de uso de uma CTE:

1. especifique o nome do CTE entre as palavras-chave with e as.
2. crie o corpo do CTE entre parênteses, pode ser uma instrução select.
3. especifique uma instrução que usa o CTE.

with nome_cte as (
    corpo_cte
)
uso_do_cte;

Obs.:  o padrão SQL introduziu o CTE em 1999 e o MariaDB o implementou desde a versão 10.2.2

Tabelas Usadas nos Exemplos

Iremos trabalhar com as tabelas country_stats e countries que estão dentro do banco de dados nation. Esse banco nós baixamos já pronto e preenchido nesse artigo: MariaDB e MySQL: Baixando Exemplo de Banco de Dados.

Estrutura da Tabela country_stats

MariaDB [nation]> desc country_stats;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| country_id | int(11)       | NO   | PRI | NULL    |       |
| year       | int(11)       | NO   | PRI | NULL    |       |
| population | int(11)       | YES  |     | NULL    |       |
| gdp        | decimal(15,0) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

Estrutura da Tabela countries

MariaDB [nation]> desc countries;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| country_id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| name          | varchar(50)   | YES  |     | NULL    |                |
| area          | decimal(10,2) | NO   |     | NULL    |                |
| national_day  | date          | YES  |     | NULL    |                |
| country_code2 | char(2)       | NO   | UNI | NULL    |                |
| country_code3 | char(3)       | NO   | UNI | NULL    |                |
| region_id     | int(11)       | NO   | MUL | NULL    |                |
+---------------+---------------+------+-----+---------+----------------+

Exemplos de CTE

Abaixo temos um exemplo com CTE que retorna as 10 maiores economias em 2018

with maiores_economias as (
    select 
        country_id, 
        gdp 
    from 
        country_stats
    where 
        year = 2018
    order by gdp desc
    limit 10
)
select 
    name, 
    gdp
from 
    countries 
inner join maiores_economias
    using (country_id);

Veja na prática

MariaDB [nation]> with  maiores_economias as ( select country_id, gdp from country_stats where year=2018 order by gdp desc limit 10 ) select name, gdp from countries inner join maiores_economias using (country_id);
+----------------+----------------+
| name           | gdp            |
+----------------+----------------+
| United States  | 20494100000000 |
| China          | 13608200000000 |
| Japan          |  4970920000000 |
| Germany        |  3996760000000 |
| United Kingdom |  2825210000000 |
| France         |  2777540000000 |
| India          |  2726320000000 |
| Italy          |  2073900000000 |
| Brazil         |  1868630000000 |
| Canada         |  1712510000000 |
+----------------+----------------+

Tabela Derivada

Uma CTE é semelhante a uma tabela derivada(derived table). Veja abaixo exemplo de uma tabela derivada que retorna o mesmo resultado que a CTE acima

select 
    name, 
    gdp
from ( 
    select 
        country_id,
        gdp 
    from 
        country_stats
    where 
        year = 2018
    order by 
        gdp desc
    limit 10
) maiores_economias 
inner join countries  
    using (country_id);

Veja sendo executada:

MariaDB [nation]> select name, gdp from (select country_id, gdp from country_stats  where  year=2018 order by gdp desc limit 10) maiores_economias inner join countries using (country_id);
+----------------+----------------+
| name           | gdp            |
+----------------+----------------+
| United States  | 20494100000000 |
| China          | 13608200000000 |
| Japan          |  4970920000000 |
| Germany        |  3996760000000 |
| United Kingdom |  2825210000000 |
| France         |  2777540000000 |
| India          |  2726320000000 |
| Italy          |  2073900000000 |
| Brazil         |  1868630000000 |
| Canada         |  1712510000000 |
+----------------+----------------+