Diseño de Consultas Complejas – SQL SERVER

Aprende a diseñar consultas complejas en SQL Server. Verás Consultas correlacionadas o JOIN, la cláusula GROUP BY y demás operadores.

Las consultas correlacionadas (join)

Un join o consulta correlacionada es la consulta que selecciona columnas de mas de una tabla, y las muestra en un único conjunto de resultados.

SELECT emp.id_emp , emp.paterno , dep.jefe
  FROM empleado emp INNER JOIN departamento dep
  ON emp.id_dep = dep.id_dep
  • lista_columnas es la lista de columnas a mostrar en el resultado de la consulta. Se recomienda que cada columna sea calificada con el alias de la tabla a la cual pertenece.
  • tipo_join indica si el join es interior (INNER), exterior (OUTER) o irrestricto (CROSS).
  • condición_del_join es una expresión que indica en base a qué columnas de cada una de las tablas se establece la relación entre ellas.

Inner join

Un inner join es la consulta correlacionada que combina todas las filas que están relacionadas en ambas tablas.

sql
SELECT emp.id_emp , emp.paterno , dep.jefe
  FROM empleado emp INNER JOIN departamento dep
  ON emp.id_dep = dep.id_dep

Outer join

Un outer join es la consulta correlacionada que entrega todas las filas que están relacionadas, y además:

  • las filas no relacionadas de la tabla izquierda (LEFT OUTER JOIN), o
  • las filas no relacionadas de la tabla derecha (RIGHT OUTER JOIN), o
  • las filas no relacionadas de ambas tablas (FULL OUTER JOIN)

Se considera como la tabla izquierda, a aquella que se menciona en la cláusula FROM.

Ejemplo LEFT OUTER JOIN

SELECT emp.id_emp , emp.paterno , dep.jefe
  FROM empleado emp
  LEFT OUTER JOIN departamento dep
  ON emp.id_dep = dep.id_dep

La consulta muestra a los 7 empleados que están registrados en departamentos existentes, y además al empleado 106 Barrios que figura registrado en un departamento inexistente.

Como empleado es la tabla izquierda, la consulta debe mostrar todas las filas de la tabla empleado; es decir, las filas relacionadas y las filas no relacionadas.

Ejemplo RIGHT OUTER JOIN

SELECT emp.id_emp , emp.paterno , dep.jefe
  FROM empleado emp
  RIGHT OUTER JOIN departamento dep
  ON emp.id_dep = dep.id_dep

La consulta muestra a los 7 empleados que están registrados en departamentos existentes, y además a los jefes de departamento que no tienen empleados a su cargo.

Como departamento es la tabla izquierda, la consulta debe mostrar todas las filas de la tabla empleado que tienen relación con la tabla departamento, y además las filas de la tabla departamento que no tienen relación con la tabla empleado.

Ejemplo FULL OUTER JOIN

SELECT emp.id_emp , emp.paterno , dep.jefe
 FROM empleado emp
 FULL OUTER JOIN departamento dep
 ON emp.id_dep = dep.id_dep

La consulta muestra a los 7 empleados que están registrados en departamentos existentes, y además a los empleados registrados en departamentos inexistentes, y a los jefes de departamento que no tienen empleados a su cargo.

Cross join

Un cross join es la consulta correlacionada que combina cada una de las filas de una de las tablas con todas las filas de la otra tabla.

No es necesario que exista una columna común para ejecutar cross join.

SELECT emp.id_emp , emp.paterno , dep.jefe
 FROM empleado emp
 CROSS JOIN departamento dep

Join con más de dos tablas

SELECT cli.nombre , pro.descripcion , ven.cantidad
 FROM clientes cli INNER JOIN ventas ven
 ON cli.id_cliente = ven.id_cliente
 INNER JOIN productos pro

 ON ven.id_producto = pro.id_producto

La cláusula GROUP BY

La cláusula GROUP BY se utiliza para agrupar las filas en base a determinado criterio, y luego ejecutar una operación que resume un atributo para cada uno de los grupos así formados. Por ejemplo, puede utilizar GROUP BY para agrupar todas las facturas por cliente, y luego calcular el monto total facturado de cada cliente.

SELECT lista_columnas , FUNCION_RESUMEN ( columna )
 FROM tabla
 GROUP BY lista_columnas
 [ HAVING condición_grupos ]
  • lista_columnas en GROUP BY debe ser la misma que en SELECT.
  • La cláusula GROUP BY permite agrupar las filas en base a los valores de una o mas columnas, y luego efectuar alguna operación de resumen sobre cada grupo asi generado.
  • HAVING permite especificar cuáles de los grupos generados se deben mostrar en el resultado de la consulta.

Cuando use la cláusula GROUP BY, considere lo siguiente:

