¿Cuál es la diferencia entre las CTEs y las vistas de SQL?

Vistas SQL vs. CTEs: ¿Qué hacen? ¿En qué se diferencian? ¿Cuál debería usar y cuándo? ¡Obtenga sus respuestas aquí!

En SQL, tanto las CTE (expresiones comunes de tabla) como las vistas ayudan a organizar las consultas, lo que permite obtener un código más limpio y fácil de seguir. Sin embargo, existen algunas diferencias importantes entre ellas. Este artículo le guiará a través de varios ejemplos de CTEs y vistas y le explicará cuándo usar cada una.

¿Qué son las expresiones comunes de tabla (CTEs) en SQL?

Como recordarás, las expresiones comunes de tabla son conjuntos de resultados temporales que se crean mediante sentencias SQL sencillas y a las que se hace referencia en las sentencias SELECT, INSERT, UPDATE o DELETE.

Por ejemplo, digamos que tenemos una tabla llamada top_apps con información de clasificación de las principales aplicaciones en diferentes categorías:

También tenemos la tabla google_apps que tiene la misma información para varias aplicaciones de Google:

Queremos comparar el rendimiento de las aplicaciones de Google con el de las aplicaciones de mayor rendimiento en las categorías correspondientes. En concreto, queremos que la información sobre la valoración máxima de cada categoría se muestre junto a la valoración de la aplicación de Google de la misma categoría.

A continuación se muestra una consulta que cumple este objetivo utilizando un CTE:

WITH top_apps_max AS (
	SELECT category, MAX(rating) AS max_rating
	FROM top_apps
	GROUP BY category)
SELECT ga.name, ga.category, ga.rating, tam.max_rating
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

La consulta comienza creando un conjunto de resultados temporal llamado top_apps_max. Este conjunto de resultados se deriva de la tabla top_apps e incluye la lista de categorías junto con las valoraciones máximas correspondientes. A continuación, en la sentencia principal SELECT, unimos este conjunto de resultados temporal con la tabla google_apps utilizando la columna común category. Este es el resultado:

Se podría obtener el mismo resultado utilizando una subconsulta en lugar de una CTE. Sin embargo, en comparación con las subconsultas, el uso de una CTE de SQL da como resultado un código más limpio y fácil de seguir que se puede leer de arriba a abajo: primero se crea un conjunto de resultados temporal con un nombre específico que se utiliza más adelante en la consulta para hacer referencia a ese conjunto de resultados.

Tenga en cuenta que el CTE existe en la memoria sólo mientras se ejecuta la consulta. Una vez ejecutada la consulta, la CTE se descarta; no puede utilizarse para la siguiente consulta SQL a menos que la definamos de nuevo. Aún así, la misma CTE puede ser referenciada varias veces en la consulta principal y en cualquier subconsulta.

Puedes aprender más sobre las CTEs de SQL en esta completa guía de introducción a las expresiones comunes de tabla. Y si está interesado en practicar las CTEs con ejemplos del mundo real, consulte nuestro curso interactivo sobre expresiones comunes de tabla.

En el lenguaje cotidiano, las CTEs se denominan a veces vistas en línea. Así pues, recordemos qué es una vista y en qué se diferencia de una CTE.

¿Qué es una vista en SQL?

Una vistaes una consulta SQL almacenada que se ejecuta cada vez que se hace referencia a ella en otra consulta. Tenga en cuenta que una vista no almacena la salida de una consulta en particular, sino que almacena la propia consulta.

Veamos cómo funciona esto. Utilizaremos un ejemplo similar, pero esta vez usaremos una vista en lugar de un CTE.

Empezaremos creando la vista top_apps_max con la palabra clave CREATE VIEW, seguida de la sentencia SELECT:

CREATE VIEW top_apps_max AS
SELECT category, MAX(rating) AS max_rating, MAX(reviews) AS max_num_reviews
FROM top_apps
GROUP BY category;

Como ves, la sentencia SELECT es muy similar a la que utilizamos con la CTE. Podría ser absolutamente igual, pero hemos cambiado el alcance: Ahora queremos ver el número máximo de reseñas además de la valoración máxima de cada categoría (para tener más datos con los que trabajar).

Así que ahora tenemos una consulta SQL almacenada llamada top_apps_max. Es hora de utilizarla.

Comencemos por replicar nuestro primer ejemplo utilizando una vista en lugar de un CTE. Sin embargo, esta vez

• No necesitamos crear un CTE al principio de la consulta, pues ya tenemos la vista almacenada top_apps_max.
• Simplemente unimos la tabla google_apps con la vista top_apps_max en la columna de categoría y listamos las columnas que queremos ver en la salida:

