Recuperar información de varias tablas en MySQL

Las instrucciones creadas hasta el momento obtienen datos de una base de datos. Pero MySQL puede esforzarse más. Ya hemos mencionado que la capacidad de un DBMS relacional radica en su capacidad para combinar información de varias tablas para responder a preguntas que no se pueden solucionar con tablas individuales. En este tutorial veremos cómo escribir instrucciones para ello.

Un tipo de operación que selecciona información de varias tablas son las combinaciones, que generan un resultado al combinar información de una tabla con la de otra. Para ello, se comparan los valores comunes de las tablas. Otro tipo de operación con varias tablas usa una instrucción SELECT anidada dentro de otra. La instrucción anidada se denomina subconsulta.
Veamos un ejemplo de combinación. En un tutorial anterior, presentamos una consulta para recuperar puntuaciones de notas sin apenas explicarla. La consulta implica una combinación triple, y la crearemos en dos pasos. En primer lugar, creamos una consulta para seleccionar notas de una fecha concreta:

mysql> SELECT student_id, date, score, category
-> FROM grade_event INNER JOIN score
-> ON grade_event.event_id = score.event_id
-> WHERE date = '2008-09-23';


  
    student_id
    date
    score
    category
  
  
    1
    2008-09-23
    15
    Q
  
  
    2
    2008-09-23
    12
    Q
  
  
    3
    2008-09-23
    11
    Q
  
  
    5
    2008-09-23
    13
    Q
  
  
    6
    2008-09-23
    18
    Q

La consulta busca la fila grade_event con la fecha proporcionada (‘2008-09-23’) y utiliza el ID de evento de dicha fila para localizar puntuaciones con el mismo ID. Por cada combinación de grade_event y score que coincida, muestra el ID de alumno, la nota, la fecha y la categoría del evento.
La consulta se diferencia de las anteriores en dos aspectos principales:

La cláusula FROM hace referencia a más de una tabla ya que recuperamos datos de más de una tabla:
FROM grade_event INNER JOIN score
La cláusula ON especifica que las tablas grade_event y score se combinan mediante la comparación de valores event_id en ambas tablas:
ON grade_event.event_id = score.event_id

Hacemos referencia a las columnas event_id como grade_event.event_id y score.event_id con la sintaxis nombre_tabla.nombre_columna, ya que event_id aparece en ambas tablas. El resto de columnas de la consulta (date, score y category) se pueden usar sin un calificador de tabla ya que solamente aparecen en una de las tablas y son inequívocas.
Por lo general, resulta preferible que califiquemos todas las columnas de una combinación para saber a qué tabla pertenece cada una de las columnas. En su forma calificada, la consulta es la siguiente:

SELECT score.student_id, grade_event.date, score.score, grade_event.category
FROM grade_event INNER JOIN score
ON grade_event.event_id = score.event_id
WHERE grade_event.date = ‘2008-09-23’;

La primera consulta utiliza la tabla grade_event para asignar una fecha a un ID de evento, que posteriormente utiliza para buscar las calificaciones que coincidan en la tabla score. El resultado de la consulta contiene valores student_id pero los nombres tendrían más sentido. Al utilizar la tabla student, podemos asignar ID de alumnos a nombres, el segundo paso. Para mostrar los nombres, recuerde que las tablas score y student tienen columnas student_id que permiten vincular sus filas. La consulta restante es la siguiente:

mysql> SELECT
-> student.name, grade_event.date, score.score, grade_event.category
-> FROM grade_event INNER JOIN score INNER JOIN student
-> ON grade_event.event_id = score.event_id
-> AND score.student_id = student.student_id
-> WHERE grade_event.date = '2008-09-23';


  
    name
    date
    score
    category
  
  
    Megan
    2008-09-23
    15
    Q
  
  
    Joseph
    2008-09-23
    12
    Q
  
  
    Kylr
    2008-09-23
    11
    Q
  
  
    Abby
    2008-09-23
    13
    Q
  
  
    Nathan
    2008-09-23
    18
    Q
  
  
    ...

Esta consulta presenta varias diferencias con respecto a la anterior según podemos ver a continuación:

La cláusula FROM incluye la tabla student ya que la instrucción la utiliza además de las tablas grade_event y score
La columna student_id era inequívoca, por lo que podemos hacer referencia a la misma de forma cualificada (student_id) o sin cualificar (score.student_id).
Ahora ya no es inequívoca ya que aparece en las tablas score y student. Por ello, es necesario cualificarla como score.student_id o student.student_id para indicar qué tabla se utiliza.
La cláusula ON especifica que las filas de la tabla score se comparan con la tabla student en función del ID del alumno:
ON ... score.student_id = student.student_id
La consulta muestra el nombre del alumno y no el ID. Para mostrar los dos, basta añadir student.student_id a la lista de columnas.

