Operadores de conjunto en SQL-Server: UNION, INTERSECT y EXCEPT

Los operadores de conjunto UNION, INTERSECT y EXCEPT nos permite combinar en una misma salida el resultado de distintas consultas SELECT, construyendo así una consulta más compleja, lo que se llama una consulta compuesta. Para poder combinar dos consultas con estos operadores necesitamos que se cumplan dos requisitos:

  • Que ambas consultas devuelvan el mismo número de columnas.
  • Que estas columnas contengan el mismo tipo de datos, o al menos tipos de datos que se puedan convertir de forma implícita.

Estos tres operadores se incorporaron a SQL-Server a partir de la versión 2008 y están también disponibles en la base de datos SQL de la plataforma Azure. Los tres operadores aceptan además el parámetro ALL, que modificará ligeramente los resultados ¿Cómo funcionan y qué diferencia hay entre ellas?

EXCEPT:

Este operador encuentra la diferencia entre las dos consultas y devuelve las filas que pertenecen únicamente a la primera consulta. Es decir, si una tupa aparece tanto en la consulta de la izquierda como en la de la derecha no será incluida en el resultado final. Si aparece solo en la de izquierda y en la de la derecha no, entonces será devuelta una vez.

Si añadimos ALL al EXCEPT notaremos una pequeña diferencia. Al igual que con el EXCEPT a secas el operador buscará la diferencia entre las dos consultas, pero los datos devueltos cambian. En este caso si una tupla aparece un número m de veces en la primera consulta, y la misma tupla aparece un número n veces en la segunda consulta, entonces esa tupla aparece m – n veces en la respuesta de salida, si dicha resta es mayor que 0.

/*consultamos una tabla de Productos 
y sacamos todos los resultados únicos 
que no que existen en la consulta sobre la tabla Almacén*/

Select Descripcion, Codigo from Productos
EXCEPT
Select Descripcion, Codigo from Almacen

/*Con ALL si la tupa Descripción,Codigo existiese tres veces
en el resultado de la primera consulta y una vez en la segunda
entonces en el resultado final saldría dos veces*/
Select Descripcion, Codigo from Productos
EXCEPT ALL
Select Descripcion, Codigo from Almacen

INTERSECT:

Este operador combina los resultados de dos consultas en un único resultado que comprime todas las filas comunes para ambas consultas. Es decir, funcionaría como un AND lógico: devuelve solo las ocurrencias existentes en ambas consultas.

Si añadimos ALL a este operador el resultado también cambiará. En ese caso si una tupla aparece un número m de veces en el resultado de la primera consulta y la misma tupla aparece n veces en la segunda, entonces esa tupla aparece el menor número de entre m o n en la respuesta de salida.

/*consultamos una tabla de Productos 
y sacamos todos los resultados únicos 
que también existen en la consulta sobre la tabla Almacén*/

Select Descripcion, Codigo from Productos
INTERSECT
Select Descripcion, Codigo from Almacen

/*Con ALL si la tupa Descripción,Codigo existiese tres veces
en el resultado de la primera consulta y una vez en la segunda
entonces en el resultado final saldría una vez solo*/
Select Descripcion, Codigo from Productos
INTERSECT ALL
Select Descripcion, Codigo from Almacen

UNION:

Finalmente vamos con UNION. Si antes os decía que INTERSECT funciona como un operador lógico AND entonces UNION funcionaría como un operador lógico OR. Devuelve las filas únicas que existen o en la consulta de la izquierda o en la de la derecha.

En este caso el operador ALL lo que hará será modificar el resultado del UNION de forma que en lugar de recibir solo las filas únicas recibamos tantas filas como haya en la primera consulta y en la segunda, un poco la operación contraria a la que realiza EXCEPT ALL. En este caso si una tupla aparece un número m de veces en la primera consulta, y la misma tupla aparece un número n veces en la segunda consulta, entonces esa tupla aparece m + n veces en la respuesta de salida.

