Reunión (join)

El uso de varias tablas en una consulta, y su concatenación siguiendo cualquier criterio, se conoce habitualmente como join, el término en inglés adoptado y utilizado. Estamos hablando, por ejemplo, de:

select *

from asignaturas, profesores, imparte

where profesores.dni = imparte.dni and asignatura = codigo

Tipos de reunión

Dependiendo de qué tablas se especifiquen en el from y del tipo de condición exigida para relacionar las filas de esas tablas, el join recibe distintos nombres (terminología que se puede considerar estándar):

Equijoin

Consultas que conllevan el uso de igualdades para la concatenación de filas de varias tablas. El ejemplo anterior es una equijoin.

Self join

Estas consultas concatenan una tabla consigo misma:

select i1.dni, ' imparte la misma asignatura que ', i2.dni

from imparte i1, imparte i2

where i1.asignatura= i2.asignatura

En realidad, el ejemplo no es muy bueno porque, obviamente, un profesor da lo mismo que él mismo. Más claro, la orden coherente sería la siguiente.

select i1.dni, ' imparte la misma asignatura que ', i2.dni

from imparte i1, imparte i2

where i1.asignatura= i2.asignatura

and i1.dni <> i2.dni

Pero, dado el contenido de la base de datos, la consulta no obtiene ninguna fila porque ningún profesor está compartiendo la asignatura con un compañero.

Más claro aún queda si lo hacemos con asignaturas y créditos:

select a1.codigo asig1,'tiene los mismos créditos que',a2.codigo asig2

from asignaturas a1, asignaturas a2

where a1.creditos = a2.creditos

and a1.codigo > a2.codigo

NOTA: en vez de utilizar el operador distinto (<>), al hacer la comparación con mayor estricto (a1.codigo > a2.codigo) evitamos la filas "inversas". La información es la misma, pero más concisa.

Producto cartesiano

El producto cartesiano utiliza dos tablas sin la condición de concatenación del where:

select dni, codigo from profesores, asignaturas

Inner join, simple join

Realmente, se trata de la consulta habitual en SQL. Sin embargo, existe una sintaxis particular alternativa usando inner join:

select nombre, descripcion

from asignaturas

join imparte on (codigo=asignatura)

join profesores on (imparte.dni=profesores.dni)

El resultado será la concatenación de todas aquellas filas, y únicamente esas, que cumplen la condición que las relaciona. Es una construcción alternativa a la que hemos venido utilizando hasta ahora que nos debe ser familiar:

select nombre, descripcion

from asignaturas, profesores, imparte

where profesores.dni = imparte.dni and asignatura = codigo

Si acaso, puede tener cierta utilidad para no olvidarnos de enlazar cada par de tablas y dejar el where para otro tipo de condiciones:

select nombre, descripcion

from asignaturas join imparte on (codigo=asignatura)

join profesores on (imparte.dni=profesores.dni)

where descripcion NOT LIKE 'PROGRAMACION%'

Outer join

El outer join se diferencia del inner join en que las filas de una tabla que se muestran en el resultado no necesariamente tienen su correspondiente fila o filas en la otra tabla.

Por ejemplo, podríamos querer obtener todos los profesores y, si da alguna asignatura, el código de esas asignatura:

select p.*, i.asignatura

from profesores p

left join imparte i on (p.dni=i.dni);

Más adelante se profundiza en el outer join.

Antijoin

Estas consultas son las que utilizan el NOT IN para obtener aquellas filas de una tabla que no se relacionan con las de otra:

Profesores que no imparten ninguna asignatura —que no aparecen en la tabla IMPARTE—:

select * from profesores

where dni not in (select dni from imparte);

Semijoin

Un semijoin se puede ver como un join normal pero del que solo nos interesan las columnas de la primera tabla.

Muestra los toda la información de los profesores que imparten alguna asignatura.

select p.* from profesores p,imparte i

where i.dni = p.dni;

Es decir, de estos profesores no queremos datos sobre qué imparten, solo si imparten alguna asignatura. El problema, si es que lo es, radica en que puede mostrar filas duplicadas dependiendo de la consulta que ejecutemos. Otra forma de hacerlo es con el operador IN y aquí no aparecerán filas duplicadas.

select * from profesores

where dni in (select dni from imparte i);

Aún hay una tercera posibilidad que es el operador EXISTS, que devuelve filas que hacen cierta una subconsulta y elimina duplicados.

select * from profesores p

where exists (select * from imparte i where i.dni = p.dni);

El operador exists y este tipo de subconsultas se muestran en una lección adicional en sesiones más avanzadas, y no se exige en los exámenes de SQL de la asignatura.

Démonos cuenta que, prácticamente, lo único que hemos hecho es dar nombre a los distintos tipos de consultas, algunas ya utilizadas durante el curso. No es importante ese nombre sino entender las necesidades de cada consulta y cómo satisfacerla.

Sí es nueva, para este curso, la sintaxis del inner y el outer join. El primero no hace falta desarrollarlo más, el segundo sí lo tratamos en la siguiente sección.

Téngase en cuenta, también, que éste no es un curso exhaustivo de SQL. Hay detalles de rendimiento que favorecen el uso de unos u otros tipos de consultas y, por supuesto, muchas más opciones a la hora de incrementar ese rendimiento sobre todo en entornos de medianas o grandes bases de datos. Para más información, podéis consultar Oracle® SQL Language Reference (release 21), que ha sido la fuente de esta sesión.

Outer join