Con esta consulta, puede introducir cualquier fecha y recuperar las calificaciones de dicha fecha, junto con el nombre de los alumnos y la categoría de la calificación. No es necesario conocer el ID de alumno ni de evento. MySQL se encarga de determinar los correspondientes valores de ID y de utilizarlos para comparar las filas.
Otra de las tareas que componen nuestro proyecto de calificaciones consiste en resumir las faltas de asistencia de los alumnos, que se registran por identificador (ID) de alumno y por fecha dentro de la tabla absence. Para obtener nombres de alumnos (no solamente sus ID), tendremos que combinar la tabla absence a la tabla student, en función del valor student_id. La siguiente consulta enumera el ID y nombre del alumno junto al número de faltas de asistencia:

mysql> SELECT student.student_id, student.name,
-> COUNT(absence.date) AS absences
-> FROM student INNER JOIN absence
-> ON student.student_id = absence.student_id
-> GROUP BY student.student_id;


  
    student_id
    name
    absences
  
  
    3
    Kyle
    1
  
  
    5
    Abby
    1
  
  
    10
    Peter
    2
  
  
    17
    Will
    1
  
  
    20
    Avery
    1

Nota: Aunque la columna GROUP BY tiene un cualificador, no es estrictamente necesario. Hace referencia a columnas de resultados y sólo hay una de éstas con el nombre student_id, por lo que MySQL sabe a qué hacemos referencia.

El resultado generado por la consulta es correcto si solamente queremos saber los alumnos ausentes. Pero puede que nos pregunten por el resto de alumnos, por lo que necesitamos un valor para cada alumno, conocer el número de faltas de asistencia incluso para los alumnos que no han faltado a clase. Como el objetivo es diferente, la consulta también será diferente.
Para resolver esta pregunta, utilizaremos LEFT JOIN en lugar de una combinación interna. LEFT JOIN indica a MySQL que genere una fila de resultados por cada fila seleccionada de la tabla que aparece primero en la combinación (es decir, la tabla indicada a la izquierda de las palabras clave LEFT JOIN). Al incluir primero la tabla student, obtendremos resultados para todos los alumnos, incluso lo que no están en la tabla absence. Para crear esta consulta, utilice LEFT JOIN entre las tablas mencionadas en la cláusula FROM (en lugar de separarlas por coma) y una cláusula ON que indique cómo comparar las filas en ambas tablas:

mysql> SELECT student.student_id, student.name
-> COUNT(absence.date) AS absences
-> FROM student LEFT JOIN absence
-> ON student.student_id = absence.student_id
-> GROUP BY student.student_id;


  
    student_id
    name
    absences
  
  
    3
    Megan
    0
  
  
    2
    Joseph
    0
  
  
    3
    Kyle
    1
  
  
    4
    Katie
    0
  
  
    5
    Abby
    1
  
  
    6
    Nathan
    0
  
  
    7
    Liesl
    0

En un tutorial anterior, ejecutamos una consulta que generaba la caracterización numérica de los datos en la tabala score. El resultado de la consulta enumeraba ID de eventos pero no incluía fechas ni categorías ya que desconocíamos cómo combinar la tabla score con la tabla grade_event para asignar los ID a fechas y categorías. Ahora ya lo sabemos. La siguiente consulta es similar a la anterior, pero muestra las fechas y categorías en lugar de simples ID numéricos:

mysql> SELECT
-> grade_event.date, grade_event.category,
-> MIN(score.score) AS minimum,
-> MAX(score.score) AS maximum,
-> MAX(score.score) - MIN(score.score)+1 AS span,
-> SUM(score.score) AS total,
-> AVG(score.score) AS average,
-> COUNT(score.score) AS count
-> FROM score INNER JOIN grade_event
-> ON score.event_id = grade_event.event_id
-> GROUP BY grade_event.date;


  
    date
    category
    minimum
    maximum
    span
    total
    average
    count
  
  
    2008-09-03
    Q
    9
    20
    12
    439
    15.1379
    29
  
  
    2008-09-06
    Q
    8
    19
    12
    425
    14.1667
    30
  
  
    2008-09-09
    T
    60
    97
    38
    2425
    78.2258
    31
  
  
    2008-09-16
    Q
    7
    20
    14
    379
    14.0370
    27
  
  
    2008-09-23
    Q
    8
    20
    13
    383
    14.1852
    27
  
  
    2008-10-01
    T
    62
    100
    39
    2325
    80.1724
    29

Puede utilizar funciones como COUNT() y AVG() para generar un resumen de varias columnas, aunque provengan de tablas distintas. La siguiente consulta determina el número de calificaciones y la nota de todas las combinaciones de fecha y género de los alumnos:

mysql> SELECT grade_evernt.date, student.sex,
-> COUNT(score.score) AS count, AVG(score.score) AS average
-> FROM grade_event INNER JOIN score INNER JOIN student
-> ON grade_event.event_id = score.event_id
-> AND score. student_id = student.student_id
-> GROUP BY grade_event.date, student.sex;


  
    date
    sex
    count
    average
  
  
    2008-09-03
    F
    14
    14.6429
  
  
    2008-09-03
    M
    15
    15.6000
  
  
    2008-09-06
    F
    14
    14.7143
  
  
    2008-09-06
    M
    16
    13.6875
  
  
    2008-09-09
    F
    15
    77.4000
  
  
    2008-09-09
    M
    16
    79.0000
  
  
    2008-09-16
    F
    13
    15.3077
  
  
    2008-09-16
    M
    14
    12.8571
  
  
    2008-09-23
    F
    12
    14.0833
  
  
    2008-09-23
    M
    15
    14.2667
  
  
    2008-10-01
    F
    14
    77.7857
  
  
    2008-10-01
    M
    15
    82.4000

