Ejercicio típico: entidades que se relacionan con dos (o más) entidades de otro tipo

Un tipo de consulta habitual es la que pretende obtener datos de una entidad de un tipo dado (entidad A) que se relacionan con varias (dos o más) de otra entidad de otro tipo (entidad B). P.ej.: alumnos (A) que están matriculados en dos asignaturas (B) dadas, proyectos (A) en los que trabajan al mismo tiempo dos empleados (B) dados, etc.

Para resolver este tipo de consultas debemos partir de una tabla en la que estén relacionadas las entidades A y B. Si no dispusiéramos de ella, deberíamos construirla con algún join.

Veamos un ejemplo más concreto que nos ayude a razonar. Supongamos que tenemos la tabla Trabaja, con los identificadores de los empleados de una empresa y los identificadores de los proyectos en los que trabajan.

+-----------+----------+
| empleado  | proyecto |
+-----------+----------+
| 123456789 |        1 |
| 453453453 |        1 |
| 123456789 |        2 |
| 333445555 |        2 |
| 453453453 |        2 |
| 333445555 |        3 |
| 666884444 |        3 |
| 333445555 |       10 |
| 987987987 |       10 |
| 999887777 |       10 |
| 333445555 |       20 |
| 888665555 |       20 |
| 987654321 |       20 |
| 987654321 |       30 |
| 987987987 |       30 |
| 999887777 |       30 |
| 453453453 |      400 |
| 555555555 |      400 |
| 555555555 |      500 |
+-----------+----------+

Nos piden obtener

Identificador de los proyectos en los que trabajan al mismo tiempo los empleados de número 123456789 y 453453453.

En este caso la entidad A (por lo que nos preguntan) son los proyectos, y la entidad B (con la que A mantiene las relaciones) son los empleados. En la tabla Trabaja tenemos todas las relaciones entre ambas entidades.

La idea general para la solución es comprobar una condición en la que simultáneamente aparezcan los empleados 123456789 y 453453453 junto con cada proyecto buscado. Pero esa condición no la podemos imponer en la tabla Trabaja directamente, porque las tuplas que tenemos solo tienen el valor de un empleado. Tenemos que construir algo que tenga dos valores de empleados. Este algo puede ser

• tuplas, en la que cada proyecto aparezca con parejas de empleados que participan en él; estas tuplas las construiremos con un autojoin
• o un grupo de tuplas para cada proyecto, que agrupe a todos los empleados que trabajan en cada proyecto; para esto usaremos group by.

Solución con autojoin

Analicemos la solución paso a paso. 

1. Comenzamos con un autojoin que emparejará la tabla Trabaja consigo misma en base a la entidad que queremos obtener (entidad A), que en este caso son los proyectos. Así asociaremos cada tupla de cada proyecto con todas las tuplas de la misma tabla Trabaja del mismo proyecto:

   select *
   from Trabaja T1 join Trabaja T2 on T1.proyecto=T2.proyecto;
   

   Pruébalo en DB Fiddle

   +-------------+--------------+-------------+--------------+
   | T1.Proyecto | T1.empleado  | T2.Proyecto | T2.empleado  |
   +-------------+--------------+-------------+--------------+
   |           1 |    123456789 |           1 |    123456789 |
   |           1 |    123456789 |           1 |    453453453 |
   |           1 |    453453453 |           1 |    123456789 |
   |           1 |    453453453 |           1 |    453453453 |
   |           2 |    123456789 |           2 |    123456789 |
   |           2 |    123456789 |           2 |    333445555 |
   |           2 |    123456789 |           2 |    453453453 |
   |           2 |    333445555 |           2 |    123456789 |
   ...
   

2. Ahora ya tenemos tuplas en las que aparecen parejas de empleados. Lo que buscamos son tuplas en ese resultado en las que alguna entidad A aparezca con todos los identificadores de las entidades B que queremos comprobar. En este caso la entidad B son los empleados, y los que buscamos son los de número 123456789 y 453453453 (hemos marcado algunas de estas tuplas en negrita). El proyecto que aparezca en una tupla junto con esos dos empleados, cumplirá los criterios de búsqueda y será parte del resultado.

   select T1.proyecto
   from Trabaja T1 join Trabaja T2 on T1.proyecto=T2.proyecto
   where T1.empleado = '123456789' and T2.empleado = '453453453';
   

   Pruébalo en DB Fiddle

