CTE MySQL

Resumo : neste tutorial, você aprenderá como usar MySQL CTE ou expressão de tabela comum para construir consultas complexas de uma maneira mais legível.

O MySQL introduziu a expressão de tabela comum ou recurso CTE desde a versão 8.0, portanto você deve ter o MySQL 8.0+ para praticar com as instruções deste tutorial.

Introdução à expressão de tabela comum (CTE)

Uma expressão de tabela comum é um conjunto de resultados temporário nomeado que existe somente dentro do escopo de execução de uma única instrução SQL, como SELECT, INSERT, UPDATEou DELETE.

Semelhante a uma tabela derivada , uma expressão de tabela comum (CTE) não é armazenada como um objeto e dura apenas durante a execução da consulta.

Ao contrário de uma tabela derivada, uma expressão de tabela comum (CTE) pode ser auto-referenciada (no caso de uma CTE recursiva ) ou referenciada várias vezes na mesma consulta. Além disso, um CTE oferece maior legibilidade e desempenho em comparação com uma tabela derivada.

Sintaxe MySQL CTE

A estrutura de um CTE inclui o nome, uma lista de colunas opcionais e uma consulta que define o CTE. Depois de definir um CTE, você pode usá-lo como uma visualização na instrução SELECT, INSERT, UPDATE, DELETEou CREATE VIEW.

O seguinte ilustra a sintaxe básica de um CTE:

WITH cte_name (column_list) AS (
    query
) 
SELECT * FROM cte_name;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe:

• WITH cte_name (column_list) AS: defina um CTE com o nome cte_name e uma lista de colunas (column_list) que o CTE terá. A lista_colunas é opcional se você não especificar lista_colunas, o CTE herdará os nomes das colunas do resultado da consulta.
• query: esta é a consulta que define o CTE. O MySQL armazenará o resultado da consulta no CTE.
• SELECT * FROM cte_name: Este é um exemplo de como você pode usar o CTE. Neste caso, é uma instrução SELECT simples que recupera todas as colunas do CTE.

Exemplos de CTE do MySQL

Vamos explorar alguns exemplos de uso do MySQL CTE.

1) Exemplo básico de MySQL CTE

Usaremos a customerstabela do banco de dados de exemplo para demonstração:

O exemplo a seguir ilustra como usar um CTE para consultar dados da customerstabela no banco de dados de amostra .

Observe que este exemplo é apenas para fins de demonstração para facilitar a compreensão do conceito CTE.

WITH customers_in_usa AS (
    SELECT 
        customerName, state
    FROM
        customers
    WHERE
        country = 'USA'
) SELECT 
    customerName
 FROM
    customers_in_usa
 WHERE
    state = 'CA'
 ORDER BY customerName;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+------------------------------+
| customerName                 |
+------------------------------+
| Boards & Toys Co.            |
| Collectable Mini Designs Co. |
| Corporate Gift Ideas Co.     |
| Men 'R' US Retailers, Ltd.   |
| Mini Gifts Distributors Ltd. |
| Mini Wheels Co.              |
| Signal Collectibles Ltd.     |
| Technics Stores Inc.         |
| The Sharp Gifts Warehouse    |
| Toys4GrownUps.com            |
| West Coast Collectables Co.  |
+------------------------------+
11 rows in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

Como funciona.

• Primeiro, defina um CTE com o nome clients_in_usa que armazene o nome do cliente e o estado dos clientes nos EUA. A consulta de definição recupera dados da customerstabela.
• Em segundo lugar, selecione os clientes localizados na Califórnia no CTE.

2) Conseguir as melhores vendas usando um CTE

Usaremos orders, orderdetailse employeesdo banco de dados de exemplo:

O exemplo a seguir usa um CTE para recuperar os cinco principais representantes de vendas com base no total de vendas no ano de 2003:

WITH topsales2003 AS (
    SELECT 
        salesRepEmployeeNumber employeeNumber,
        SUM(quantityOrdered * priceEach) sales
    FROM
        orders
            INNER JOIN
        orderdetails USING (orderNumber)
            INNER JOIN
        customers USING (customerNumber)
    WHERE
        YEAR(shippedDate) = 2003
            AND status = 'Shipped'
    GROUP BY salesRepEmployeeNumber
    ORDER BY sales DESC
    LIMIT 5
)
SELECT 
    employeeNumber, 
    firstName, 
    lastName, 
    sales
