DQL (Multi-tabla)

Tema	Título	Subtema	Versión
04	*El modelo físco: SQL*	*PARTE III*	v 1.0
04	DQL Multi-tabla

TEMA 4: El modelo físico: SQL (multi-tabla)

Índice

DQL (consultas multi-tabla)

Introducción

Muchas consultas solicitan datos procedentes de dos o más tablas en la BD. SQL permite recuperar datos que responden a estas peticiones mediante consultas compuestas o multi-tabla.

Por ejemplo, tenemos estas dos tablas:

BD de ejemplo

PUEBLOS = codigo_postal + nombre + comarca
ALUMNOS = num + nombre + grupo + cp
- C. Aj: cp -> PUEBLOS (codigo_postal)

Queremos realizar una consulta donde aparezca el nombre del alumno y el nombre del pueblo donde vive. Como la información está en 2 tablas, deberemos:

Poner las 2 tablas en el FROM

Tablas necesarias separadas por “,”. Da igual el orden.
Poner en el WHERE cómo están relacionadas las 2 tablas: suele ser una condición sobre la clave ajena.

Enlace entre las tablas del FROM. Por regla general es la clave ajena de una tabla con la clave primaria de la otra tabla.

SELECT alumnos.nombre, pueblos.nombre
   FROM alumnos, pueblos
   WHERE alumnos.cp = pueblos.código_postal

El resultado de la consulta anterior sería:

Nombre	Nombre
Pepe García	Sueca
Pepa Sales	Sueca
Andreu Albors	Sollana
Martí Manyes	Cullera

conflicto de nombres

En una misma tabla no podemos tener dos columnas con el mismo nombre, con lo cual hasta ahora no habíamos tenido este problema. Pero si tenemos dos tablas y en ambas hay una columna con el mismo nombre, SQL no sabrá a qué columna nos referimos. Por tanto, es necesario usar nombres calificados: nombre_tabla.nombre_columna.

Explicación de por qué poner el enlace entre las tablas

Tenemos las siguientes tablas en nuestra base de datos:

ALUMNOS 3 registros

ALUMNOS
num	nombre	cp
1	Pep	46410
2	Pepa	46410
3	Pepito	46760

PUEBLOS 2 registros

PUEBLOS
cpostal	nombre	comarca
46410	Sueca	Ribera Baja
46760	Tabernas	La Valldigna

Clave ajena

Tenemos una clave ajena entre Alumnos y Pueblos (relación un a muchos):

C. Aj: cp -> PUEBLOS(cpostal)

Queremos obtener en una consulta:

Todos los datos de los alumnos y los correspondientes datos de los pueblos de cada alumno.

Para lo cual escribimos la siguiente consulta y obtenemos el siguiente resultado:

consulta SQL

SELECT alumnos.*, pueblos.*
   FROM alumnos, pueblos

resultado de la consulta

num	nombre	cp	cpostal	nombre	comarca
1	Pep	46410	46410	Sueca	Ribera Baja
1	Pep	46410	46760	Tabernas	La Safor
2	Pepa	46410	46410	Sueca	Ribera Baja
2	Pepa	46410	46760	Tabernas	La Safor
3	Pepito	46760	46410	Sueca	Ribera Baja
3	Pepito	46760	46760	Tabernas	La Safor

¿Qué ha pasado? ¿Por qué tenemos tantas filas?, ¿Si solo teníamos 3 alumnos?

Producto cartesiano

si no ponemos el enlace, obtendremos lo que se llama producto cartesiano de las filas. Es decir, cada fila de alumnos la relacionará con cada fila de pueblos. Por tanto, tendremos 3 x 2 = 6 filas:

En la siguiente tabla he marcado con un aspa las filas que SI se correnden con la realidad:

num	nombre	cp	cpostal	nombre	comarca
1	Pep	46410	46410	Sueca	Ribera Baja	[X]
1	Pep	46410	46760	Tabernas	La Safor	[ ]
2	Pepa	46410	46410	Sueca	Ribera Baja	[X]
2	Pepa	46410	46760	Tabernas	La Safor	[ ]
3	Pepito	46760	46410	Sueca	Ribera Baja	[ ]
3	Pepito	46760	46760	Tabernas	La Safor	[X]

