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
, UPDATE
ou 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
, DELETE
ou 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 customers
tabela do banco de dados de exemplo para demonstração:
O exemplo a seguir ilustra como usar um CTE para consultar dados da customers
tabela 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
customers
tabela. - Em segundo lugar, selecione os clientes localizados na Califórnia no CTE.
2) Conseguir as melhores vendas usando um CTE
Usaremos orders
, orderdetails
e employees
do 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
employees
tabela 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
: SelecioneemployeeNumber
e concatene as colunasfirstName
elastName
para criar uma coluna chamadasalesrepName
e inclua apenas funcionários com o cargo'Sales Rep'
. - CTE
customer_salesrep
: selecionacustomerName
esalesrepName
junta acustomers
tabela com osalesrep
CTE baseado na coluna comumemployeeNumber
. - Consulta principal: Selecione todas as colunas do
customer_salesrep
CTE.
4) Exemplo de união de dois CTEs
Usaremos as tabelas offices
e employees
do 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
officeCode
coluna.
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.