Este es el segundo de una serie de artículos sobre subconsultas. En este artículo, analizamos las subconsultas en la lista de columnas de la instrucción SELECT. Otros artículos discuten sus usos en otras cláusulas.
Todos los ejemplos de esta lección están basados en Microsoft SQL Server Management Studio y la base de datos AdventureWorks2012. Puede comenzar a usar estas herramientas gratuitas con mi Guía Introducción a SQL Server.
Uso de subconsultas en la instrucción Select
Cuando se coloca una subconsulta en la lista de columnas, se usa para devuelve valores únicos. En este caso, puede pensar en la subconsulta como una expresión de valor único. El resultado devuelto no es diferente de la expresión «2 + 2». Por supuesto, las subconsultas también pueden devolver texto, ¡pero entiendes el punto!
Cuando se trabaja con subconsultas, la instrucción principal a veces se llama consulta externa. Las subconsultas se incluyen entre paréntesis, esto hace que sea más fácil detectarlas. .
Tenga cuidado al usar subconsultas. Pueden ser divertidas de usar, pero a medida que agrega más a su consulta, pueden comenzar a ralentizar su consulta.
Subconsulta simple para calcular el promedio
Comencemos con una consulta simple para mostrar SalesOrderDetail y compararla con el promedio general de SalesOrderDetail LineTotal. La declaración SELECT que usaremos es:
SELECT SalesOrderID,LineTotal,(SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotalFROM Sales.SalesOrderDetail;
Esta consulta devuelve resultados como:
La subconsulta, que se muestra en rojo arriba, se ejecuta primero para obtener el LineTotal promedio.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetail
Este resultado se vuelve a insertar en la lista de columnas y la consulta continúa. Hay varias cosas que quiero señalar :
- Las subconsultas están entre paréntesis .
- Cuando se utilizan subconsultas en una instrucción SELECT, solo pueden devolver un valor. Esto debería tener sentido, simplemente seleccionar una columna devuelve un valor para una fila, y debemos seguir el mismo patrón.
- En general, la subconsulta se ejecuta solo una vez para toda la consulta y su resultado se reutiliza . Esto se debe a que el resultado de la consulta no varía para cada fila devuelta.
- Es importante usar alias para los nombres de las columnas para mejorar la legibilidad.
Subconsulta simple en la expresión
Como es de esperar, el resultado de una subconsulta se puede utilizar en otras expresiones. Basándonos en el ejemplo anterior, usemos la subconsulta para determinar cuánto varía nuestro LineTotal del promedio.
La varianza es simplemente el LineTotal menos el promedio de LineTotal. En la siguiente subconsulta, lo coloreé de azul. Aquí está la fórmula para la variación:
LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail)
La instrucción SELECT encerrada entre paréntesis es la subconsulta. Como en el ejemplo anterior, esta consulta se ejecutará una vez, devolverá un valor numérico, que luego se resta de cada valor LineTotal.
Aquí está la consulta en su forma final:
SELECT SalesOrderID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotal, LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS VarianceFROM Sales.SalesOrderDetail
Aquí está el resultado:
Cuando trabajo con subconsultas en sentencias select, normalmente construyo y pruebe la subconsulta primero. Las sentencias SELECT pueden complicarse muy rápidamente. Lo mejor es ir construyéndolos poco a poco. Al construir y probar las distintas piezas por separado, realmente ayuda con la depuración.
Consultas correlacionadas
Hay formas de incorporar los valores de la consulta externa en las cláusulas de la subconsulta. Estos tipos de consultas se denominan subconsultas correlacionadas, ya que los resultados de la subconsulta están conectados, de alguna forma, a los valores de la consulta externa. Las consultas correlacionadas a veces se denominan consultas sincronizadas.
Si tiene problemas para saber qué significa correlacionar, consulte esta definición de Google:
Correlación: «tener una relación o conexión mutua, en el que una cosa afecta o depende de otra. «
Un uso típico de una subconsulta correlacionada es usar una de las columnas de la consulta externa en la cláusula WHERE de la consulta interna. Esto es de sentido común en muchos casos que desea restringir la consulta interna a un subconjunto de datos.
Ejemplo de subconsulta correlacionada
Proporcionaremos un ejemplo de subconsulta correlacionada informando cada SalesOrderDetail LineTotal, y el Promedio LineTotals para las ventas generales Pedido.
Esta solicitud difiere significativamente de nuestros ejemplos anteriores ya que el promedio que calculamos varía para cada pedido de venta.
Aquí es donde entran en juego las subconsultas correlacionadas. valor de la consulta externa e incorpórelo a los criterios de filtro de la subconsulta.
Tomemos un mire cómo calculamos el total de línea promedio. Para hacer esto, he reunido una ilustración que muestra la instrucción SELECT con subconsulta.
Para profundizar en el diagrama. La instrucción SELECT consta de dos partes, la consulta externa y la subconsulta. La consulta externa se utiliza para recuperar todas las líneas SalesOrderDetail.La subconsulta se usa para buscar y resumir las líneas de detalles de la orden de venta para un SalesOrderID específico.
Si tuviera que verbalizar los pasos que vamos a tomar, los resumiría como:
- Obtenga el SalesOrderID.
- Devuelva el Promedio LineTotal de todos los elementos SalesOrderDetail donde el SalesOrderID coincide.
- Continúe con el siguiente SalesOrderID en la consulta externa y repita los pasos 1 y 2.
La consulta que puede ejecutar en la base de datos AdventureWork2012 es:
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail SOD
Estos son los resultados de la consulta:
Hay un par de elementos para señalar.
- Puede ver que usé alias de columna para ayudar a que los resultados de la consulta sean más fáciles de leer.
- También usé un alias de tabla, SOD, para consulta externa. Esto hace posible utilizar los valores de la consulta externa en la subconsulta. De lo contrario, la consulta no está correlacionada.
- El uso de los alias de la tabla hace que sea inequívoco qué columnas son de cada tabla.
Desglose de la subconsulta correlacionada
Intentemos ahora desglosar esto usando SQL.
Para empezar, supongamos que vamos a obtener nuestro ejemplo para SalesOrderDetailID 20. El SalesOrderID correspondiente es 43661.
Obtener el LineTotal promedio para este elemento es fácil
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetailWHERE SalesOrderID = 43661
Esto devuelve el valor 2181.765240.
Ahora que tenemos el promedio, podemos conéctelo a nuestra consulta
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, 2181.765240 AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
El uso de subconsultas se convierte en
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43661) AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
La consulta final es :
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail AS SOD
Subconsulta correlacionada con una tabla diferente
Una subconsulta correlacionada, o para el caso cualquier subconsulta, puede usar una tabla diferente a la la consulta externa. Esto puede resultar útil cuando está trabajando con una tabla «principal», como SalesOrderHeader, y desea incluir como resultado un resumen de las filas secundarias, como las de SalesOrderDetail.
Devolvemos el OrderDate, TotalDue y número de líneas de detalle de pedidos de venta. Para ello, podemos utilizar el siguiente diagrama para orientarnos:
Para hacer esto, incluiremos una subconsulta correlacionada en nuestra declaración SELECT para devolver el COUNT de líneas SalesOrderDetail. Nos aseguraremos de contar el elemento SalesOrderDetail correcto filtrando el SalesOrderID de la consulta externa.
Aquí está la declaración SELECT final:
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Los resultados son:
Algunas cosas a tener en cuenta con este ejemplo son:
- La subconsulta está seleccionando datos de una tabla diferente a la consulta externa.
- Usé tabla y alias de columna para facilitar la lectura del SQL y los resultados.
- Asegúrese de marcar dos veces k tu cláusula where! Si olvida incluir el nombre de la tabla o los alias en la cláusula WHERE de la subconsulta, la consulta no se correlacionará.
Subconsultas correlacionadas versus combinaciones internas
Es importante para comprender que puede obtener los mismos resultados utilizando una subconsulta o una combinación. Aunque ambos devuelven los mismos resultados, cada método tiene ventajas y desventajas.
Considere el último ejemplo en el que contamos las líneas de pedido para los artículos de SalesHeader.
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Esta misma consulta se puede hacer usando INNER JOIN junto con GROUP BY como
SELECT SO.SalesOrderID, OrderDate, TotalDue, COUNT(SOD.SalesOrderDetailID) as LineCountFROM Sales.SalesOrderHeader SO INNER JOIN Sales.SalesOrderDetail SOD ON SOD.SalesOrderID = SO.SalesOrderIDGROUP BY SO.SalesOrderID, OrderDate, TotalDue
¿Cuál es más rápido?
Encontrarás que muchas personas dirán que evites las subconsultas, ya que son más lentas. Ellos argumentarán que la subconsulta correlacionada tiene que «ejecutarse» una vez por cada fila devuelta en la consulta externa, mientras que INNER JOIN solo tiene que hacer una pasada a través de los datos.
¿Yo mismo? el plan de consulta. Seguí mi propio consejo para los dos ejemplos anteriores y descubrí que los planes eran los mismos.
Eso no quiere decir que los planes cambiarían si hubiera más datos, pero mi punto es que no debe simplemente hacer suposiciones. La mayoría de los optimizadores de DBMS SQL son realmente buenos para descubrir la mejor manera de ejecutar su consulta. Tomarán sus sintaxis, como una subconsulta o INNER JOIN, y las usarán para crear una plan de ejecución real.
¿Cuál es más fácil de leer?
Dependiendo de con qué se sienta cómodo, puede encontrar el ejemplo INNER JOIN más fácil de leer que la consulta correlacionada. Personalmente, en este ejemplo, me gusta la subconsulta correlacionada porque parece más directa. Es más fácil para mí ver lo que se está contando.
En mi opinión, INNER JOIN es menos directo. Primero debe ver que todas las filas de detalles de ventas se devuelven manualmente y luego se resumen. Realmente no entiende esto hasta que lee la declaración completa.
¿Cuál es mejor?
Dígame lo que piensa. Me gustaría saber si prefiere utilizar la subconsulta correlacionada o el ejemplo INNER JOIN.