Y ¿cómo he podido saber que las filas marcadas con un aspa son las que nos interesan? Porque el código postal de los pueblos coincide con el cp de los alumnos. Por tanto, si queremos obtener sólo las filas que nos interesan, debemos poner en la cláusula WHERE la condición de que el cp de los alumnos sea igual al cpostal de los pueblos:

consulta SQL con enlace entre tablas

SELECT alumnos.*, 
       pueblos.*
FROM alumnos, pueblos
WHERE alumnos.cp = pueblos.cpostal

resultado de la consulta con enlace entre tablas

num	nombre	cp	cpostal	nombre	comarca
1	Pep	46410	46410	Sueca	Ribera Baja
2	Pepa	46410	46410	Sueca	Ribera Baja
3	Pepito	46760	46760	Tabernas	La Safor

Y ahora, si queremos, como el código postal aparece repetido en dos columnas, quitaremos una cualquiera de las dos (o las dos, si no quisiéramos mostrarlo):

consulta SQL sin cpostal repetido

SELECT alumnos.*, 
    pueblos.nombre, 
    pueblos.comarca
FROM alumnos, pueblos
WHERE alumnos.cp = pueblos.cpostal

resultado de la consulta sin cpostal repetido

num	nombre	cp	nombre	comarca
1	Pep	46410	Sueca	Ribera Baja
2	Pepa	46410	Sueca	Ribera Baja
3	Pepito	46760	Tabernas	La Safor

Ejemplo para más de dos tablas

Supongamos que ahora tenemos las siguientes tablas en nuestra base de datos:

BD de ejemplo

ASIGNATURAS = codigo + nombre
ALUMNOS = num + nombre + grupo + cp
MATRÍCULAS = alu + asig + nota
- C. Aj: alu -> ALUMNOS(num)
- asig -> ASIGNATURAS(código)

Queremos obtener de cada alumno: su nombre y el nombre de las asignaturas en las que está matriculado.

Solución:

El nombre del alumno está en la tabla ALUMNOS y el nombre de las asignaturas en la tabla ASIGNATURAS. Pero también necesitamos la tabla MATRÍCULAS, ya que hace de enlace entre ambas (es en esa tabla donde está la información de qué asignaturas está matriculado cada alumno).
Dicho de otro modo: la información de las matrículas está en la tabla MATRIÍCULAS pero los campos a obtener estarán en otras dos tablas. Por tanto, habrá que hacer el enlace de la tabla MATRÍCULAS a las otras dos.
Si lo pensamos desde el punto de vista de un diagrama entidad-relación, tendriamos una relación muchos a muchos entre ALUMNOS y ASIGNATURAS, y la tabla MATRÍCULAS sería la tabla de relación entre ambas.

Si tenemos 3 tablas, para enlazarlas será necesario poner los 2 enlaces entre ellas (claves ajenas).

consulta SQL con 3 tablas

SELECT alumnos.nombre, asignaturas.nombre
FROM alumnos, asignaturas, matrículas -- tablas, no importa el orden
WHERE 
    alumnos.num = matriculas.alu  -- clave ajena entre ALUMNOS y MATRÍCULAS
    AND matricules.assig = asignaturas.código --  clave ajena entre MATRÍCULAS y ASIGNATURAS

resultado de la consulta con 3 tablas

Nombre	Nombre
Pep García	Matemáticas
Pep García	Valenciano
Pepa Sales	Valenciano
Pepa Sales	Inglés
Martí Manyes	Valenciano

Columnas de emparejamiento

El proceso de formar parejas de filas haciendo coincidir los contenidos de las columnas relacionadas se denomina componer las tablas (o bien: “hacer un join” de las tablas).

Las columnas usadas en la composición de las tablas se llaman columnas de emparejamiento. En la mayoría de los casos, las columnas de emparejamiento están formadas por la clave ajena de una tabla con la clave principal de la otra tabla. Habrá que tener en cuenta si entre las tablas hay alguna clave ajena compuesta o bien hay distintas claves ajenas que relacionan varias tablas:

Relacionar tablas con clave compuesta

