Join

The use of several tables in a query, and their linking using some criteria, is usually known as join. For example:

select *

from asignaturas, profesores, imparte

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

Types of joins

Depending on the tables listed in the from and the type of the condition required to relate rows of these tables, the join is called differently (standard terminology):

Equijoin

Selects that implies the use of equals to the linking of rows of various tables. The previous example is a equijoin.

Self join

This select links a table with itself:

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

from imparte i1, imparte i2

where i1.asignatura= i2.asignatura

Actually, this example is not good, obviously, a person teaches the same subjects that himself. More clear, the coherent query would be the next one.

select i1.dni, ' teaches the same subject than ', i2.dni

from imparte i1, imparte i2

where i1.asignatura= i2.asignatura

and i1.dni <> i2.dni

Because of the data stored at the momento in the database, the query doesn't obtain any row, no teacher is sharing any subject with another colleague.

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. Este sería el resultado utilizando "<>".

Cartesian product

The cartesian product uses two tables without any linking condition in the where:

select dni, codigo

from profesores, asignaturas

Inner join, simple join

Actually, it is a very usual query in SQL. However, it exists an alternative particular syntax using inner join:

select nombre, descripcion

from asignaturas

join imparte on (codigo=asignatura)

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

The result will be the linking of all those rows, and only those, that are fulfilling the condition that relates them. This is an alternative statement from the ones shown until this moment:

select nombre, descripcion

from asignaturas, profesores, imparte

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

Perhaps, maybe it has some utility for remembering linking every pair of tables and leave the where for other type of conditions::

select nombre, descripcion

from asignaturas join imparte on (codigo=asignatura)

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

where descripcion NOT LIKE 'PROGRAMACION%'

Outer join

The outer join differs from the inner join in that the rows of the table shown in the result have not necessarily its corresponding row or rows in another table.

For example, we will want to get all the teachers, and if they teach any subject, get the code of this subject:

select p.*, i.asignatura

from profesores p

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

Later, we will go more deeply into outer join.

Antijoin

These queries are the ones using NOT IN to get those rows of a table that are not related with the other.

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

A semijoin can be seen as a normal join but only the columns of the first table are of interest to us.

Show all the information of the teachers who teach a subject.

select p. * from profesores p, imparte i

where i.dni = p.dni;

That is, these teachers do not want data on what they teach, only if they teach some subject. The problem, if it is, is that it can display duplicate rows depending on the query we execute. Another way to do this is with the IN operator and here no duplicate rows will appear.

select * from profesores

where dni IN (select dni from imparte i);

There is still a third possibility which is the EXISTS operator, which returns rows that make a subquery true and removes duplicates.

select * from profesores p

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

The exists operator and these types of subqueries are shown in an additional lesson in more advanced sessions, and are not required in the subject's SQL exams..

Note that, practically, the only thing we have done is giving a name to the different types of queries using in this course. It is not important the name but understanding the necessities of each query and how to satisfy it.

Now we introduce the syntax of inner and outer join. The first has been explained yet, the second will be explained in next section.

For more information, look for Oracle® SQL Language Reference (release 21), being the source of this section.

Outer join

As said before, the outer join extends the result of a simple query of, for example, two tables, obtaining all the rows that fulfill a linking condition, and besides, all or some of the rows of a table that do not fulfill that condition.

Suppose two tables A and B:

• select * from A left [outer] join B on (condición) -- Obtains all the rows related from A and B, and all the ones not related in A.

• select * from A right [outer] join B on (condición) -- Obtains all the rows related from A and B, and all the ones not related in B.

• select * from A full [outer] join B on (condición) -- (Not supported by MariaDB/MySQL) Obtains all the rows related with A and B and all the ones not related in A and B.

In order to understand better the way to operate of the different possibilities of outer join, we will work with an additional table, COORDINADORES, in our database 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.

Shows all the coordinators, and if they do so, the subjects they coordinate.

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

It behaves exactly the same as left join, but in the reverse order of the order of the tables in from.

Show all subjects, and if they have them, their coordinators.

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

What is expected from a full join is that all data from one and the other table will appear, whether they are related or not. More or less what is shown below:

Show all coordinators and all subjects, and whether there is a relationship between them.

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

Full join is not supported by MariaDB and MySQL

Nevertheless, full join is not fully supported by MySQL (although it is by other engines like Oracle PL/SQL) and if we execute the previous sentence the result is identical to a simple join, MariaDB/MySQL would ignore our wishes and execute what it considers the closest result.

Page updated

Google Sites

Report abuse