Como ya hemos dicho, el outer join extiende el resultado de una consulta simple de, por ejemplo, dos tablas, obteniendo todas las filas que cumplen la condición de concatenación y, además, todas o algunas de las filas de una tabla que no cumplen tal condición.

Supongamos dos tablas A y B:

select * from A left [outer] join B on condición (obtiene todas las filas relacionadas de A y B, y todas las no relacionadas de A. )
select * from A right [outer] join B on condición (obtiene todas las filas relacionadas de A y B, y todas las no relacionadas de B. )
select * from A full [outer] join B on condición (No soportado por MySQL) Obtiene todas las filas relacionadas de A y B, y todas las no relacionadas de A y B. )

Para ver mejor el funcionamiento de las distintas alternativas de join, vamos a trabajar con una tabla adicional, COORDINADORES, en nuestra base de datos Ejemplo.

BD Ejemplo

PROFESORES ( dni varchar2(10), nombre varchar(40), categoria char(4), ingreso date )

CP (dni)

ASIGNATURAS ( codigo char(5), descripcion varchar(35), creditos decimal(3,1), creditosp decimal(3,1) )

CP (código)

IMPARTE ( dni varchar(10), asignatura char(5) )

CP (dni, asignatura)

CAj (dni) → PROFESORES

CAj (asignatura) → ASIGNATURAS

COORDINADORES ( dni varchar(10), nombre varchar(40), dpto char(4), asig char(5) )

CP (dni)

CAj (asig) → ASIGNATURAS

LEFT JOIN

Informalmente podemos decir que, dado TablaA LEFT JOIN TablaB, el resultado sería "todas las filas de la izquierda y, si están relacionadas, los datos asociados de la derecha". La tabla resultado contendría, al menos, todas las filas de la TablaA, y si alguna de ellas está relacionada con otras de la TablaB, también estos datos.

Muestra todos los coordinadores y, si lo hacen, las asignaturas que coordinan.

select * from coordinadores left join asignaturas on (codigo=asig);

Agapito, Romualdo y Caturlo son coordinadores. Como tales, deben aparecer en el resultado. Pero, además, Romualdo y Caturlo están asociados a sendas asignaturas. Caturlo, que no lo está, simplemente muestra nulos en las columnas correspondientes a datos de la asignatura.

Si se diera el caso de una fila de TablaA asociada a varias de TablaB, esa fila aparecería varias veces —con los correspondientes datos de TablaB—. Por ejemplo:

Muestra todos los profesores y, si imparten, qué códigos de asignatura imparten.

SELECT *

FROM profesores

LEFT JOIN imparte ON profesores.dni=imparte.dni;

Eva y Rafael imparten asignaturas; Manuel no. Pero Eva imparte FBD y DGBD, por eso aparece dos veces en el resultado.

Cuando trabajamos con más de dos tablas hay que tener cuidado con left join. Es casi seguro que tendremos que usarlo en todos los join consecutivos.

Muestra todos los profesores y, si imparten, toda la información de las asignaturas que imparten.

La solución siguiente no es lo que buscamos, no obtiene lo que nos piden:

SELECT *

FROM profesores

LEFT JOIN imparte ON profesores.dni=imparte.dni

JOIN asignaturas ON imparte.asignatura=asignaturas.codigo;

Podemos pensar que, efectivamente, entre profesores e imparte funciona como pretendemos, pero después queremos un join simple con asignaturas. Recuerda que INNER JOIN muestra solo información relacionada, las filas de una y otra tabla que no estén asociadas no se muestran. En el caso de Manuel Palomar, i.asignatura es nulo y, por tanto, no se relaciona con ninguna asignatura: en consecuencia, Manuel Palomar no sale como resultado.

Sin embargo, si aplicamos LEFT JOIN también a asignaturas:

SELECT *

FROM profesores

LEFT JOIN imparte ON profesores.dni=imparte.dni

LEFT JOIN asignaturas ON imparte.asignatura=asignaturas.codigo;

Ahora sí hemos obtenido lo que pretendíamos. Más o menos, "todas las filas de profesor, y las de imparte que estén relacionadas"—primer LEFT JOIN—; "de esas que hemos conseguido (profesor-imparte), muéstralas todas, y si hay asignatura con la que se relacione, muestra sus datos" —segundo LEFT JOIN—.

RIGHT JOIN

Se comporta exactamente igual que left join, pero en el sentido inverso al orden de las tablas en from.

Muestra todas las asignaturas, y si los tuvieran, sus coordinadores.

select * from coordinadores right join asignaturas on (codigo=asig);

Ahora nos piden que mostremos todas las asignaturas, pero a la derecha; y si tienen coordinador, entonces muestra los datos de este. DGBD, FBD y PC no tienen coordinador.

FULL JOIN

Lo que se espera de un full join es que aparezcan todos los datos de una y otra tabla, estén o no relacionados. Más o menos, lo que se muestra a continuación:

Muestra todos los coordinadores y todas las asignaturas, y si hay relación entre ellos.

select * from coordinadores full join asignaturas on (codigo=asig);

Full join no está soportado por MariaDB y MySQL

Sin embargo, full join no está soportado por MariaDB/MySQL —aunque sí por otros motores, Oracle PL/SQL, por ejemplo— y si ejecutando la sentencia anterior, el resultado es idéntico a un join simple. MariaDB/MySQL ignora nuestros deseos y ejecuta lo que considera más cercano.

No obstante, siempre podemos utilizar el operador union —que se verá en lecciones posteriores— para conseguir el resultado deseado:

select * from coordinadores left join asignaturas on (codigo=asig)

union

select * from coordinadores right join asignaturas on (codigo=asig)

Google Sites

Report abuse