Conjuntos

Operaciones de conjuntos y MariaDB/MySQL

Un operador sobre conjuntos combina el resultado de dos sentencias select en un único resultado. Dependiendo del tipo de operación, esas sentencias deberán cumplir unos requisitos en cuanto al resultado que dan. Los operadores de conjuntos definidos para el álgebra relacional, base sobre la que se cimenta SQL, son

unión
intersección
diferencia
producto cartesiano
concatenación natural

No siempre estarán todos implementados. En MariaDB/MySQL, en las versiones actuales sí podemos encontrar

UNION
INTERSECT
EXCEPT
CROSS JOIN
NATURAL JOIN

Producto cartesiano

En SQL, el producto cartesiano entre dos tablas se obtiene si no establecemos ninguna condición a evaluar en where. De hecho, proponemos pensar en cualquier consulta como una secuencia ordenada de operaciones, de las cuales from es la primera, y cuyo resultado es el producto cartesiano de todas las tablas especificadas. Los operadores en álgebra relacional serían el producto cartesiano, la selección y la proyección:

select nombre, codigo from PROFESORES, IMPARTE where PROFESORES.dni = IMPARTE.dni

PROFESORES x IMPARTE -- from

donde PROFESORES.dni = IMPARTE.dni -- where

[nombre, codigo] -- select

Todas las combinaciones posibles de dni de profesor y código de asignatura

En álgebra relacional se solucionaría con

PROFESORES x ASIGNATURA [dni, codigo]

O con

PROFESORES[dni] x (ASIGNATURA [codigo])

En SQL

select dni, codigo

from profesores, asignaturas

No obstante, el producto cartesiano, como cualquier otro tipo de consulta, puede filtrar las filas resultado a nuestra conveniencia.

Dni de los profesores que imparten 2 o más asignaturas

select distinct i1.dni

from imparte i1, imparte i2

where i1.dni = i2.dni

and i1.asignatura != i2.asignatura;

Del producto cartesiano de una tabla por sí misma solo nos interesan las filas en las que el profesor "de la izquierda" es el mismo que el "de la derecha"; si, además, las asignaturas son diferentes, la conclusión no puede ser otra que ese profesor cumple con la condición.

Aunque para el ejemplo que hemos propuesto hay una forma más amigable de solucionar este enunciado, la que mostramos a continuación, el producto cartesiano es una opción que podemos utilizar cuando creamos conveniente

select dni from imparte group by dni having count(*) >= 2;

El estándar SQL define CROSS JOIN como el operador específico para el producto cartesiano. Sin embargo, dependiendo del motor de base de datos, su implementación difiere, haciéndolo más o menos eficiente.

En bases de datos pequeñas esa diferencia de rendimiento es irrelevante y, en cualquier caso, en MariaDB/MySQL podemos pensar que son equivalentes:

select * from profesores CROSS JOIN asignaturas;

select * from profesores JOIN asignaturas;

select * from profesores, asignaturas;

Intersección

El operador INTERSECT obtiene las filas comunes del resultado de dos consultas. Podría confundirse con un simple JOIN, y de hecho muchos resultados se pueden obtener con uno y otro operador, pero JOIN combina columnas de varias tablas y, si hay varias coincidencias, duplica las filas de la tabla izquierda, mientras que INTERSECT compara filas completas y devuelve una sola copia de las coincidencias. INTERSECT ALL se utiliza cuando no queremos un comportamiento estrictamente relacional, cuando no queremos eliminar duplicados si los hubiera.

DNI de los profesores que imparten y preparan: IMPARTE[dni] ∩ (PREPARA[dni])

Lo que debería solucionarse como

select dni from imparte

INTERSECT

select dni from prepara

Nótese que es necesario que las consultas obtengan tablas compatibles, con la misma cantidad de columnas y tipos de datos comparables en cada posición de columna.

Coordinadores de asignatura:

select * from coordinadores

Supervisores de asignatura:

select * from supervisores

Coordinadores de asignatura que también son supervisores:

select * from coordinadores

INTERSECT

select * from supervisores

/* Error de SQL (1222): The used SELECT statements have a different number of columns */

select dni,nombre from coordinadores

INTERSECT

select * from supervisores

Unión

Al utilizar el operador UNION entre dos sentencias select, el resultado final estará compuesto por todas aquellas filas que aparecen en el resultado de como mínimo una de las select. El operador UNION elimina filas duplicadas en el resultado final. El operador UNION ALL opera de igual modo, pero no elimina filas duplicadas en el resultado final.

Supongamos que queremos saber el nombre de los profesores que son "ASO6" o imparten asignaturas de 6 créditos. Veamos primero el resultado de cada consulta por separado.

1) Nombre de los profesores cuya categoría es ASO6.

select nombre from profesores where categoria='ASO6';

2) Nombre de los profesores que imparten asignaturas de 6 créditos.