FROM
    employees
        JOIN
    topsales2003 USING (employeeNumber);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+----------------+-----------+-----------+-----------+
| employeeNumber | firstName | lastName  | sales     |
+----------------+-----------+-----------+-----------+
|           1165 | Leslie    | Jennings  | 413219.85 |
|           1370 | Gerard    | Hernandez | 295246.44 |
|           1401 | Pamela    | Castillo  | 289982.88 |
|           1621 | Mami      | Nishi     | 267249.40 |
|           1501 | Larry     | Bott      | 261536.95 |
+----------------+-----------+-----------+-----------+
5 rows in set (0.02 sec)Linguagem de código:  JavaScript  ( javascript )

Como funciona.

• Primeiro, defina um CTE que recupere os 5 principais funcionários com o total de vendas em 2003.
• Em segundo lugar, junte o CTE com a employeestabela para incluir o nome e o sobrenome dos representantes de vendas.

3) Usando vários CTEs

Usaremos os clientes e funcionários do banco de dados de exemplo:

O exemplo a seguir usa vários CTEs para mapear os clientes com seus respectivos representantes de vendas:

WITH salesrep AS (
    SELECT 
        employeeNumber,
        CONCAT(firstName, ' ', lastName) AS salesrepName
    FROM
        employees
    WHERE
        jobTitle = 'Sales Rep'
),
customer_salesrep AS (
    SELECT 
        customerName, salesrepName
    FROM
        customers
            INNER JOIN
        salesrep ON employeeNumber = salesrepEmployeeNumber
)
SELECT 
    *
FROM
    customer_salesrep
ORDER BY customerName;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+------------------------------------+------------------+
| customerName                       | salesrepName     |
+------------------------------------+------------------+
| Alpha Cognac                       | Gerard Hernandez |
| American Souvenirs Inc             | Foon Yue Tseng   |
| Amica Models & Co.                 | Pamela Castillo  |
| Anna's Decorations, Ltd            | Andy Fixter      |
| Atelier graphique                  | Gerard Hernandez |
| Australian Collectables, Ltd       | Andy Fixter      |
| Australian Collectors, Co.         | Andy Fixter      |
| Australian Gift Network, Co        | Andy Fixter      |
...

Como funciona.

• CTE salesrep: Selecione employeeNumbere concatene as colunas firstNamee lastNamepara criar uma coluna chamada salesrepNamee inclua apenas funcionários com o cargo 'Sales Rep'.
• CTE customer_salesrep: seleciona customerNamee salesrepNamejunta a customerstabela com o salesrepCTE baseado na coluna comum employeeNumber.
• Consulta principal: Selecione todas as colunas do customer_salesrepCTE.

4) Exemplo de união de dois CTEs

Usaremos as tabelas officese employeesdo banco de dados de exemplo:

O exemplo a seguir é criar dois CTEs e juntá-los para obter os Representantes de Vendas localizados nos EUA, incluindo as informações do escritório:

WITH e AS (
  SELECT 
    * 
  FROM 
    employees 
  WHERE 
    jobTitle = 'Sales Rep'
), 
o AS (
  SELECT 
    * 
  FROM 
    offices 
  WHERE 
    country = 'USA'
) 
SELECT 
  firstName, 
  lastName, 
  city, 
  state, 
  postalCode 
FROM 
  e 
  INNER JOIN o USING (officeCode);
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+-----------+-----------+---------------+-------+------------+
| firstName | lastName  | city          | state | postalCode |
+-----------+-----------+---------------+-------+------------+
| Leslie    | Jennings  | San Francisco | CA    | 94080      |
| Leslie    | Thompson  | San Francisco | CA    | 94080      |
| Julie     | Firrelli  | Boston        | MA    | 02107      |
| Steve     | Patterson | Boston        | MA    | 02107      |
| Foon Yue  | Tseng     | NYC           | NY    | 10022      |
| George    | Vanauf    | NYC           | NY    | 10022      |
+-----------+-----------+---------------+-------+------------+
6 rows in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

Como funciona.

• CTE e: Recuperar funcionários cujo cargo seja Sales Rep.
• CTE o: Recupera escritórios localizados nos EUA.
• Consulta principal: Une o CTE e e o usando a officeCodecoluna.

Resumo

• Use CTEs do MySQL para dividir consultas complexas em consultas mais simples e gerenciáveis. Cada CTE representa um conjunto de resultados temporário que pode ser referenciado na consulta principal.