Solución con group by

Quizás, la solución con group by es más fácil de entender, aunque, en determinadas circunstancias, sea más costosa en tiempo de ejecución. Analicémosla paso a paso.

1. Partiremos de la tabla que relaciona las entidades A y B (Trabaja en nuestro caso). Hemos de quedarnos sólo con las tuplas que tengan las entidades B que queremos considerar (las demás nos van a estorbar). En nuestro caso eso implica quedarnos solo con las tuplas de los empleados 123456789 y 453453453.

   select *
   from trabaja
   where empleado in ('123456789', '453453453')
   

   El resultado de esta consulta es:

   +--------+-----------+
   | Numero | Empleado  |
   +--------+-----------+
   |      1 | 123456789 |
   |      2 | 123456789 |
   |      1 | 453453453 |
   |      2 | 453453453 |
   |    400 | 453453453 |
   +--------+-----------+
   

2. Luego agrupamos por Entidad A (en este caso por proyecto).

   select proyecto
   from trabaja
   where empleado in ('123456789', '453453453')
   group by proyecto
   

   Si estudiásemos los grupos que obtenemos veríamos que son tres, que podemos representar así (lo siguiente no es el resultado de la agrupación sino una forma de representar las tuplas que van a parar a cada grupo):

   +--------+-----------+
   | Numero | Empleado  |
   +--------+-----------+
   |      1 | 123456789 |
   |      1 | 453453453 |
   - - - - - - - - - - - 
   |      2 | 123456789 |
   |      2 | 453453453 |
   - - - - - - - - - - -
   |    400 | 453453453 |
   +--------+-----------+
   

   En el grupo del proyecto 1 están ambos empleados, así como en el grupo del proyecto 2. No ocurre lo mismo con el grupo del proyecto 400, que solo tiene uno de los dos empleados.

3. Ya tenemos lo que necesitamos: grupos en los que podemos comprobar si todos los valores de la entidad B considerados (empleados 123456789 y 453453453) aparecen con la misma entidad A (proyecto). Solo resta imponer la condición para quedarnos esos grupos. Esto se hace contando cuantos empleados distintos hay en cada grupo. Eso descartará el grupo del proyecto 400, en el que solo trabaja uno de los empleados. Para imponer esta condición a los grupos recurrimos, como siempre en estos casos, a una cláusula having:

   select proyecto
   from trabaja 
   where empleado in ('123456789', '453453453')
   group by proyecto
   having count(distinct empleado) = 2;
   

   Pruébalo en DB Fiddle

Qué solución es mejor

Resumiendo, hemos construido algo que tiene los valores que buscamos (proyectos) junto con los valores con los que tienen que estar relacionados (empleados). En el primer caso (autojoin) ese algo son tuplas, por lo que podemos poner una condición expresada en el where; en el segundo caso (group by) son grupos, en los que podemos poner una condición expresada en un having.

La ventaja que tiene la solución con group by es que escala muy bien si se tienen que considerar más casos. Por ejemplo, para obtener los proyectos en los que trabajan al mismo tiempo los empleados de números 123456789, 333445555 y 453453453, bastaría con cambiar mínimamente la consulta:

select proyecto
from Trabaja 
where empleado in ('123456789', '453453453', '333445555')
group by proyecto
having count(distinct empleado) = 3;

Pruébalo en DB Fiddle

Sin embargo, hacer lo mismo usando solo joins es más complicado:

select T1.proyecto
from trabaja T1
     join trabaja T2 on T1.proyecto =T2.proyecto
     join trabaja T3 on T2.proyecto=T3.proyecto
where T1.empleado = '123456789' 
      and T2.empleado = '453453453'
      and T3.empleado = '333445555';

Pruébalo en DB Fiddle

Otro aspecto a analizar es el coste de ejecución de ambas consultas. Éste es difícil de predecir, porque depende de varios factores: tamaño de las tablas, existencia de índices… Aun así podemos aventurar que, en muchas ocasiones, el coste de ejecutar el group by es superior al del autojoin, por lo que esta solución será más eficiente.