SELECT ga.name, ga.category, ga.rating, tam.max_rating
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

El resultado de esta consulta será el mismo que en nuestro primer ejemplo:

¿Cuál es la diferencia?

En primer lugar, se puede utilizar la misma vista en otras consultas sin tener que definirla de nuevo. Por ejemplo, la consulta SQL que aparece a continuación hace referencia a la misma vista top_apps_max; esta vez, se utiliza para comparar el número de reseñas en lugar de la calificación de las diferentes aplicaciones:

SELECT ga.name, ga.category, ga.reviews, tam.max_num_reviews
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

Además, se puede utilizar un CTE al definir una vista. Digamos que también queremos ver el nombre de la aplicación más importante de cada categoría (es decir, la aplicación con el mayor número de reseñas). Una forma de hacerlo es crear una vista que muestre el nombre, la categoría y el número de opiniones de la aplicación más importante de cada categoría:

CREATE VIEW top_app_per_category AS
WITH top_app_max_reviews AS (
    SELECT category, MAX(reviews) AS max_num_reviews
    FROM top_apps
    GROUP BY category)
SELECT ta.name, ta.category, ta.reviews
FROM top_apps ta
JOIN top_app_max_reviews tamr
ON ta.reviews = tamr.max_num_reviews;

Como ves, la consulta almacenada en esta vista incluye una expresión de tabla común que da como resultado el número máximo de reseñas para cada categoría. Luego, en la consulta principal de la vista top_app_per_category, unimos esta CTE con la tabla top_apps para obtener el nombre de la aplicación con el mayor número de reseñas en cada categoría.

Ahora podemos hacer referencia a esta vista en otra consulta que devuelva las aplicaciones de Google junto con el nombre de la aplicación más importante de la categoría correspondiente y su número de reseñas:

SELECT ga.name, ga.category, ga.reviews, top.name AS top_app, top.reviews AS top_app_reviews
FROM google_apps ga
JOIN top_app_per_category top
ON ga.category = top.category;

Lo más importante que hay que recordar sobre las vistas SQL es que, a diferencia de una CTE, una vista es un objeto físico en una base de datos y se almacena en un disco. Sin embargo, las vistas sólo almacenan la consulta, no los datos devueltos por ésta. Los datos se calculan cada vez que se hace referencia a la vista en la consulta.

¿Quieres saber más sobre las vistas SQL? Aquí hay un gran artículo que explica las vistas SQL con múltiples ejemplos e ilustraciones. Además, recuerde practicar las vistas con nuestro curso interactivo Working with Views.

CTE SQL vs. Vista: Cuándo usar cada una

Aunque hay algunas diferencias entre ellas, las expresiones comunes de tabla y las vistas parecen tener un rendimiento muy similar. Por lo tanto, ¿cuándo se debe utilizar cada una de ellas?

• Consultas ad-hoc. Para las consultas que se consultan ocasionalmente (o sólo una vez), suele ser mejor utilizar una CTE. Si vuelve a necesitar la consulta, puede copiar el CTE y modificarlo si es necesario.
• Consultas de uso frecuente. Si suele hacer referencia a la misma consulta con frecuencia, crear una vista correspondiente es una buena idea. Sin embargo, para crear una vista necesitará un permiso de creación de vista en su base de datos.
• Gestión del acceso. Una vista puede utilizarse para restringir el acceso de determinados usuarios a la base de datos, pero permitiéndoles obtener la información que necesitan. Se puede dar a los usuarios acceso a vistas específicas que consulten los datos que están autorizados a ver sin exponer toda la base de datos. En este caso, una vista proporciona una capa de acceso adicional.

Practiquemos las CTEs y Vistas SQL

Ahora que tienes un conocimiento básico de las CTEs y vistas SQL, estás listo para empezar a usarlas en tus consultas. Practicar las consultas SQL es la mejor manera de entender cómo funcionan las CTEs y cómo las vistas ahorran tiempo en la reescritura y ejecución de las consultas.

LearnSQL.es ha desarrollado varios cursos que cubren estos temas en profundidad. En primer lugar, consulte el curso Consultas recursivas y expresiones de tabla comunes que le guiará desde los CTEs simples, pasando por los CTEs anidados, hasta los CTEs recursivos más difíciles. 114 ejercicios interactivos le ayudarán a dominar las expresiones comunes de las tablas de la manera más eficiente.

Para aquellos interesados en aprender más sobre las vistas SQL, hemos preparado el curso Trabajando con Vistas. Este es un curso avanzado que le enseñará cómo crear, modificar y eliminar vistas en SQL Server, MySQL, Oracle y PostgreSQL.

Gracias por leer, y ¡feliz aprendizaje!