Habrá que relacionar cada una de las parejas de campos.
Ejemplo de relación con clave compuesta
- FACTURAS = anyo + numero + cliente + fecha
- LINEAS_FAC = anyo + numero + linea + articulo + cantidad + precio
  - C. Aj: (anyo + numero) -> FACTURAS (anyo + numero)
Si queremos obtener todas las líneas de cada factura, debemos relacionar las dos tablas por los dos campos de la clave ajena:
```
SELECT facturas.*, lineas_fac.*
    FROM facturas, lineas_fac
    WHERE facturas.anyo = líneas_fac.anyo 
    AND facturas.numero = líneas_fac.numero -- clave ajena compuesta
```
Relacionar tablas con más de una clave ajena entre ellas

Habrá que "seguir el camino" de claves ajenas según la consulta que queremos hacer. Por ejemplo:

Tenemos el siguiente esquema relacional:
Esquema relacional
- CICLISTAS = dorsal + nombre + edad
- ETAPAS = numero + kms + ciclista - C. Aj: ciclista -> CICLISTAS (dorsal)
- PUERTOS = nombre + altura + categoría + etapa + ciclista - C. Aj: etapa -> ETAPAS(numero) - C. Aj: ciclista -> CICLISTAS(dorsal)
Ejemplo b1 (verde)

Queremos obtener de cada ciclista: los puertos ganados y la etapa por la que pasa cada uno de esos puertos Relacionaremos:
- ciclistas con puertos
- puertos con etapas
Consulta SQL b1 (verde)

La conusta SQL sería:
```
SELECT ciclistas.*, puertos.*, etapas.*
    FROM ciclistas, puertos, etapas
    WHERE ciclistas.dorsal = puertos.ciclista
        AND puertos.etapa = etapas.numero
```
Hay que fijarse en la claúsula WHERE, las claves ajenas seleccionadas.
Ejemplo b2 (rojo)

Muestra de cada ciclista, los puertos ganados y la etapa por la que pasa cada uno de esos puertos:

Relacionaremos:
- ciclistas con puertos
- puertos con etapas
Consulta SQL b2 (rojo)

La consulta sería:
```
SELECT ciclistas.*, puertos.*, etapas.*
    FROM ciclistas, puertos, etapas
    WHERE ciclistas.dorsal = etapas.ciclista
        AND etapas.numero = puertos.etapa
```
Hay que fijarse en la claúsula WHERE, las claves ajenas seleccionadas.

Condiciones de búsqueda en consultas multi-tabla

En la cláusula WHERE podemos combinar emparejamiento de columnas y otras condiciones de búsqueda. Por ejemplo. Queremos saber el nombre de la editorial y el título de los libros que tienen un precio mayor de 30 euros.

SELECT nombre_edit, título_libro
   FROM libro, editorial
   WHERE libro.codi_edit = editorial.codi_edit
        AND precio_libro > 30

Uso de alias en las tablas

En la cláusula FROM podemos hacer uso de alias de tablas, por diferentes motivos:

Para simplificar nombres calificados de las tablas:

Usamos la palabra reservada AS para indicar el alias, pero es opcional.

También podemos usar alias en consultas con una única tabla.

SELECT vend.nombre, ofi.ciudad, ofi.comarca  -- usamos los alias para referenciar campos
    FROM ventas AS vend, oficinas AS ofi     -- aplicamos alias a las tablas
    WHERE vend.oficina = ofi.codigo.         -- usamos los alias para referencuas campos

Para crear una consulta multi-tabla que relaciona una tabla consigo misma.

Por ejemplo, en la tabla empleados tenemos a todos los empleados de una empresa: tanto los trabajadores subordinados como sus superiores.

En el esquema E/R tendríamos:
Esquema E/R empleados
- EMPLEADOS = código+ nombre + fecha_nacimiento + superior
  - C. Aj: superior -> EMPLEADOS(código)
En esa tabla, cada empleado tiene el código de su superior, pero no su nombre.
Queremos obtener una consulta en la que aparezca el nombre del empleado junto al nombre (no el código) de su superior. Implementando la consulta que queremos en SQL, tendríamos:
```
SELECT sub.nombre, jefe.nombre
    FROM empleados AS sub, empleados AS jefe
    WHERE sub.superior = jefe.código
```
Truco para relaciones unarias