Podemos utilizar una consulta similar para realizar una de las tareas del proyecto de calificaciones: calcular la nota total por cada alumno al final del semestre:

SELECT student.student_id, student.name
SUM(score.score) AS total, COUNT(score.score) AS n
FROM grade_event INNER JOIN score INNER JOIN student
ON grade_event.event_id = score.event_id
AND score.student_id = student.student_id
GROUP BY score.student_id
ORDER BY tota;

No es obligatorio que una combinación se realice entre tablas diferentes. Puede parecer extraño pero puede combinar una tabla a sí misma. Por ejemplo, puede determinar si hay presidentes nacidos en la misma ciudad, si comprueba el lugar de nacimiento de todos los presidentes:

mysql> SELECT p1.last_name, p1.first_name, p1.city, p1.state
-> FROM president AS p1 INNER JOIN president AS p2
-> ON p1.city = p2.city AND p1.state = p2.state
-> WHERE (p1.last_name <> p2.last_name OR p1.first_name <> p2.first_name)
-> ORDER BY state, city, last_name;


  
    last_name
    first_name
    city
    state
  
  
    Adams
    John Quincy
    Braintree
    MA
  
  
    Adams
    John
    Braintree
    MA

Esta consulta presenta dos peculiaridades:

Es necesario hacer referencia a dos instancias de la misma tabla, por lo que crearemos alias de tabla (p1, p2). Como sucede con los alias de columna, la palabra clave AS es opcional al hacer referencia a los alias.
Cada fila de presidente coincide consigo misma pero no queremos que aparezca en el resultado. La cláusula WHERE no permite que esto suceda y garantiza que en las filas se comparen distintos presidentes.

Una consulta similar busca presidentes nacidos el mismo día. Sin embargo, no se pueden comparar directamente las fechas de nacimiento ya que perderíamos presidentes nacidos en años diferentes. En su lugar, utilizamos MONTH() y DAYOFMONTH() para comparar el mes y el día de la fecha de nacimiento:

mysql> SELECT p1.last_name, p1.first_name, p1.birth
-> FROM president AS p1 INNER JOIN president AS p2
-> WHERE MONTH(p1.birth) = MONTH(p2.birth)
-> AND DAYOFMONTH(p1.birth) = DAYOFMONTH(p2.birth)
-> AND (p1.last_name <> p2.last_name OR p1.first_name <> p2.first_name)
-> ORDER BY p1.last_name;


  
    last_name
    first_name
    birth
  
  
    Harding
    Warren G.
    1865-11-02
  
  
    Polk
    James K.
    1795-11-02

El uso de DAYOFYEAR() en lugar de la combinación de MONTH() y DAYOFMONTH() generaría una consulta más sencilla pero resultados incorrectos al comparar fechas de años bisiestos con fechas de años que no lo son.
Otro tipo de recuperación de varias tablas utiliza una subconsulta, una instrucción SELECT anidada dentro de otra. Imagine que desea identificar los alumnos con una asistencia perfecta. Es lo mismo que determinar qué alumnos no aparecen en la tabla absence, de esta forma:

mysql> SELECT * FROM student
-> WHERE student_id NOT IN (SELECT student_id FROM absence);


  
    name
    sex
    student_id
  
  
    Megan
    F
    1
  
  
    Joseph
    M
    2
  
  
    Katie
    F
    4
  
  
    Nathan
    M
    6
  
  
    Liesl
    F
    7
  
  
    ...

La instrucción SELECT anidada determina el conjunto de valores student_id presentes en la tabla absence y la instrucción SELECT exterior recupera filas student que no coinciden con dichos ID.
Una subconsulta también proporciona una solución a la pregunta formulada en un tutorial anterior sobre presidentes nacidos antes que Andrew Jackson. En la solución original se utilizaban dos instrucciones y una variable de usuario pero se puede conseguir con la siguiente subconsulta:

mysql> SELECT last_name, first_name, birth FROM president
-> WHERE birth < (SELECT birth FROM president
-> WHERE last_name = 'Jackson' AND first_name = 'Andrew');


  
    last_name
    first_name
    birth
  
  
    Washington
    George
    1732-02-22
  
  
    Adam
    John
    1735-10-30
  
  
    Jefferson
    Thomas
    1743-04-13
  
  
    Madison
    James
    1751-03-16
  
  
    Monroe
    James
    1758-04-28

La instrucción SELECT interna determina la fecha de nacimiento de Andrew Jackson y la externa recupera presidentes nacidos antes.

Comparte