En MySQL, cada declaración o consulta produce un resultado o relación temporal. Se utiliza una expresión de tabla común o CTE para nombrar esos resultados temporales establecidos que existen dentro del alcance de ejecución de esa declaración en particular, como CREATE, INSERTAR , SELECCIONAR , ACTUALIZAR , BORRAR , etc.
uri frente a URL
Algunos de los puntos clave relacionados con CTE son:
- Se define utilizando el CON cláusula.
- La cláusula CON nos permite especificar más de un CTE en una sola consulta.
- Un CTE puede hacer referencia a otros CTE que forman parte de la misma cláusula CON, pero esos CTE deben definirse antes.
- El alcance de ejecución de CTE existe dentro de la declaración particular en la que se utiliza.
Sintaxis CTE de MySQL
La sintaxis de MySQL CTE incluye el nombre, una lista de columnas opcional y una declaración/consulta que define la expresión de tabla común (CTE). Después de definir el CTE, podemos usarlo como vista en una consulta SELECCIONAR, INSERTAR, ACTUALIZAR y ELIMINAR.
La siguiente es la sintaxis básica de CTE en mysql :
WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;
Es para garantizar que el número de columnas en los argumentos CTE sea el mismo que el número de columnas en la consulta. Si no hemos definido las columnas en los argumentos del CTE, utilizará las columnas de consulta que definen el CTE.
Al igual que la tabla derivada, no se puede almacenar como un objeto y se perderá tan pronto como se complete la ejecución de una consulta. Un CTE proporciona una mejor legibilidad y también aumenta el rendimiento en comparación con la tabla derivada.
A diferencia de una tabla derivada, una CTE es una subconsulta eso puede ser autorreferencia usando su propio nombre. También se le conoce como CTE recursivo y también se puede hacer referencia a él varias veces en la misma consulta.
Algunos de los puntos esenciales relacionados con el CTE recursivo son:
- Se define mediante la cláusula CON RECURSIVA.
- Un CTE recursivo debe contener una condición terminal.
- Usaremos el CTE recursivo para la generación de series y el recorrido de datos jerárquicos o estructurados en árbol.
Sintaxis CTE recursiva de MySQL
La siguiente es la sintaxis básica de CTE recursivo en MySQL:
WITH RECURSIVE cte_name (column_names) AS ( subquery ) SELECT * FROM cte_name;
Aquí, la subconsulta es una consulta MySQL que se refiere a sí misma utilizando cte_name como su propio nombre.
Ejemplos de CTE de MySQL
Entendamos cómo funciona CTE en MySQL usando varios ejemplos. Aquí vamos a utilizar una tabla. 'empleados' para una manifestación. Supongamos que esta tabla contiene los siguientes datos:
dormir para javascript
Ejecute la siguiente declaración para comprender el concepto de CTE. En este ejemplo, el nombre CTE es empleado_en_california , la subconsulta que define el CTE devuelve las tres columnas emp_name, emp_age y city. Por lo tanto, el CTE empleado_en_california devolverá a todos los empleados que se encuentren ubicados en el ciudad de california .
Después de definir el CTE empleado_en_california, lo hemos referenciado en el SELECCIONAR declaración para seleccionar sólo aquellos empleados que están ubicados en California.
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;
Después de ejecutar la declaración anterior, obtendrá el siguiente resultado. Aquí podemos ver que el resultado devuelve solo los datos de los empleados que se encuentran en California.
El ejemplo más avanzado de MySQL CTE
Supongamos que tenemos una tabla llamada cliente y orden que contiene los siguientes datos:
Tabla: cliente
tipos de arbol binario
Tabla: pedidos
Consulte la siguiente declaración que explica el ejemplo de CTE avanzado utilizando el UNIR INTERNAMENTE 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);
Después de ejecutar, obtendremos el resultado como se muestra a continuación:
Ejemplo de CTE recursivo de MySQL
Los siguientes ejemplos explican el funcionamiento del CTE recursivo. Considere la siguiente declaración que genera una serie de primeros cinco números impares :
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>
En segundo lugar, podemos usar la cláusula CON al comienzo de una subconsulta o una subconsulta de tabla derivada como se muestra a continuación:
mil millones a millón
SELECT ... WHERE id IN (WITH ... SELECT ...); SELECT * FROM (WITH ... SELECT ...) AS derived_table;
En tercer lugar, podemos usar la cláusula CON inmediatamente anterior a las declaraciones SELECT que incluyen una cláusula SELECT como se muestra a continuación:
CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ... INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... DECLARE CURSOR ... WITH ... SELECT ... EXPLAIN ... WITH ... SELECT ...
Beneficios de usar CTE
- Proporciona una mejor legibilidad de la consulta.
- Aumenta el rendimiento de la consulta.
- El CTE nos permite utilizarlo como alternativa al concepto VIEW
- También se puede utilizar como encadenamiento de CTE para simplificar la consulta.
- También se puede utilizar para implementar consultas recursivas fácilmente.