SQL Server produce una columna de valores para cada criterio de agrupación definido en GROUP BY.

  • SQL Server retorna solo una fila para cada grupo que especifica; no devuelve la información de detalle.
  • Todas las columnas que se especifican en la cláusula GROUP BY deben ser incluidas en la lista SELECT.
  • Si incluye una cláusula WHERE, SQL Server agrupa solo las filas que satisfacen la condición de la cláusula WHERE.
  • Al usar la cláusula GROUP BY en columnas que contienen valores NULL, éstos se procesan como un grupo.

Use la palabra clave ALL con la cláusula GROUP BY para desplegar todas las filas con valores nulos en las columnas, sin tener en cuenta si las filas satisfacen la cláusula WHERE.

Ejemplo 1
Este ejemplo devuelve información sobre las órdenes de la tabla orderhist. La consulta agrupa y lista cada productid y calcula la cantidad (quantity) total pedida. La cantidad total es calculada con la función de resumen SUM, y se despliega un valor por cada producto en el resultado.

USE northwind
SELECT productid, SUM( quantity ) AS cantidad_total
  FROM orderhist
  GROUP BY productid

Uso de GROUP BY con la cláusula HAVING

Así como la cláusula WHERE se utiliza como un filtro de filas, la cláusula HAVING se utiliza como un filtro de grupos. Cuando use la cláusula HAVING, considere lo siguiente:

Use la cláusula HAVING solo con la cláusula GROUP BY para restringir la agrupación. Puede tener hasta 128 condiciones en una cláusula HAVING. Cuando define condiciones múltiples, debe combinarlas con los operadores lógicos (AND, OR, o NOT).

La cláusula HAVING debe hacer referencia a cualquiera de las columnas que aparecen en la lista SELECT. No utilice la palabra clave ALL con la cláusula HAVING.

Ejemplo
Este ejemplo devuelve información sobre las órdenes de la tabla orderhist. La consulta agrupa y lista cada productid y calcula la cantidad (quantity) total pedida. La cantidad total es calculada con la función de resumen SUM, y se despliega un valor por cada producto en el resultado. Solo se mostrarán los productos cuya cantidad total pedida es superior a las 300 unidades.

USE northwind
SELECT productid, SUM( quantity ) AS cantidad_total
FROM orderhist
GROUP BY productid

HAVING SUM( quantity ) > 300

Uso de GROUP BY con el operador ROLLUP

Use la cláusula GROUP BY con el operador ROLLUP para resumir los valores de grupo. El operador ROLLUP se usa normalmente para producir promedios acumulados o sumas acumuladas. Puede hacer esto aplicando la función de resumen en la lista de columnas de SELECT para cada columna en la cláusula GROUP BY moviéndose de izquierda a derecha.

Ejemplo
En este ejemplo, GROUP BY agrupa las filas de la tabla titles en base a la combinación de los valores de las columnas type y pub_id, y para cada una de las combinaciones obtenidas calcula la suma de la columna ytd_sales.

Luego, el operador ROLLUP agrupa en base a los valores de la primera columna en GROUP BY (type) sin tomar en cuenta los valores de la segunda columna en GROUP BY, y calcula la suma de ytd_sales para los grupos asi obtenidos.

Finalmente calcula la suma de la columna ytd_sales para toda la tabla.

SELECT type, pub_id, SUM( ytd_sales ) AS ytd_sales
  FROM titles
  GROUP BY type, pub_id

WITH ROLLUP
La imagen tiene un atributo ALT vacío; su nombre de archivo es consulta-compleja-13-300x258.png

En el ejemplo, GROUP BY ha agrupado en base a type y pub_id obteniendo las combinaciones business/0736 y business/1389. Para cada una de ellas calculo la suma de ytd_sales (18722 y 12066 respectivamente).

Luego, el operador ROLLUP agrupó en base a la columna type sin considerar la columna pub_id obteniendo la combinación business/NULL, y para este grupo calculó la suma de ytd_sales (30788). Finalmente, calculó la suma de la columna ytd_sales para toda la consulta: combinación NULL/NULL con el resultado 97446.

Uso de GROUP BY con el operador CUBE

Use la cláusula GROUP BY con el operador CUBE para crear y resumir todas las posibles combinaciones de grupos basadas en la cláusula GROUP BY.

Ejemplo
En este ejemplo, el operador CUBE entrega las mismas filas que el operador ROLLUP, pero añade las combinaciones generadas al agrupar en base a la segunda columna en GROUP BY (pub_id) sin tomar en cuenta los valores de la primera columna (type).

SELECT type, pub_id, SUM( ytd_sales ) AS ytd_sales
  FROM titles
  GROUP BY type, pub_id
  WITH CUBE
La imagen tiene un atributo ALT vacío; su nombre de archivo es consulta-compleja-14-300x268.png
(18 row(s) affected)

El operador CUBE ha generado tres filas adicionales al agrupar en base a la columna pub_id.

