Inicio‎ > ‎SQL‎ > ‎SQL (english)‎ > ‎

T03 Joins

 
 

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

dni imparte la misma asignatura que dni
21111222 imparte la misma asignatura que 21111222
21111222 imparte la misma asignatura que 21111222
21333444 imparte la misma asignatura que 21333444

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.

Cartesian product

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

select dni, codigo
from profesores, asignaturas

dni codigo
21111222 DGBD
21222333 DGBD
21333444 DGBD
21111222 FBD
21222333 FBD
21333444 FBD
21111222 FP
21222333 FP
21333444 FP
21111222 HI
21222333 HI
21333444 HI
21111222 PC
21222333 PC
21333444 PC

 

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)
nombredescripcion
EVA GOMEZDISEÑO Y GESTION DE BASES DE DATOS
EVA GOMEZFUNDAMENTOS DE LAS BASES DE DATOS
RAFAEL ROMEROPROGRAMACION CONCURRENTE


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%'
nombredescripcion
EVA GOMEZDISEÑO Y GESTION DE BASES DE DATOS
EVA GOMEZFUNDAMENTOS DE LAS BASES DE DATOS
RAFAEL ROMEROPROGRAMACION CONCURRENTE

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);

dni nombre categoria ingreso asignatura
21111222 EVA GOMEZ TEU 1993-10-01 DGBD
21111222 EVA GOMEZ TEU 1993-10-01 FBD
21222333 MANUEL PALOMAR TEU 1989-06-16  null
21333444 RAFAEL ROMERO ASO6 1992-06-16 PC

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:

select * from profesores
where dni not in (select dni from imparte);

dni nombre categoria ingreso
21222333 MANUEL PALOMAR TEU 1989-06-16

 

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;


dni nombre categoria ingreso
21111222 EVA GOMEZ TEU 1993-10-01
21333444 RAFAEL ROMERO ASO6 1992-06-16

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. It is new, in this course, 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® Database SQL Reference 10g Release 2 (10.2), 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 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



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

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

dni nombre dpto asig codigo descripcion creditos creditosp
55777666 AGAPITO CIFUENTES DLSI FP FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5
66555444 ROMUALDO GOMEZ DLSI HI HI HISTORIA DE LA INFORMATICA 4.5  null
99222111 CATURLO PEREZ DLSI  null  null  null  null  null


Shows all the coordinators with a subjects and all the subjects.

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

dni nombre dpto asig codigo descripcion creditos creditosp
null null null null DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0
 null  null  null  null FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5
55777666 AGAPITO CIFUENTES DLSI FP FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5
66555444 ROMUALDO GOMEZ DLSI HI HI HISTORIA DE LA INFORMATICA 4.5  null
 null  null  null  null PC PROGRAMACION CONCURRENTE 6.0 1.5


Shows all the coordinators and all the subjects and if they are a relationship between them.

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

When full join is used, expected result would be the following, showing all data from one table and the other:

dni nombre dpto asig codigo descripcion creditos creditosp
null null null null DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0
55777666 AGAPITO CIFUENTES DLSI FP FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5
66555444 ROMUALDO GOMEZ DLSI HI HI HISTORIA DE LA INFORMATICA 4.5  
 null  null  null  null FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5
 null  null  null  null PC PROGRAMACION CONCURRENTE 6.0 1.5
99222111 CATURLO PEREZ DLSI  null  null  null  null  null


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.

dni nombre dpto asig codigo descripcion creditos creditosp
55777666 AGAPITO CIFUENTES DLSI FP FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5
66555444 ROMUALDO GOMEZ DLSI HI HI HISTORIA DE LA INFORMATICA 4.5  null



 
 
 
Comments