No MySQL, cada instrução ou consulta produz um resultado ou relação temporária. Uma expressão de tabela comum ou CTE é usada para nomeie esses conjuntos de resultados temporários que existem dentro do escopo de execução daquela instrução específica, como CREATE, INSERIR , SELECIONE , ATUALIZAR , EXCLUIR , etc.
capa dura x brochura
Alguns dos pontos-chave relacionados ao CTE são:
- É definido usando o COM cláusula.
- A cláusula WITH nos permite especificar mais de um CTEs em uma única consulta.
- Um CTE pode fazer referência a outros CTEs que fazem parte da mesma cláusula WITH, mas esses CTEs devem ser definidos anteriormente.
- O escopo de execução do CTE existe na instrução específica em que é usado.
Sintaxe MySQL CTE
A sintaxe do MySQL CTE inclui o nome, uma lista de colunas opcional e uma instrução/consulta que define a expressão de tabela comum (CTE). Depois de definir o CTE, podemos usá-lo como visualização em uma consulta SELECT, INSERT, UPDATE e DELETE.
A seguir está a sintaxe básica do CTE em MySQL :
WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;
É para garantir que o número de colunas nos argumentos CTE seja igual ao número de colunas na consulta. Se não tivermos definido as colunas nos argumentos do CTE, serão utilizadas as colunas de consulta que definem o CTE.
Semelhante à tabela derivada, ela não pode ser armazenada como um objeto e será perdida assim que a execução de uma consulta for concluída. Um CTE oferece melhor legibilidade e também aumenta o desempenho em comparação com a tabela derivada.
Ao contrário de uma tabela derivada, um CTE é um subconsulta Isso pode ser auto-referência usando seu próprio nome. Também é conhecido como CTE recursivo e também pode ser referenciado várias vezes na mesma consulta.
Alguns dos pontos essenciais relacionados ao CTE recursivo são:
- É definido usando a cláusula WITH RECURSIVE.
- Um CTE recursivo deve conter uma condição de terminação.
- Usaremos o CTE recursivo para geração de séries e passagem de dados hierárquicos ou estruturados em árvore.
Sintaxe CTE recursiva MySQL
A seguir está a sintaxe básica do CTE recursivo no MySQL:
WITH RECURSIVE cte_name (column_names) AS ( subquery ) SELECT * FROM cte_name;
Aqui, a subconsulta é uma consulta MySQL que se refere usando cte_name como seu próprio nome.
Exemplos de CTE do MySQL
Vamos entender como o CTE funciona no MySQL usando vários exemplos. Aqui, vamos usar uma tabela 'funcionários' para uma demonstração. Suponha que esta tabela contenha os seguintes dados:
filmes indianos
Execute a seguinte instrução para entender o conceito de CTE. Neste exemplo, o nome CTE é funcionário_na_california , a subconsulta que define o CTE retorna as três colunas emp_name, emp_age e city. Assim, o CTE Employee_in_california retornará todos os funcionários que estão localizados no Cidade da Califórnia .
Após definir o CTE Employee_in_california, nós o referenciamos no SELECIONAR declaração para selecionar apenas os funcionários localizados na Califórnia.
WITH employees_in_california AS ( SELECT * FROM employees WHERE city = 'California' ) SELECT emp_name, emp_age, city FROM employees_in_california WHERE emp_age >= 32 ORDER BY emp_name;
Depois de executar a instrução acima, ele fornecerá a seguinte saída. Aqui podemos ver que o resultado retorna apenas os dados dos funcionários localizados na Califórnia.
O exemplo mais avançado do MySQL CTE
Suponha que temos uma tabela chamada cliente e ordem que contém os seguintes dados:
Tabela: cliente
tamanho de colher de chá
Tabela: pedidos
Veja a declaração abaixo que explica o exemplo de CTE avançado usando o JUNÇÃO INTERNA cláusula.
WITH total_customer_2020 AS ( SELECT cust_id, name, occupation FROM customer INNER JOIN orders USING (cust_id) ORDER BY age ) SELECT * FROM orders JOIN total_customer_2020 USING (cust_id);
Após a execução, obteremos a saída conforme abaixo:
Exemplo de CTE recursivo MySQL
Os exemplos a seguir explicam o funcionamento do CTE recursivo. Considere a declaração abaixo que gera uma série de primeiros cinco números ímpares :
WITH RECURSIVE odd_num_cte (id, n) AS ( SELECT 1, 1 union all SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>After executing the above statement, it will give the output as below:</p> <img src="//techcodeview.com/img/mysql-tutorial/80/mysql-common-table-expression-6.webp" alt="MySQL Common Table Expression (CTE)"> <p>The above statement consists of two parts one is non-recursive, and another is recursive.</p> <p> <strong>Non-recursive: SELECT 1, 1</strong> </p> <p>This part will produce the initial rows with two columns as 'id' and 'n' and a single row.</p> <p> <strong>Recursive: SELECT id+1, n+2 from odd_num_cte where id <5< strong> </5<></strong></p> <p>This part is responsible for adding rows to the previous output until the terminating condition (id <5) will not be satisfied. when the id reached 5, condition becomes false, and recursion process is terminated.< p> <h3>The WITH clause uses</h3> <p>MySQL provides many contexts to use the WITH clause for creating CTE. Let's discuss one by one in detail.</p> <p>First, we can use the WITH clause at the beginning of SELECT, UPDATE, and DELETE query as below.</p> <pre> WITH ... SELECT ... WITH ... UPDATE ... WITH ... DELETE ... </pre> <p>Second, we can use the WITH clause at the beginning of a subquery or a derived table subquery as below:</p> <pre> SELECT ... WHERE id IN (WITH ... SELECT ...); SELECT * FROM (WITH ... SELECT ...) AS derived_table; </pre> <p>Third, we can use the WITH clause immediately preceding of SELECT statements that include a SELECT clause as below:</p> <pre> CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ... INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... DECLARE CURSOR ... WITH ... SELECT ... EXPLAIN ... WITH ... SELECT ... </pre> <h3>Benefits of using CTE</h3> <ul> <li>It provides better readability of the query.</li> <li>It increases the performance of the query.</li> <li>The CTE allows us to use it as an alternative to the VIEW concept</li> <li>It can also be used as chaining of CTE for simplifying the query.</li> <li>It can also be used to implement recursive queries easily.</li> </ul> <hr></5)></p></5>
Em segundo lugar, podemos usar a cláusula WITH no início de uma subconsulta ou de uma subconsulta de tabela derivada conforme abaixo:
lista ordenada por java
SELECT ... WHERE id IN (WITH ... SELECT ...); SELECT * FROM (WITH ... SELECT ...) AS derived_table;
Terceiro, podemos usar a cláusula WITH imediatamente anterior às instruções SELECT que incluem uma cláusula SELECT conforme abaixo:
CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ... INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... DECLARE CURSOR ... WITH ... SELECT ... EXPLAIN ... WITH ... SELECT ...
Benefícios de usar CTE
- Ele fornece melhor legibilidade da consulta.
- Aumenta o desempenho da consulta.
- O CTE permite-nos utilizá-lo como alternativa ao conceito VIEW
- Também pode ser utilizado como encadeamento de CTE para simplificar a consulta.
- Também pode ser usado para implementar consultas recursivas facilmente.