select nombre

from profesores p, imparte i, asignaturas

where p.dni=i.dni and asignatura=codigo and creditos=6;

Nombre de los profesores que son ASO6 o imparten asignaturas de 6 créditos.

select nombre from profesores where categoria='ASO6'

UNION

select nombre

from profesores p, imparte i, asignaturas

where p.dni=i.dni and asignatura=codigo and creditos=6;

La misma consulta pero solucionada con UNION ALL

select nombre from profesores where categoria='ASO6'

UNION ALL

select nombre

from profesores p, imparte i, asignaturas

where p.dni=i.dni and asignatura=codigo and creditos=6

Diferencia

El operador EXCEPT obtiene aquellas filas que aparecen en el resultado de la primera select y no en el resultado de la segunda. Al igual que UNION, es un operador de conjuntos y no devuelve filas duplicadas.

Dni y nombre de los profesores que son TEU y no imparten asignaturas de 6 créditos.

PROFESORES donde categoría='TEU' [dni,nombre]

(PROFESORES x IMPARTE x ASIGNATURA

donde (PROFESORES.dni = IMPARTE.dni y codigo=asignatura y créditos=6)

[PROFESORES.dni,nombre])

Esta expresión escrita en álgebra relacional tiene su equivalente en SQL:

select dni,nombre from profesores where categoria='TEU'

EXCEPT

select p.dni,nombre

from profesores p

join imparte i on p.dni=i.dni

join asignaturas a on a.codigo=i.asignatura

and creditos=6

Recordemos que estamos ante una operación de conjuntos, debemos asegurar la compatibilidad entre una y otra subconsulta, deben devolver la misma cantidad de columnas, y comparables en cada posición:

select x1,y1,z1...

EXCEPT

select x2,y2,z2

EXCEPT no es lo mismo que NOT IN

Aunque muchas consultas tienen expresiones alternativas, el uso de EXCEPT y NOT IN no es el mismo. EXCEPT es la diferencia de conjuntos de filas, mientras que NOT IN es, en realidad, una concatenación de expresiones lógicas conectadas por OR. x NOT IN (a, b, c) == (x != a OR x != b OR x != c)

Sí será el mismo resultado para estos 2 ejemplos:

select dni, nombre from profesores

where categoria='TEU'

EXCEPT

select p.dni, p.nombre

from profesores p

join imparte i on p.dni=i.dni

join asignaturas a on a.codigo=i.asignatura

and creditos=6

select dni,nombre from profesores

where categoria='TEU'

and dni NOT IN (

select p.dni

from profesores p

join imparte i on p.dni=i.dni

join asignaturas a on a.codigo=i.asignatura

and creditos=6)

Pero porque en la segunda consulta estamos trabajando sobre una única columna que no admite nulos (dni). El operador IN sigue la lógica ternaria (true, false, unknown) que, en el caso de valores nulos, lleva a resultados no intuitivos:

d NOT IN (a, b, null) == (d!=a OR d!=b OR d!=NULL) == falso O falso O desconocido == desconocido

Aplicado a consultas SQL, esto significa no devolver ninguna fila, lo que puede resultarnos contraintuitivo. Sin embargo {d} EXCEPT {a, b, null} sí devuelve d como elemento que está en el primer conjunto pero no en el segundo.

IN / NOT IN Es seguro usarlo cuando:

Se trabaja con listas de valores constantes definidas a mano (ej. WHERE provincia IN ('03', '28', '46')).
La subconsulta devuelve una sola columna y se tiene la certeza absoluta de que no hay valores NULL en los datos de esa columna.

Concatenación natural

Este operador está implementado en MariaDB/MySQL y Oracle Database pero no es seguro que otros SGBD también lo tengan. Igual que en álgebra relacional, este operador asume que hay columnas comunes en dos tablas diferentes (en principio, que se llamen igual) y automatiza la reunión (join) en base a la igualdad de valores en estas columnas comunes.

Todos los datos de profesores que imparten alguna asignatura y códigos de esas asignaturas.

select distinct p.dni, p.nombre, p.categoria, p.ingreso, i.asignatura

from profesores p, imparte i

where p.dni=i.dni;

Se obtiene el mismo resultado que con

select * from profesores NATURAL JOIN imparte;

En ambos casos la salida es la que se muestra a continuación. Nótese que solo se muestra una columna de "dni" (concretamente la de PROFESORES, la tabla a la izquierda del operador).

Su expresión en álgebra relacional sería:

PROFESORES ⨝ IMPARTE

Aunque en álgebra el operador no devuelve filas duplicadas, en SQL —a diferencia de UNION, INTERSECTION o EXCEPT—, NATURAL JOIN no filtra esos posibles duplicados.

A pesar de la posible comodidad de uso de este operador hay que tener mucho cuidado, si no existen columnas comunes se devolverá un producto cartesiano. Otra fuente de resultados no esperados es que haya más columnas de las deseadas con idéntico nombre.

