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

Resumen sintaxis

• Join (concatenación de tablas):

SELECT ... 

FROM tabla1

[{LEFT|RIGHT} OUTER] JOIN (tabla2, tabla3, ...) ON (condiciones);

• Funciones para datos agregados:

COUNT([DISTINCT] expr): Cuenta datos

SUM([DISTINCT] expr): Sumatorio 

AVG([DISTINCT] expr): Media 

MIN(expr): valor mínimo

MAX(expr): valor máximo

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 p.nombre, a.descripcion 

from asignaturas a, profesores p, imparte i

where p.dni = i.dni and i.asignatura = a.codigo;

Inner join, simple join

Realmente, se trata de la misma consulta habitual pero con una sintaxis alternativa usando join:

select p.nombre, a.descripcion 

from asignaturas a

join (imparte i, profesores p) on (a.codigo=i.asignatura and i.dni=p.dni);

También funciona:

select p.nombre, a.descripcion 

from asignaturas a

join imparte i on (a.codigo=i.asignatura)

join profesores p on (i.dni=p.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.

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 p.nombre, a.descripcion 

from asignaturas a

join (imparte i, profesores p) on (a.codigo=i.asignatura and i.dni=p.dni)

where a.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); 

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.

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

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

Muestra los coordinadores que tienen asignatura y todas las asignaturas. 

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

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:

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. 

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;

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;

Funciones de agregación

Se dispone de una serie de funciones para la agregación de datos 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. 

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;

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

select count(*) from asignaturas

where creditos > 4;

¿Cuantos valores de créditos distintos hay?

select count(distinct creditos) quecreditos

from asignaturas;

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;

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;