/*consultamos una tabla de Productos 
y la tabla Almacén y sacamos los resultados únicos, distintos*/

Select Descripcion, Codigo from Productos
UNION
Select Descripcion, Codigo from Almacen

/*Con ALL si la tupa Descripción,Codigo existiese tres veces
en el resultado de la primera consulta y una vez en la segunda
entonces en el resultado final saldría cuatro veces*/
Select Descripcion, Codigo from Productos
UNION ALL
Select Descripcion, Codigo from Almacen

La función lógica CHOOSE() en SQL-Server

La función lógica CHOOSE() se añadió a SQL-Server desde la versión 2012, y su funcionamiento es similar a recuperar valores de un array. Es una función que recibe al menos tres valores: el primero, que será un valor índice, y tras él una serie de valores separados por comas, requeríendose al menos dos. La función devolverá el valor de la lista que coincida posicionalmente con el valor del índice.

Veámoslo con un ejemplo simple:

--Esto devolvería "Pringao" por ser el tercer valor:
SELECT CHOOSE ( 3, 'Jefazo', 'Jefecillo', 'Pringao', 'Becario' ) AS TuPuesto  

Lógicamente el primer valor no tiene por qué ser una constante, puede salir de una consulta. Veámoslo en un ejemplo similar al de arriba:

--Si el IdPuesto es 1 devuelve "Jefazo", si es 4 devuelve "Becario"
SELECT IdPuesto, CHOOSE ( IdPuesto, 'Jefazo', 'Jefecillo', 'Pringao', 'Becario' ) AS NombrePuesto from Plantilla  

O de una variable:

SELECT @Puesto, CHOOSE ( @Puesto, 'Jefazo', 'Jefecillo', 'Pringao', 'Becario' ) AS NombrePuesto from Plantilla 


Common Table Expression (CTE): la sentencia WITH de SQL-Server

Las expresiones de tabla común (common table expression o CTE) fueron añadidas por Microsoft a SQL-Server a partir de la versión 2008. Cuando hablamos de CTE hablamos de un artefacto que nos mantiene en memoria el resultado de una consulta, que podremos llamar luego dentro de esa misma consulta. Esta cláusula también se puede utilizar en una instrucción CREATE VIEW como parte de la instrucción SELECT que la define.

Sintaxis de una CTE

La sintaxis básica de una CTE sería la siguiente:

WITH nombreDeNuestra_CTE (Columna1, Columna2, Columna3)  
AS  
-- Aquí definimos la consulta que la crea.  
(  
    SELECT Columna1, Columna2, Columna3
    FROM BaseDatos.dbo.Tabla
    Where Condicion=Condicion
)  

Por ejemplo, imagina que tenemos una tabla con vendedores y otra con sus ventas, donde el Id del vendedor actúa como clave externa en cada línea de ventas. Podemos usar una CTE para tener un resultado temporal con su total de ventas de 2018. Algo así:

WITH Ventas_CTE (IdVendedor, NombreVendedor, TotalVendido)  
AS  
(  
    SELECT 
      v.Id as IdVendedor, 
      v.Nombre as NombreVendedor, 
      SUM(vt.Importe) AS TotalVentas 
    FROM Vendedores v inner join ventas vt on v.Id = vt.IdVendedor
    WHERE YEAR(vt.Fecha)=2018
)  

Ahora podríamos usar la CTE del ejemplo de arriba en una consulta como si de una tabla normal se tratase. Por ejemplo, podríamos sacar todos los departamentos donde haya vededores que hayan superado los 100.000 euros en ventas haciendo un join contra una tabla de departamentos:

SELECT Distinct
  d.Nombre
FROM
  Departamentos d
inner join
  Departamentos_Vendedores vd
on
  d.Id = vd.Idep
inner join
  Ventas_CTE v
on
  v.IdVendedor = vd.IdVend
