▷ Tutorial SQL #5: Operaciones con conjuntos

19/11/2021Artículo original

Hasta ahora hemos estudiado las consultas simples, las consultas multi-tabla y los diferentes tipos de JOIN en las consultas multi-tabla. Ahora vamos a aprender a operar con conjuntos de resultados.

[youtube:mCQq2_DUduQ]

Combinando consultas multi-tabla

Con las variantes INNER, LEFT, RIGHT y FULL de consultas multi-tabla somos capaces de obtener registros relacionados y los registros relacionados + los registros no relacionados en uno de los dos lados o en ambos, básicamente estas combinaciones de los datos de dos tablas:

Pero ¿qué pasa si queremos jugar con la parte común para conseguir combinaciones como éstas pero excluyendo los datos comunes?

Nota: Si esta serie de artículos sobre SQL te está pareciendo interesante, ni te imaginas lo que puedes aprender con este curso de fundamentos de SQL.

Por ejemplo, en el caso de la base de datos Nortwind, si queremos obtener los clientes que NO tienen pedidos. Sería equivalente a esto en nuestros diagramas de Venn:

Es decir, sería equivalente a una hipotética cláusula LEFT ONLY (que no existe en SQL) en la que estamos excluyendo el resultado del INNER JOIN.

Dado que lo que queremos es encontrar a los que no tienen relación, es decir, aquellos cuyo campo de unión en el JOIN no existe en la tabla de la derecha, podemos usar una sintaxis como esta:

SELECT T1.Col1, T1.Col2, T1.Col3, T2.Col7FROM Tabla1 T1 LEFT [OUTER] JOIN Tabla2 T2 ON T1.Col1 = T2.Col1WHERE T2.Col1 IS NULL

Es decir, basta con indicar que el campo en la tabla de la derecha es nulo, o sea, falla la relación por ese lado.

En nuestra base de datos de ejemplo si lanzamos esta consulta:

SELECT OrderID, C.CustomerID, CompanyName, OrderDateFROM Customers C FULL JOIN Orders O ON C.CustomerID = O.CustomerID WHERE O.CustomerID IS NULL

Obtendremos todos los clientes que no tienen pedidos, que como sabemos de otras ocasiones son solamente dos:

Fíjate en como se obtienen los resultados con los campos correspondientes al pedido nulos.

Si solo nos interesara conocer qué clientes son estos sería fácil hacerlo con una consulta y su correspondiente sub-consulta, sin necesidad de usar un JOIN, de la siguiente manera:

SELECT CustomerID, CompanyName FROM CustomersWHERE CustomerID NOT IN (SELECT DISTINCT CustomerID FROM Orders)

que nos devuelve la información que queremos pero la relación que buscamos es menos obvia y no involucra campos de la tabla de la derecha (a excepción de la clave externa, claro).

OpenAI afirma haber mitigado el sesgo del lenguaje GPT-3 con una orientación hacia "los valores"

Exactamente del mismo modo pero cambiando la consulta por su “espejo” podríamos simular una hipotética función RIGHT ONLY de la siguiente manera:

SELECT T1.Col1, T1.Col2, T1.Col3, T2.Col7FROM Tabla1 T1 LEFT [OUTER] JOIN Tabla2 T2 ON T1.Col1 = T2.Col1WHERE T1.Col1 IS NULL

que es equivalente al siguiente diagrama:

Finalmente, y rizando el rizo, podríamos obtener únicamente todos los registros desparejados de la tabla de la izquierda y todos los desparejados de la tabla de la derecha para una hipotética operación EXCEPT INNER que no existe en SQL:

simplemente combinando ambas condiciones vistas antes:

SELECT T1.Col1, T1.Col2, T1.Col3, T2.Col7FROM Tabla1 T1 LEFT [OUTER] JOIN Tabla2 T2 ON T1.Col1 = T2.Col1WHERE T1.Col1 IS NULL OR T2.Col1 IS NULL

Con esto tenemos contempladas todas las operaciones entre dos conjuntos de tablas relacionadas.

Operaciones de conjuntos entre tablas independientes

Además de lo visto hasta ahora es posible combinar los resultados de dos consultas independientes y fusionarlos en uno solo o realizar otras operaciones de conjuntos.

Por ejemplo podemos tomar el nombre y apellidos de todos los clientes de una tabla de clientes, y combinarlos con el nombre y los apellidos de todos los proveedores de una tabla de proveedores. No existe relación alguna entre ellos, pero son datos compatibles y podemos querer combinarlos.

