sql - tipos - Procesalmente transformar la subconsulta en unirse
subconsultas sql w3schools (7)
A menudo es posible, y lo que es bueno es que el optimizador de consultas puede hacerlo automáticamente, por lo que no tiene que preocuparse por ello.
¿Hay un procedimiento o algoritmo generalizado para transformar una subconsulta SQL en una unión, o viceversa? Es decir, ¿hay un conjunto de operaciones tipográficas que se pueden aplicar a una declaración de consulta SQL sintácticamente correcta que contiene una subconsulta que da como resultado una declaración funcionalmente equivalente sin una subconsulta? Si es así, ¿qué son (es decir, cuál es el algoritmo) y en qué casos no se aplican?
En SQL Server, al menos, el optimizador puede hacer esto a voluntad, pero estoy seguro de que hay restricciones cuando lo hace. Estoy seguro de que probablemente fue la tesis doctoral de alguien para poder hacerlo en la computadora.
Cuando lo hago de la manera humana a la vieja usanza, es bastante sencillo, particularmente si la subconsulta ya tiene un alias, primero se puede extraer a una Expresión de tabla común.
En un nivel realmente alto. para transformar una subconsulta en un JOIN:
- FROM: Los nombres de las tablas entran en FROM
- ÚNASE Las partes de la cláusula WHERE con nombres de tabla en ambos lados determinan (a) el tipo de JOIN (b) la condición de unión
- DONDE Las partes de la cláusula where sin nombres de tabla en ambos lados entran en la cláusula WHERE
- SELECCIONAR Los nombres de columna de Sub-Query van a SELECT
La transformación de un JOIN a Sub-Query implica el reverso de la lógica anterior
Convertir una subconsulta en un JOIN puede ser bastante sencillo:
Cláusula IN
FROM TABLE_X x
WHERE x.col IN (SELECT y.col FROM TABLE_Y y)
... se puede convertir a:
FROM TABLE_X x
JOIN TABLE_Y y ON y.col = x.col
Su criterio de JOIN es donde tiene una comparación directa.
Cláusula EXISTS
Pero hay complicaciones cuando mira la cláusula EXISTS . Los EXISTS se suelen correlacionar, donde la subconsulta se filtra por criterios de la (s) tabla (s) fuera de la subconsulta. Pero EXISTS solo sirve para devolver un booleano según los criterios.
FROM TABLE_X x
WHERE EXISTS (SELECT NULL
FROM TABLE_Y y
WHERE y.col = x.col)
...convertido:
FROM TABLE_X x
JOIN TABLE_Y y ON y.col = x.col
Debido al booleano, existe el riesgo de que aparezcan más filas en el conjunto de resultados.
SELECT s en la cláusula SELECT
Estos siempre se deben cambiar, con prejuicio :
SELECT x.*,
(SELECT MAX(y.example_col)
FROM TABLE_Y y
WHERE y.col = x.col)
FROM TABLE_X x
Probablemente esté notando un patrón ahora, pero hice esto un poco diferente para un ejemplo de vista en línea:
SELECT x.*,
z.mc
FROM TABLE_X x
JOIN (SELECT y.col, --inline view within the brackets
MAX(y.example_col) ''mc''
FROM TABLE_Y y
GROUP BY y.col) z ON z.col = x.col
La clave es asegurarse de que el conjunto de resultados de la vista en línea incluya la (s) columna (s) necesaria (s) para unirse, junto con las columnas.
LEFT JOIN s
Es posible que haya notado que no tenía ningún ejemplo de LEFT JOIN - esto solo sería necesario si las columnas de la subconsulta usan pruebas NULL ( COALESCE en casi cualquier base de datos en estos días, NVL2 o NVL2 , IFNULL de IFNULL , IFNULL SQL Server, etc. ...):
SELECT x.*,
COALESCE((SELECT MAX(y.example_col)
FROM TABLE_Y y
WHERE y.col = x.col), 0)
FROM TABLE_X x
Convertido:
SELECT x.*,
COALESCE(z.mc, 0)
FROM TABLE_X x
LEFT JOIN (SELECT y.col,
MAX(y.example_col) ''mc''
FROM TABLE_Y y
GROUP BY y.col) z ON z.col = x.col
Conclusión
No estoy seguro si eso satisfará sus necesidades tipográficas, pero espero haber demostrado que la clave es determinar qué es el criterio JOIN. Una vez que conozca la (s) columna (s) involucrada (s), conocerá la (s) tabla (s) involucradas.
Esto califica un fuerte "depende".
En un nivel, si estás hablando de consultas compatibles con ANSI SQL 89 o 92 *, entonces supongo que es definitivo tal vez. Si tiene consultas simples (o incluso no tan simples) que consisten en cláusulas "básicas" de selección, de y "y", entonces sí, me gustaría pensar que es matemáticamente posible definir procesos y procedimientos para crear y "descrear" subconsultas (Sin embargo, cómo puede determinar cuándo forma algorítmicamente una subconsulta está más allá de mí). Creo que este "razonamiento" podría aplicarse a las uniones externas y subconsultas correlacionadas.
En otro nivel, diría "de ninguna manera". La mayoría de las veces que escribo una subconsulta, es porque no puedo pensar en una forma de encajarla en la consulta "principal". Muy raramente esto involucra subconsultas correlacionadas, pero la mayoría de las veces involucra lo que son, estoy bastante seguro, extensiones propietarias de los estándares. ¿Cómo podría explicar los pivots, los unpivots, las funciones de clasificación, las cláusulas TOP N (que bien pueden ser normas ANSI, admitiré que nunca las he leído de principio a fin), FULL o EXTERNALMENTE APLICAR, y cosas por el estilo? Y eso es solo partes de SQL Server, estoy seguro de que Oracle, DB2, MYSQL y casi todos los demás jugadores tienen sus propias extensiones que rompen el modelo relacional "purista".
Por supuesto, dicen que es imposible probar un negativo. Resumiría que "no se puede hacer hasta que se demuestre lo contrario", deje la prueba a los académicos y teóricos, y señale que, incluso entonces, el sistema que adquiera no lo admitirá a menos que tenga sentido financiero para el fabricante. trabajar en. (¿Algún sistema es compatible con UNIÓN EXTERIOR todavía?)
** Un poco de Google no produjo ninguna referencia a un tercer estándar ANSI SQL. Sé que oí hablar de eso hace años, ¿alguna vez pasó? *
Un sistema completamente automático para transformar consultas de subconsultas en uniones sería relativamente difícil de construir. Debería realizar una consulta de entrada, analizarla en un árbol de análisis sintáctico y luego realizar algunas coincidencias de patrones bastante complejas en el árbol de análisis sintáctico, reemplazando secciones del árbol con nuevas secciones del árbol de análisis sintáctico. Al final, se recorre el árbol para generar la nueva consulta.
Puede haber repercusiones de rendimiento sorprendentemente buenas o malas. En ocasiones, una subconsulta es mucho más rápida que una combinación. Algunas veces es el inverso.
Esta pregunta se basa en un conocimiento básico de álgebra relacional. Debe preguntarse qué tipo de unión se está realizando. Por ejemplo, un LEFT ANTI SEMI JOIN es como una cláusula WHERE NOT EXISTS.
Algunas combinaciones no permiten la duplicación de datos, otras no permiten la eliminación de datos. Otros permiten que haya campos adicionales disponibles. Discuto esto en mi blog en http://msmvps.com/blogs/robfarley/archive/2008/11/09/join-simplification-in-sql-server.aspx
Además, no sienta que necesita hacer todo en JOINs. El Optimizador de consultas debe encargarse de todo esto por usted y, a menudo, puede hacer que sus consultas sean mucho más difíciles de mantener de esta manera. Puede encontrar que usa una cláusula GROUP BY extensa y que tiene filtros WHERE ... IS NULL interesantes, que solo servirán para desconectar la lógica comercial del diseño de la consulta.
Una subconsulta en la cláusula SELECT (esencialmente una búsqueda) solo proporciona un campo adicional, no duplicación o eliminación. Por lo tanto, deberá asegurarse de aplicar los valores GROUP BY o DISTINCT en su JOIN, y usar OUTER JOIN para garantizar que el comportamiento sea el mismo.
Una subconsulta en la cláusula WHERE nunca puede duplicar datos, o proporcionar columnas adicionales a la cláusula SELECT, por lo que debe usar GROUP BY / DISTINCT para verificar esto. DONDE EXISTE es similar. (Este el SEMI IZQUIERDO SE UNE)
DONDE NO EXISTE (IZQUIERDA ANTI SEMI JOIN) no proporciona datos, y no duplica las filas, pero puede eliminar ... para esto, necesita hacer UNIONES IZQUIERDAS y buscar NULLs.
Pero el Optimizador de consultas debe manejar todo esto por usted. De hecho, me gusta tener subconsultas ocasionales en la cláusula SELECT, porque deja muy claro que no estoy duplicando o eliminando filas. El QO puede arreglarlo para mí, pero si uso una función de vista o de tabla con valores de tabla, quiero dejar claro a los que me siguen que el QO puede simplificarlo mucho. Eche un vistazo a los Planes de ejecución de su consulta original, y verá que el sistema proporciona las combinaciones INNER / OUTER / SEMI por usted.
Lo que realmente debe evitar (al menos en SQL Server) son las funciones que utilizan BEGIN y END (como las funciones escalares). Pueden sentir que simplifican su código, pero en realidad se ejecutarán en un contexto separado, ya que el sistema los considera como de procedimiento (no simplificables).
Hice una sesión sobre este tipo de cosas en la reciente conferencia SQLBits V. Fue grabado, por lo que deberías poder verlo en algún momento (¡si puedes aguantar mis bromas!)