where
  vd.TotalVendido > 100000

Hay que recordar que las cláusulas INTO, ORDER BY, FOR BROWSE y OPTION no están permitidas en una consulta de definición de una CTE. Sí permite el uso de operadores de conjuntos como UNION ALL, UNION, INTERSECT o EXCEPT y también permite referencias a tablas externas, e incluso a tablas situadas en servidores remotos.

En el ejemplo hemos usado un SELECT después de crear nuestra CTE, pero podríamos haber usado una instrucción INSERT, DELETE o UPDATE también. Incluso la podemos utilizar para definir un cursor.

¿Por qué usar una CTE?

Aparte del potencial que tienen las CTE por permitir crear una CTE recursiva, cosa de la que ya hablaremos más adelante en otro artículo, las CTE principalmente nos permiten sustituir a subconsultas y a variables de tabla.

En el caso de las subconsultas realmente la CTE no nos da ninguna ventaja en el rendimiento, pero sí nos permitirá tener un código más ordenado y más manejable, lo que facilita la legibilidad del mismo y las tareas de mantenimiento.

En el caso de la variables de tipo tabla ahí sí que las CTE nos dan un rendimiento mejor en la consulta, por lo que siempre serán una opción más recomendable.

También uno podría pensar en cambiar una Vista por una CTE. Si se trata de conjuntos de datos muy grandes, con muchas líneas, la Vista tendrá un rendimiento mayor por tratarse de un objeto creado en la base de datos que no permite definir índices (ya hablamos aquí sobre cuándo esto es recomendable y cuándo no).

SQL-Server: Usar CASE en una cláusula ORDER BY

La ordenación de resultados en una consulta SQL suele ralentizar la respuesta de la consulta, aunque en muchos casos necesitamos tener nuestro resultado ordenado. Hoy me veía con este caso particular, en una aplicación que conecta con una base de datos de SQL-Server: en una pantalla se muestran una serie de líneas de deuda, algunas haciendo referencia a tratamientos ya realizados y otras a tratamientos pendientes. En caso de que estén realizados habría que ordenarlos por fecha de realización, en caso de que no estén realizados sería por fecha de creación.

Para eso podemos utilizar una sentencia CASE en la cláusula ORDER BY:

SELECT 
  FechaCrea,   
  Concepto,
  Precio,
  ImportePagado,
  Realizado,
  FechaRealizado 
FROM 
  vistaPagos  
WHERE 
  Precio > 0 
ORDER BY 
 CASE Realizado WHEN True THEN FechaRealizado  
 ELSE FechaCrea END;  

Veamos ahora otra posibilidad de uso del CASE: cuando queremos que la consulta reciba un parámetro con el campo por el que ordenar los resultados:

SELECT 
  FechaCrea,   
  Concepto,
  Precio,
  ImportePagado,
  Realizado,
  FechaRealizado 
FROM 
  vistaPagos  
WHERE 
  Precio > 0 
ORDER BY 
CASE @OrdenaPor 
   WHEN 'Fecha' THEN FechaCrea
   WHEN 'FReal' THEN FechaRealizado       
 END,
CASE @OrdenaPor 
   WHEN 'Precio' THEN Precio
   WHEN 'Pendiente' THEN ImportePagado   
 END
;  

¿Por qué hay dos CASE separados en el segundo ejemplo? Bueno, CASE necesita que los tipos devueltos en la expresión sean compatibles. En el primer caso devolverá fechas, en el segundo devolverá importes. En caso de que no se cumpla ninguna de las condiciones devolverá un null, así que no debería hacer fallar la consulta.

Algunos consejos para optimizar consultas en SQL-Server

