Inicio‎ > ‎SQL‎ > ‎Lecciones SQL‎ > ‎

P07 Reunión (join) y aritmética de columna

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

Inner join, simple join

Realmente, se trata de la misma consulta habitual pero con una sintaxis alternativa usando 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


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


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

dninombrecategoriaingresoasignatura
21111222EVA GOMEZTEU1993-10-01DGBD
21111222EVA GOMEZTEU1993-10-01FBD
21222333MANUEL PALOMARTEU1989-06-16 
21333444RAFAEL ROMEROASO61992-06-16PC



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


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

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


dninombredptoasigcodigodescripcioncreditoscreditosp
55777666AGAPITO CIFUENTESDLSIFPFPFUNDAMENTOS DE LA PROGRAMACION9.04.5
66555444ROMUALDO GOMEZDLSIHIHIHISTORIA DE LA INFORMATICA4.5 null
99222111CATURLO PEREZDLSI null null null null null


Muestra los coordinadores que tienen asignatura y todas las asignaturas. 

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

dninombredptoasigcodigodescripcioncreditoscreditosp
null
null
null
null
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
null 
null
null
nullFBDFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
55777666AGAPITO CIFUENTESDLSIFPFPFUNDAMENTOS DE LA PROGRAMACION9.04.5
66555444ROMUALDO GOMEZDLSIHIHIHISTORIA DE LA INFORMATICA4.5 null
 nullnull 
null 
null 
PCPROGRAMACION CONCURRENTE6.01.5


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

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

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:

dninombredptoasigcodigodescripcioncreditoscreditosp
null
null
null
nullDGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
55777666AGAPITO CIFUENTESDLSIFPFPFUNDAMENTOS DE LA PROGRAMACION9.04.5
66555444ROMUALDO GOMEZDLSIHIHIHISTORIA DE LA INFORMATICA4.5 null
nullnull
null
null
FBDFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
nullnull
null
null 
PCPROGRAMACION CONCURRENTE6.01.5
99222111CATURLO PEREZDLSInullnull
 null null null


Sin embargo, full join no está soportado por MySQL aunque sí por otros motores (Oracle PL/SQL) y si ejecutáramos la sentencia anterior el resultado es idéntico a un join simple. 

dninombredptoasigcodigodescripcioncreditoscreditosp
55777666AGAPITO CIFUENTESDLSIFPFPFUNDAMENTOS DE LA PROGRAMACION9.04.5
66555444ROMUALDO GOMEZDLSIHIHIHISTORIA DE LA INFORMATICA4.5 null


Aritmética de columna

Se pueden utilizar expresiones aritméticas tanto en la cláusula select, para obtener una nueva columna en la tabla resultado, como en la construcción de condiciones de selección de filas.

Suponiendo que el curso se divide en 2 semestres y que 3 créditos se corresponden con 1 hora de clase semanal, nombre de las asignaturas y número de horas de clase semanales de cada una en un único semestre.

select descripcion, (creditos/3)*2 horas
from asignaturas;

descripcionhoras
DISEÑO Y GESTION DE BASES DE DATOS4.00000
FUNDAMENTOS DE LAS BASES DE DATOS4.00000
FUNDAMENTOS DE LA PROGRAMACION6.00000
HISTORIA DE LA INFORMATICA3.00000
PROGRAMACION CONCURRENTE4.00000

Los créditos de cada asignatura son anuales: divididos entre 3, obtenemos las horas a impartir durante un año lectivo cada semana; si lo reducimos a un único semestre, cada semana tendremos el doble de horas de clase.


Descripción de las asignaturas y número de horas semanales de las asignaturas con menos de 4 horas semanales de clase

select descripcion, creditos 
from asignaturas 
where (creditos/3)*2 < 4


descripcioncreditos
HISTORIA DE LA INFORMATICA4.5


Funciones de agregados


Se dispone de una serie de funciones de agregados que retornan valores calculados sobre una determinada columna o columnas.

La diferencia con las mostradas en la sección anterior es que estas funciones trabajan sobre conjuntos de valores: devuelven un único valor resultado de procesar varias tuplas seleccionadas mediante la condición de la cláusula where; si no se especifica ésta, el cálculo se realiza sobre la totalidad de la columna.


 
COUNT( * )número de filas
COUNT( [DISTINCT] expr )número de valores distintos en expr
SUM( [DISTINCT] expr )suma de todos los valores en expr
AVG( [DISTINCT] expr )promedio de todos los valores en expr
MINexpr )el más pequeño de todos los valores en expr
MAXexpr )el mayor de todos los valores en expr









Las funciones de tipo estadístico precisan que la expresión que se evalúe se construya sobre columnas numéricas. La expresión expr puede contener el nombre de una columna o un cálculo sobre una o varias columnas.

Si se especifica la palabra clave distinct la expresión obligatoriamente ha de ser un nombre de columna, y se asume que la función se calcula únicamente sobre valores distintos de la expresión.


¿Cuántos profesores hay en nuestra base de datos?

select count(*) profes from profesores;

profes
3


¿Cuántas asignaturas de más de 4 créditos tenemos?

select count(*) from asignaturas
where creditos > 4;

count(*)
5


¿Cuantos valores de créditos distintos hay?

select count(distinct creditos) quecreditos
from asignaturas;

quecreditos
3

Hay 3 valores distintos de créditos: 6.0, 9.0 y 4.5.

codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
FBDFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
FPFUNDAMENTOS DE LA PROGRAMACION9.04.5
HIHISTORIA DE LA INFORMATICA4.5 
PCPROGRAMACION CONCURRENTE6.01.5



Sobre el modificador DISTINCT

Todas las funciones de agregados (menos count(*)) ignoran los nulos (NULL). Por tanto, select count(creditos) from asignaturas devolvería la cantidad de filas en la tabla asignaturas que no tienen un nulo en creditos.

select count(*) filas, count(creditosp) valores, count(distinct creditosp) distintos
from asignaturas;

filasvaloresdistintos
543

El uso del modificador distinct implica, además, que no se tienen en cuenta los valores duplicados. Supongamos una tabla con cinco filas y una columna colx y que queremos obtener la media de los valores almacenados en ella (NULL,1,1,1,3): AVG(colx) nos devolvería 1.5, mientras que AVG(distinct colx) nos devolvería 2.

Por ejemplo:

select avg(creditosp) sinDis, avg(distinct creditosp) conDis from asignaturas;

sinDisconDis
2.625003.00000