El truco está en hacer como si tuviéramos dos tablas de empleados: una donde están los empleados como subordinados y otra donde están los empleados como superiores (jefes).

componer tablas con JOIN

Hay otra forma de realizar las composiciones de tablas: el uso de JOIN. Por ejemplo, estas dos sentencias sacan el mismo resultado:

consulta sin JOIN

SELECT alumnos.*, 
        pueblos.nombre, 
        pueblos.comarca
FROM alumnos, pueblos
WHERE alumnos.cp = pueblos.cpostal
    AND alumnos.edad > 17

consulta con JOIN

`sql SELECT alumnos.*, pueblos.nombre, pueblos.comarca FROM alumnos JOIN pueblos on alumnos.cp = pueblos.cpostal WHERE alumnos.edad > 17

Esta segunda forma es más eficiente cuando alguna de las dos tablas tiene un índice en alguna de las columnas de emparejamiento (por ejemplo, si una es clave principal).

Además, con JOIN, podremos decir más sobre cómo queremos enlazar dos tablas. ¿Qué pasaría si hay alumnos sin pueblo (es decir, alumnos que tienen NULL en el cp)? Que no saldrían en el listado (ni con el operador coma ni con el JOIN). Tampoco aparecerán los pueblos que carecen de alumnos. Para solucionar esto, lo indicaremos de alguna forma en el JOIN. Ahora veremos los distintos tipos de JOIN que podemos realizar entre dos tablas.

Tipo	Nombre	Descripción
	[INNER] JOIN	Sólo parejas de A y B relacionadas, Igual que el FROM
	LEFT JOIN	Todos los A (con los B relacionados)
	RIGHT JOIN	Todos los B (con los A relacionados)
	OUTER JOIN	Todos los A y todos los B (con los correspondientes relacionados)

Problema con OUTER JOIN en MySQL

El outer join (o hoja outer join) no funciona en MySQL (al menos en algunas versiones). Vemos la solución (hacer LEFT JOIN UNION RIGHT JOIN):

SELECT alumnos.*, pueblos.*
    FROM alumnos LEFT JOIN pueblos
        on alumnos.cp = pueblos.cpostal
    WHERE edad > 17

UNION

SELECT alumnos.*, pueblos.*
    FROM alumnos RIGHT JOIN pueblos
        on alumnos.cp = pueblos.cpostal
    WHERE edad > 17

Nota sobre JOIN

Esta forma de hacer las composiciones (con JOIN) es mejor, ya que la otra primero hace el producto cartesiano y después elimina filas conel filtro WHERE, mientras que esta es más rápida ya que simplemente relaciona las filas con el mismo valor en la clave ajena. Ahora bien: es más rápida si una de las columnas de emparejamiento es una clave primaria o tiene un índice creado sobre ella.

links W3Schools sobre JOIN

Join

Ejercicios multi-tabla

Ejercicio 1

De cada partido queremos mostrar los códigos de los equipos y el nombre de la ciudad en la que juegan.

Ejercicio 2

De cada partido que falta por jugar queremos mostrar en qué fecha se disputará, los nombres cortos de los equipos, los nombres de las respectivas ciudades y el total de habitantes de las dos ciudades.

Ejercicio 3

De cada equipo: el presupuesto, lo que se gasta con los jugadores y el porcentaje que representa.

Ejercicio 4

Lista de jugadores donde conste: nombre del jugador y nombre de la ciudad en la que juega.

Ejercicio 5

Cantidad total de goles de penalti marcados por equipos de ciudades de menos de 200.000 habitantes.

Ejercicio 6

En qué fechas se han enfrentado Valencia y Barça (sabiendo que los códigos son “vale” y “bar”). Muestra cuál jugaba en casa y quién fuera y el resultado de goles.

Ejercicio 7

En qué fechas se han enfrentado Valencia y Barça (sabiendo que los nombres cortos son “Valencia” y “Barça”). Muestra cuál jugaba en casa y quién fuera (nombre largos) y el resultado de goles.

Ejercicio 8

Muestra parejas de jugadores en los que uno de ellos cobra más de 10 vueltas que el otro. Muestra también sus sueldos.

Ejercicio 9

Modifica el ejercicio anterior para que también aparezcan los respectivos nombres (largos) de los equipos.

Ejercicio 10

Centrocampistas que cobran más que algún delantero de su equipo. Es necesario mostrar los 2 nombres y los 2 sueldos.

Ejercicio 11

Parejas de portero y goleador de un mismo equipo en el que el goleador haya marcado más goles que los goles que ha encajado el portero. Hay que mostrar el equipo y los nombres del portero y goleador con sus respectivos goles. Ordenado por el equipo y el nombre del portero.

Ejercicio 12

Queremos comparar los goles de Messi y Ronaldo (no sabemos el nombre completo de ellos). Muestra el nombre del jugador y toda la estadística de los goles como jugadores pero sólo de ambos.

Ejercicio 13

Media de goles marcados en cada jornada y fecha de la jornada (un decimal).

Ejercicio 14

¿Cuántos partidos ha ganado/empatado/perdido cada equipo, pero sin diferenciar si está en casa o fuera (sólo los totales).

EQUIPO	PARTIDOS	RESULTADO
ath	11	ganados
ath	8	empatados
ath	16	perdidos
atm	22	ganados
atm	6	empatados
atm	8	perdidos
bar	29	ganados
...	...	...

Ejercicio 15

¿Cuántos partidos le queda por jugar en total en cada equipo?

Ejercicios base de datos `Employees` (Estabase de datos de ejemplo de MySQL)

Ejercicios base de datos Employees

Reglas para procesamiento de consultas multi-tabla

Vemos qué hace SQL para generar los resultados de una consulta con una sentencia SELECT:

Si la sentencia es una UNION de sentencias SELECT, aplica los pasos 2 hasta el 7 en cada una de las sentencias para generar los resultados individuales de cada SELECT.
Forma el producto cartesiano de las tablas indicadas en la cláusula FROM. Si la cláusula FROM designa una sola tabla, el producto es esa tabla.
Si existe una cláusula WHERE, aplica su condición de búsqueda en cada fila de la tabla producto, reteniendo aquellas filas para las que la condición de búsqueda es TRUE (y descartando aquellas para las que es FALSE o NULL).
Si existe cláusula GROUP BY, las filas retenidas anteriormente son agrupadas por los campos de esta cláusula.
Si existe HAVING, se retienen aquellos grupos que cumplen la condición de esta cláusula.
Se calcula el valor de cada elemento de la cláusula SELECT para cada fila retenida (o bien, para cada grupo si existe el GROUP BY).
Si se especifica SELECT DISTINCT, elimina las filas duplicadas de los resultados que se hubiesen producido.
Si la sentencia es una UNION de sentencias SELECT, mezcla las filas retenidas en cada SELECT, en una única tabla de resultados. Elimina las filas duplicadas a menos que se haya especificado UNION ALL.
Si existe una cláusula ORDER BY, ordenar los resultados de la consulta según se haya especificado.

Resumen de las consultas multi-tabla

En una consulta multitabla, las tablas que contienen los datos son designadas en la cláusula FROM.
Si no ponemos el join de las tablas del FROM, se generan tantas filas como combinaciones distintas de las filas de las tablas (producto cartesiano).
Las consultas multi-tabla más habituales utilizan las relaciones creadas por las claves primarias y claves ajenas.
Una tabla puede componerse consigo misma; para ello se requiere el uso de alias.
Con el operador JOIN podemos realizar más variantes de las composiciones.
Para procesar una consulta multi-tabla, los SGBD dan los siguientes pasos:
- Para toda sentencia SELECT de la UNION:
  - Si el FROM tiene más de una tabla, forma el producto cartesiano
  - Si WHERE, selecciona las filas
  - Calcula el valor de cada columna de SELECT para cada fila
  - Si DISTINCT, elimina filas repetidas
Si UNION, junta filas obtenidas. Si no tiene ALL, elimina filas repetidas.
Si ORDER BY, ordena filas.