en ingeniería de software, es una práctica común agrupar instrucciones como unidades pequeñas y fácilmente comprensibles, es decir, funciones o métodos. Esto hace que el código sea reutilizable y mejora la legibilidad.
aunque SQL también tiene funciones y procedimientos, no son las herramientas adecuadas para construir unidades fácilmente comprensibles y reutilizables. En SQL, ni las funciones ni los procedimientos son ciudadanos de primera clase de la misma manera que lo son las subconsultas.0 los bloques de construcción de SQL son queries1-No instrucciones.,
para hacer las consultas reutilizables, SQL-92 introdujo vistas. Una vez creada, una vista tiene un nombre en el esquema de la base de datos para que otras consultas puedan usarla como una tabla.
SQL: 1999 agregó la cláusula with para definir «vistas de alcance de sentencia». No se almacenan en el esquema de la base de datos: en su lugar, solo son válidos en la consulta a la que pertenecen. Esto hace posible mejorar la estructura de una sentencia sin contaminar el espacio de nombres global.,
sintaxis
la cláusulawith es, en pocas palabras, un prefijo opcional paraselect:2
WITH query_name (column_name1, ...) AS (SELECT ...) SELECT ...la sintaxis después de la palabra clavewith es lo mismo que paracreate view: comienza con el nombre de la consulta, y opcionalmente 3 y entre paréntesis el nombre de las columnas que devuelve. La palabra clave as finalmente introduce la definición en sí (la consulta)—de nuevo entre paréntesis.,
With no es un solo comando como create view es: debe ser seguido por select. Esta consulta (y las subconsultas que contiene) pueden referirse al nombre de consulta recién definido en su cláusula from.
una sola cláusula with puede introducir varios nombres de consulta separándolos con una coma (la palabra clave with no se repite)., Cada una de estas consultas puede referirse a los nombres de consulta previamente definidos dentro del mismo with clause4 (una regla a menudo descuidada—consulte Compatibilidad):
WITH query_name1 AS ( SELECT ... ) , query_name2 AS ( SELECT ... FROM query_name1 ... )SELECT ...nombres de consulta definidos usando with enmascara tablas o vistas existentes con el mismo nombre.5
consideraciones de rendimiento
La mayoría de las bases de datos procesan with-consultas de la misma manera que procesan vistas: reemplazan la referencia a la consulta por su definición y optimizan la Consulta general.,
La base de datos PostgreSQL era diferente hasta la versión 12: optimizaba cada consulta with y la instrucción principal independientes entre sí.
si se hace referencia a una consulta with varias veces, algunas bases de datos almacenan en caché (es decir, «materializan») su resultado para evitar la doble ejecución.
Lea más sobre esto en «with Clause: Performance Impacts».
en mi propio nombre
me gano la vida de la formación, Otros servicios relacionados con SQL y la venta de mi libro. Más información en https://winand.at/.,
casos de uso
-
SQL alfabetizado
-
pruebas unitarias sobre datos transitorios
-
asignar nombres a columnas sin un nombre conocido
compatibilidad
la cláusula with se introdujo con SQL:1999 como un conjunto de características opcionales. Dejando a un lado la variante recursive, las características son T121 para cláusulas de nivel superior with y t122 para cláusulas with en subconsultas.
La funcionalidad básica de with está bien soportado., La única área donde los productos se comportan de manera diferente es la resolución de nombres. Es especialmente notable que with a menudo se trata como with recursive.6 las incompatibilidades más sutiles están relacionadas con los nombres de tabla calificados (schema.table no puede hacer referencia a una consulta with) 7 y las vistas utilizadas en el ámbito de las consultas with (la consulta dentro de la vista no «ve»>cláusula).,8
Conforme Alternativas
las Vistas pueden cubrir algunos de los casos de uso. Sin embargo, esto puede conducir fácilmente a un número irrazonable de vistas («contaminación del espacio de nombres»). En esos casos, las subconsultas suelen ser la mejor opción.
extensiones propietarias
con como prefijo DML (PostgreSQL, SQL Server, SQLite)
algunas bases de datos aceptan with como prefijo a instrucciones DML (docs: PostgreSQL, SQL Server, SQLite).,
SQL Server también puede usar una consulta with como destino para instrucciones DML (básicamente creando una vista actualizable).
Funciones En with (Oracle)
La base de datos Oracle admite declaraciones de funciones y procedimientos dentro de la cláusula with desde la versión 12cR1 (documentación).
DML in with (PostgreSQL)
a partir de la 9.1, La base de datos PostgreSQL admite sentencias DML (insert, update, delete) dentro del cuerpo de with consultas (documentación)., Cuando se utiliza la cláusula (también propietaria) returning de la instrucción DML, la consulta with devuelve datos (por ejemplo, las filas recién insertadas).