Vamos con una serie de pequeños trucos para optimizar nuestras consultas en SQL-Server y conseguir un mejor rendimiento, como ya habíamos hecho en el pasado con MySQL.

  • Añadir al nombre de la tabla el propietario y el esquema hará más rápida nuestra consulta. Si no lo ponemos SQL-Server buscará en todos los esquemas hasta encontrar el objeto.
  • Un clásico: No usar el comodín * en las consultas, poner el nombre de las columnas que queremos traer ayudará a ahorrar tiempo y memoria.
  • Como ya dijimos en un artículo anterior, NOT EXISTS tiene un mejor rendimiento que NOT IN.
  • No uses el prefijo sp_ para nombrar tus procedimientos almacenados. Aunque suene raro, al ser el prefijo que SQL-Server usa para los procedimientos almacenados por defecto siempre que se invoque uno que empieza por sp_ primero lo buscará en la base de datos maestra.
  • Comprueba que tus índices sean eficientes y que no estén demasiado saturados. Recuerda usar las claves primarias y externas de forma adecuada y tener la bases de datos bien normalizadas
  • Recuerda que las vistas con índices, como ya comentamos en otro artículo, mejoran la velocidad de consulta pero penalizan las operaciones de borrado, lectura y escritura.
  • No uses variables de tipo table en los joins de las consultas. Una tabla temporal o una expresión de tabla común (CTE) te darán un mejor rendimiento.
  • No utilices las cláusulas Distinct, Group By y Order By si no es indispensable. Consumen mucha memoria.
  • SQL-Server siempre devuelve la cuenta del número de filas afectado por las consultas de INSERT, DELETE, UPDATE y SELECT. Utilizar la cláusula SET NOCOUNT ON evitará esto ahorrando memoria y tiempo. En una consulta simple apenas se nota, pero en consultas con muchos joins o subconsultas ahorra mucho tiempo.
  • Las funciones son uno de los puntos débiles de SQL-Server, ralentizan enormemente las consultas. La ventaja que aportan es la reutilización de código, pero tienes que ver si te compensa su uso por lo que penalizan el rendimiento. En muchos casos una subconsulta o una tabla temporal serán más rápidas.

Borrar un trigger solo si este existe, en SQL-Server

El otro día vimos cómo hacerlo con una tabla, hoy vamos a ver cómo borrar un trigger de una base de datos, pero solo en caso de que exista (para evitar mensajes de error en nuestros scripts).

De nuevo, como en el ejemplo anterior, antes de SQL-Server 2016 se hacía de una forma y a partir de esa versión tenemos un sintaxis simplificada:

Versiones anteriores a SQL-Server 2016:

Tendríamos que hacer una consulta para ver si existe el trigger y luego lo eliminaríamos:

IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trelTriggerQueBorraremos')  DROP TRIGGER trelTriggerQueBorraremos

De SQL-Server 2016 en adelante:

DROP TRIGGER IF EXISTS trelTriggerQueBorraremos

Borrar una tabla solo si esta existe, en SQL-Server

El caso que vamos a tratar es el siguiente. Estamos creando un script para SQL-Server y queremos que si existe una tabla esta se borre. ¿Cómo lo hacemos? Dependiendo de la versión de nuestro SGBD lo haremos de una forma u otra:

SQL-Server anterior a la versión 2016:

Si trabajamos con una versión de SQL-Server anterior a 2016 (yo normalmente trabajo con SQL-Server 2008 R2 en la mayoría de los clientes de mi empresa) la sintaxis más correcta sería la siguiente:

IF OBJECT_ID('dbo.TablaQueQueremosBorrar', 'U') IS NOT NULL 
  DROP TABLE dbo.TablaQueQueremosBorrar; 

Como puedes ver, comprobamos si existe el nombre de la tabla a borrar y si la consulta devuelve algo distinto de NULL ejecutamos el borrado.

De SQL-Server 2016 en adelante:

Para SQL-Server 2016 se simplificó este paso con una nueva instrucción, que podríamos resumir como DIE (Drop If Exists)

DROP TABLE IF EXISTS dbo.TablaQueQueremosBorrar