EXISTS

Aunque es un operador que se comporta de manera diferente a los anteriores, EXISTS tiene cierta relación con los operadores SQL de conjuntos. Además, es la base a una de las simulaciones clásicas del operador DIVISIÓN del álgebra relacional.

EXISTS es un operador de un único argumento y cuyo resultado es un valor de verdad:.

[NOT] EXISTS (orden select)

El operador EXISTS nos informa de si una subconsulta obtiene alguna fila o no, devuelve verdadero si hay al menos una tupla en la relación derivada, y falso si la relación derivada no tiene filas. Por eso, al contrario que IN, a EXISTS no le "preocupan" los nulos, solo la presencia de filas, contengan los datos que contengan.

¿Tenemos profesores?

select exists (select 1 from profesores) respuesta

Puesto que no necesitamos nada de la tabla profesores salvo saber si hay o no filas, por eso nos basta con "select 1". Pero no lo confundamos con el "1" de la tabla resultado, ese es un valor de verdad: 1 es verdadero, 0 es falso.

¿Está vacía la tabla profesores?

select not exists (select 1 from profesores) respuesta

Es más habitual componer una condición que nos permita filtrar filas.

Todos los datos de las asignaturas que son impartidas por algún profesor

select * from asignaturas a

where exists (select 1 from imparte i where i.asignatura=a.codigo)

Hacemos uso de los que se conoce como subconsulta correlacionada: i.asignatura=a.codigo vincula ambas tablas. Viene a ser algo así como "busca el código de asignatura en la tabla IMPARTE". Esto lo hará para cada fila de asignatura.

Nótese que no se hace un join, solamente se buscan valores hasta que se encuentran, lo que puede hacerse sobre un índice si existiera.

También podemos consultar todo lo contrario:

Todos los datos de las asignaturas que no son impartidas por ningún profesor

select * from asignaturas a

where not exists (select 1 from imparte i where i.asignatura=a.codigo)

En el esquema de base de datos EJEMPLO tenemos una tabla PREPARA con la siguiente estructura

CREATE TABLE prepara (

dni VARCHAR(10),

asignatura CHAR(5),

PRIMARY KEY (dni, asignatura),

FOREIGN KEY (asignatura) REFERENCES asignaturas (codigo),

FOREIGN KEY (dni) REFERENCES profesores (dni)

);

Esta tabla representa a profesores que han sido asignados para preparar los contenidos y materiales y evaluación de ciertas asignaturas. Es una relación muchos a muchos entre PROFESORES y ASIGNATURAS.

Nombre de los profesores que preparan alguna asignatura

select nombre from profesores p

where exists

(select 1 from prepara pp where p.dni=pp.dni)

Podríamos leer esta consulta algo así como "dame el nombre de los profesores para los que existe al menos una fila en PREPARA".

Pero si negamos solo uno de los exists

Nombre de los profesores que no preparan todas las asignaturas (que existe al menos una asignatura que no prepara)

select nombre from profesores p

where exists -- existe al menos una asignatura

(select codigo from asignaturas a

where not exists -- que el profesor no prepara

(select 1 from prepara pp

where pp.asignatura=a.codigo and p.dni=pp.dni)

)

Cuando esto se pone difícil es al emular el operador división del álgebra relacional.

Nombre de los profesores que preparan todas las asignaturas

select nombre from profesores p -- profesores tales

where not exists -- que no existe asignatura

(select 1 from asignaturas a

where not exists -- que no prepare él

(select 1 from prepara pp

where pp.asignatura=a.codigo and p.dni=pp.dni)

)

Digamos que viene a ser "dame el nombre de los profesores tales que no existe asignatura que no preparen ellos". Definitivamente, resulta complicado lidiar con dobles negaciones, pero no imposible.

Alternativas

A veces, el resultado buscado permite otro tipo de soluciones.

Nombre de los profesores que preparan todas las asignaturas.

select nombre from profesores p join prepara pp on p.dni = pp.dni

group by p.dni, nombre

having count(*) = (select count(*) from asignaturas);

O con ciertos operadores de conjuntos:

select dni,nombre from coordinadores

INTERSECT

select * from supervisores

select dni,nombre from coordinadores c where exists (select 1 from supervisores s where s.dni=c.dni)

select dni,nombre from coordinadores

EXCEPT

select * from supervisores

select dni,nombre from coordinadores c where NOT EXISTS (select 1 from supervisores s where s.dni=c.dni)

El consenso es que EXISTS es más eficiente de cara al motor de base de datos —cuando "encuentra" la primera fila, ya no sigue "buscando"—. Sin embargo, para que haya diferencias de rendimiento significativas tenemos que hablar de tablas realmente grandes, y las consultas escritas de esta manera pueden resultar más difíciles de "leer".

Google Sites

Report abuse