En SQL, la cláusula CON también se conoce como CTE. Es una característica poderosa que nos permite crear conjuntos de resultados temporales dentro de una consulta. Una función importante de los CTE es simplificar las consultas complejas en subconsultas más pequeñas y reutilizables. Esto ayuda a que el código sea más legible y mantenible a largo plazo.
Únase a nosotros en este tutorial mientras exploramos el funcionamiento de las expresiones de tabla comunes utilizando la cláusula CON y la funcionalidad compatible.
Requisitos:
Para fines de demostración, utilizaremos lo siguiente:
- MySQL versión 8.0 y superior
- Base de datos de muestra de Sakila
Cumplidos los requisitos dados, podemos proceder a conocer más sobre los CTE y la cláusula CON.
Cláusula SQL CON
La cláusula CON nos permite definir uno o más conjuntos de resultados temporales que se conocen como expresiones de tabla comunes.
Podemos hacer referencia a los CTE resultantes en la consulta principal como cualquier otra tabla o conjunto de resultados. Esto juega un papel crucial en la creación de consultas SQL modulares.
Aunque la sintaxis de CTE puede variar ligeramente según sus requisitos, a continuación se muestra la sintaxis básica de CTE en SQL:
CON cte_name (columna1, columna2, ...) COMO (
- Consulta CTE
SELECCIONAR ...
DE ...
DÓNDE ...
)
-- Consulta principal
SELECCIONAR ...
DE ...
ÚNETE a cte_name EN...
DÓNDE ...
Comenzamos con la palabra clave CON que le dice a la base de datos SQL que deseamos crear y usar CTE.
A continuación especificamos el nombre del CTE que nos permite referenciarlo en otras consultas.
También especificamos una lista opcional de nombres de columnas si el CTE incluye los alias de las columnas.
A continuación procedemos a definir la consulta CTE. Contiene entre paréntesis todas las tareas o los datos que realiza el CTE.
Por último, especificamos la consulta principal que hace referencia al CTE.
Uso de ejemplo:
Una de las mejores formas de comprender cómo utilizar y trabajar con CTE es observar un ejemplo práctico.
Tomemos, por ejemplo, la base de datos de muestra de Sakila. Supongamos que deseamos encontrar los 10 clientes principales con el mayor número de alquileres.
Eche un vistazo al CTE que se muestra a continuación.
Uso de la cláusula SQL CON para encontrar los 10 clientes principales con los recuentos de alquileres más altos:
CON CustomerRentals AS (SELECCIONE c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) COMO alquiler_count
DEL cliente c
ÚNETE al alquiler r ON c.customer_id = r.customer_id
GRUPO POR c.customer_id, c.first_name, c.last_name
)
SELECCIONAR *
DE CustomerRentals
ORDENAR POR alquiler_count DESC
LÍMITE 10;
En el ejemplo dado, comenzamos definiendo un nuevo CTE usando la palabra clave CON seguida del nombre que deseamos asignar al CTE. En este caso, lo llamamos “CustomerRentals”.
Dentro del cuerpo del CTE calculamos el cómputo de alquileres de cada cliente uniendo la tabla de clientes y alquileres.
Por último, en la consulta principal, seleccionamos todas las columnas del CTE, ordenamos los resultados según el recuento de alquileres (orden descendente) y limitamos la salida solo a las 10 filas superiores.
Esto nos permite buscar a los clientes con el mayor número de alquileres, como se muestra en el siguiente resultado:
CTE recursivos
En otros casos, es posible que se trate de estructuras de datos jerárquicas. Aquí es donde entran en juego los CTE recursivos.
Tomemos, por ejemplo, un caso en el que queremos navegar por la organización jerárquica o representar una estructura en forma de árbol. Podemos usar la palabra clave CON RECURSIVE para crear un CTE recursivo.
Dado que no hay datos jerárquicos que podamos usar en la base de datos de Sakila para demostrar un CTE recursivo, establezcamos un ejemplo básico.
CREAR TABLA departamento (depart_id INT CLAVE PRIMARIA AUTO_INCREMENT,
nombre_departamento VARCHAR(255) NO NULO,
parent_department_idINT,
CLAVE EXTRANJERA (parent_department_id) REFERENCIAS departamento(department_id)
);
INSERTAR EN el departamento (nombre_departamento, id_departamento_padre)
VALORES
('Corporativo', NULL),
('Finanzas', 1),
('HR', 1),
('Contabilidad', 2),
'Reclutamiento', 3),
('Nómina', 4);
En este caso, tenemos una tabla de 'departamento' de muestra con algunos datos aleatorios. Para encontrar la estructura jerárquica de los departamentos, podemos utilizar un CTE recursivo de la siguiente manera:
CON Jerarquía de Departamento RECURSIVA AS (SELECCIONE id_departamento, nombre_departamento, id_departamento_padre
DEL departamento
DONDE parent_department_id ES NULO
UNIÓN TODOS
SELECCIONE d.department_id, d.department_name, d.parent_department_id
DEL departamento d
UNIRSE a la Jerarquía de Departamentos dh ON d.parent_department_id = dh.department_id
)
SELECCIONAR *
DESDE Jerarquía de departamentos;
En este caso, el CTE recursivo comienza con departamentos que tienen un “parent_department_id” NULL (departamentos raíz) y recupera recursivamente los departamentos secundarios.
Conclusión
En este tutorial, aprendimos sobre las características más fundamentales y útiles de las bases de datos SQL, como las expresiones de tabla comunes, al comprender cómo trabajar con la palabra clave CON.