Resúmenes detallados con COMPUTE…BY

SELECT lista_columnas
  FROM tabla
  ORDER BY columnaX
  COMPUTE FUNCION_RESUMEN ( columna ) BY columnaX

columnaX es la columna en base a cuyos valores se formarán los grupos.

La cláusula COMPUTE…BY permite agrupar las filas en base a los valores de una o más columnas, y luego efectuar alguna operación de resumen sobre cada grupo así generado. La diferencia con GROUP BY es que permite mostrar información más detallada de cada grupo.

Ejemplo:

SELECT tem_cod , pub_nom , pub_aut
  FROM publicacion
  ORDER BY tem_cod , pub_nom
  COMPUTE COUNT ( pub_nom ) BY tem_cod

Esta consulta agrupa las filas de la tabla publicacion en base al valor de la columna tem_cod, y luego, cuenta cuántas filas hay (¿cuántas publicaciones?) en cada grupo así generado. Muestra el código de tema, el título de la publicación, y el autor para cada publicación contenida en cada uno de los grupos.

Eliminación de filas duplicadas del resultado de una consulta

SELECT DISTINCT lista_columnas
FROM tabla
[ WHERE condición_filas ]
[ ORDER BY columna(s) ]

Cuando se utiliza la cláusula DISTINCT, SQL elimina las filas que se duplican en el resultado de la consulta. Ejemplo

SELECT DISTINCT pub_aut FROM publicacion

Muestra una lista de todos los autores diferentes registrados en la tabla publicación.

El operador UNION

El operador UNION une los resultados de dos o mas sentencias SELECT en un solo conjunto de resultados. Use el operador UNION cuando los datos que desea recuperar residen en diferentes localizaciones y no puede acceder a ellos con una sola consulta.

Cuando use el operador UNION considere lo siguiente:

  • SQL Server requiere que las consultas a las tablas referenciadas tengan el mismo número de columnas, los mismos tipos de datos, y que las columnas se encuentren en el mismo orden en la lista del SELECT.
  • SQL Server elimina las filas duplicadas en el resultado. Sin embargo, si usa la opción ALL, todas las filas (incluso las duplicadas) son incluidas en el resultado.
  • Debe especificar los nombres de las columnas en la primera sentencia del SELECT. Por consiguiente, si quiere definir los nuevos títulos de las columnas para el resultado, debe crear los seudónimos de las columnas en la primera sentencia SELECT.
  • Si quiere que el resultado completo sea devuelto en un orden específico, debe especificar el orden e incluir la cláusula ORDER BY dentro de la sentencia UNIÓN.
sentencia_select
UNlON [ALL]
sentencia_ select
UNION [ALL]
sentencia_select ,...

Ejemplo
Este ejemplo combina dos resultados. El primer resultado devuelve el nombre, la ciudad, y código postal de cada cliente de la tabla customers. El segundo resultado devuelve el nombre, ciudad, y el código postal de cada empleado de la tabla employees.

USE northwind
SELECT nombre = ( firstname + ' '+ lastname ), city, postalcode
  FROM employees
UNION
SELECT companyname, city, postalcode
  FROM customers

Subconsultas

Un subconsulta es una declaración SELECT anidada dentro una sentencia SELECT, INSERT, UPDATE o DELETE o dentro de otra subconsulta.

Porqué usar subconsultas

Use las subconsultas para solucionar una consulta compleja en una serie de pasos lógicos y, en consecuencia, para resolver un problema con una sola sentencia.

Porque no usar subconsultas

Se recomienda el uso de joins en lugar de subconsultas porque Microsoft SQL Server procesa un join más rápido que las subconsultas. El uso de las subconsultas puede forzar al SQL Server Query Optimizer a ejecutar pasos adicionales, como ordenar, y puede influir en el rendimiento del proceso.

Si una consulta no requiere de múltiples pasos, puede no ser necesario usar una subconsulta.

Uso de subconsultas

  • Cuando decida usar subconsultas, considere los hechos siguientes:
  • La subconsulta se especifica entre paréntesis.
  • La lista SELECT de una subconsulta solo puede contener una expresión o nombre de columna que devuelve un valor.
  • Puede utilizar una subconsulta en cualquier lugar donde se define una expresión, con la condición de que devuelva un solo valor o un conjunto de valores.
  • No se puede usar subconsultas en columnas que contienen texto y tipos de datos de imagen.

Subconsultas que retornan un solo valor

Se definen:

  • en la lista de columnas del SELECT externo
  • en la cláusula WHERE con un operador de comparación (Test de comparación)

Esto a sido un post sobre el diseño de consultas complejas en SQL Server, si deseas seguir profundizando te recomendamos.

Contenido relacionado:

Te puede interesar

Deja una respuesta

Tu dirección de correo electrónico no será publicada.