Del mismo modo, y asumiendo que puede haber solapamiento entre ambas tablas, podríamos querer averiguar qué clientes tenemos que además son proveedores, o al contrario, qué proveedores no son clientes.

Veamos como…

La cláusula UNION de SQL permite unir las filas devueltas por dos instrucciones SELECT. Para ello se debe cumplir que las columnas devueltas en ambas instrucciones coincidan en número y en tipo de datos de cada una de ellas, ya que en caso contrario dará un error al ejecutarse.

Programación para Juegos. Artículo 3. ¿Qué es un pixel?

Su sintaxis es:

SELECT Columnas FROM …UNION [ALL]SELECT Columnas FROM …

Si utilizamos la opción ALL, aparecerán todas las filas devueltas por ambas instrucciones SELECT, pero si no la ponemos se eliminarán las filas repetidas.

Veamos un ejemplo con la base de datos Northwind:

SELECT ShipCountry FROM OrdersUNIONSELECT Country FROM Customers

Esta consulta nos devolverá la lista de todos los países de destino de los pedidos, unidos a los países de ubicación de los clientes (que no tienen por qué coincidir). En este caso, si lanzamos la consulta, obtendremos 21 registros.

Sin embargo añadiéndole la opción ALL:

SELECT ShipCountry FROM OrdersUNION ALLSELECT Country FROM Customers

nos devolverá todos los registros existentes, aunque estén repetidos, y obtendremos 921 filas como resultado (¡frente a 21 de antes!).

Si tuviésemos en vez de una sola tabla de ventas (Orders), una tabla de ventas por cada año (por ejemplo Orders2001, Orders2002, Orders2003, Orders2004, Orders2005 y Orders2006). Si necesitamos un listado con el Nº de pedido, el nombre del empleado que la realizó, y la fecha, de todas las ventas del cliente cuyo código es ‘ALFKI’ a lo largo de todos esos años, podríamos combinar los resultados con UNION para obtener el listado consolidado:

SELECT O.OrderID, E.FirstName, O.OrderDateFROM Orders2001 O INNER JOIN Employees E ON O.EmployeeID = E.EmployeeIDUNION ALLSELECT O.OrderID, E.FirstName, O.OrderDateFROM Orders2002 O INNER JOIN Employees E ON O.EmployeeID = E.EmployeeIDUNION ALLSELECT O.OrderID, E.FirstName, O.OrderDateFROM Orders2003 O INNER JOIN Employees E ON O.EmployeeID = E.EmployeeIDUNION ALLSELECT O.OrderID, E.FirstName, O.OrderDateFROM Orders2004 O INNER JOIN Employees E ON O.EmployeeID = E.EmployeeIDUNION ALLSELECT O.OrderID, E.FirstName, O.OrderDateFROM Orders2005 O INNER JOIN Employees E ON O.EmployeeID = E.EmployeeIDUNION ALLSELECT O.OrderID, E.FirstName, O.OrderDateFROM Orders2006 O INNER JOIN Employees E ON O.EmployeeID = E.EmployeeID

Además de esta instrucción, SQL incluye un par de instrucciones adicionales de gran utilidad para trabajar con conjuntos de tablas no relacionadas: EXCEPT e INTERSECT. Como cabría esperar por sus nombres, permiten respectivamente obtener diferencias de conjuntos e intersecar conjuntos.

FIX: Planes de mantenimiento de SQL Server

Al igual que UNION estas dos operaciones se usan colocándolas entre dos consultas que deben ser compatibles.

INTERSECT devuelve los valores distintos devueltos por las consultas y comunes a ambas, con lo que obtenemos una intersección (sólo los registros que están entre los resultados de ambas consultas).
EXCEPT (o MINUS) devuelve los valores de la primera consulta que no se encuentran en la segunda. Así podemos averiguar qué registros están en una consulta pero no en la otra, calculando la diferencia entre dos conjuntos de registros. Algo realmente útil en ocasiones y difícil de conseguir con instrucciones más simples.

Con esto hemos dado un repaso bastante amplio a las posibilidades de trabajo con consultas de resultados en SQL estándar, que podremos aplicar a prácticamente cualquier gestor de bases de datos relacionales.

En la siguiente entrega de esta serie iremos ampliando el conocimiento del lenguaje de consultas.