Entendiendo las tablas autoreferenciadas

Como ya hemos visto en estos dos artículos:

https://firebird21.wordpress.com/2013/05/06/ejemplo-no-004-usando-un-inner-join-para-autoreferenciar-una-tabla/

https://firebird21.wordpress.com/2013/07/19/autorreferenciar-una-tabla-algunos-ejemplos/

podemos escribir una consulta en la cual una tabla se referencia a sí misma. Explayémonos un poco más sobre este tema.

Evidentemente para que referenciemos una tabla con sí misma en ella deben encontrarse los datos que necesitamos. Aquí tenemos dos posibilidades:

1. Que la referencia se haga a dos columnas de la tabla
2. Que la referencia se haga a una sola columna de la tabla

Quizás habrás notado que en el primero de los dos artículos superiores se usaron dos columnas y en el segundo artículo solamente se usó una columna.

En general, cuando se trata de una tabla maestra (es decir: una tabla que no depende de otras tablas) usamos la primera opción. Y cuando se trata de una tabla de movimientos (es decir: una tabla que depende de otras tablas) usamos la segunda opción. Pero esa no es una regla inmutable, es lo más común pero no está grabado en piedra.

Ejemplo 1:

Necesitamos registrar los datos de algunos animales (por ejemplo: perros) y entre otras cosas queremos saber cual es el padre de cada perro, cual es el abuelo, etc. (lo que en castellano se llama pedigrí)

Para ello podríamos tener una tabla para los hijos, otra para los padres, otra para los abuelos, etc. Pero sería tedioso trabajar así porque tendríamos muchos datos duplicados (un mismo perro podría encontrarse en dos o en tres tablas). Una manera mucho más racional sería tener una sola tabla e identificar en ella al padre de cada perro.

(si haces clic en la imagen la verás más grande)

Y los datos de esa tabla podrían ser algo así:

(si haces clic en la imagen la verás más grande)

Entonces, para ver los nombres de cada perro y los nombres de sus respectivos padres podríamos escribir:

SELECT
   H.ANI_IDENTI AS "Id. perro",
   H.ANI_NOMBRE AS "Nombre del perro",
   H.ANI_FECNAC AS "Fecha nacimiento",
   P.ANI_IDENTI AS "Id. padre",
   P.ANI_NOMBRE AS "Nombre del padre"
FROM
   ANIMALES H
LEFT JOIN
   ANIMALES P
      ON H.ANI_IDEPAD = P.ANI_IDENTI

Como ves, la tabla ANIMALES tiene un JOIN a la tabla ANIMALES, o sea que se referencia a sí misma. Al ejecutar ese SELECT obtendríamos este resultado:

(si haces clic en la imagen la verás más grande)

Donde podemos ver el nombre del padre de cada perro. En algunos casos, también podríamos obtener el nombre del abuelo (INDIO es hijo de AFRO quien a su vez es hijo de SATAN).

SELECT
   H.ANI_IDENTI AS "Id. perro",
   H.ANI_NOMBRE AS "Nombre del perro",
   H.ANI_FECNAC AS "Fecha nacimiento",
   P.ANI_IDENTI AS "Id. padre",
   P.ANI_NOMBRE AS "Nombre del padre",
   A.ANI_IDENTI AS "Id. abuelo",
   A.ANI_NOMBRE AS "Nombre del abuelo"
FROM
   ANIMALES H
LEFT JOIN
   ANIMALES P
      ON H.ANI_IDEPAD = P.ANI_IDENTI
LEFT JOIN
   ANIMALES A
      ON P.ANI_IDEPAD = A.ANI_IDENTI

Si ejecutamos este SELECT esto será lo que obtendremos:

(si haces clic en la imagen la verás más grande)

 El cual claramente nos indica que el padre de INDIO es AFRO y que su abuelo es SATAN. De similar manera podríamos obtener los nombres de los perros que no tienen padre (bueno, en la vida real sí lo tienen pero no los tenemos registrados quizás porque no sabemos sus nombres), de los perros que no tienen abuelos, de los perros que son hermanos entre sí, de los hijos de un perro, de los nietos de un perro, etc.

En este ejemplo hemos usado dos columnas de la tabla ANIMALES (ANI_IDENTI y ANI_IDEPAD) para hacer la referencia.

Ejemplo 2:

Queremos conocer los nombres de los alumnos que se han aplazado en Matemática y también se han aplazado en Historia. Las calificaciones van del 0 al 100 y quienes obtienen menos de 40 puntos se aplazan.

La estructura de nuestra tabla de CALIFICACIONES es la siguiente:

(si haces clic en la imagen la verás más grande)

Y estas son algunas de sus filas:

(si haces clic en la imagen la verás más grande)

 Entonces, para ver los nombres de los alumnos que se aplazaron en Matemática y también en Historia podríamos escribir:

SELECT
   C.CAL_IDEALU AS "Id. alumno",
   A.ALU_NOMBRE AS "Nombre del alumno",
   C.CAL_NOTAXX AS "Matemática",
   D.CAL_NOTAXX AS "Historia"
FROM
   CALIFICACIONES C
JOIN
   CALIFICACIONES D
      ON C.CAL_IDEALU = D.CAL_IDEALU
JOIN
   ALUMNOS A
      ON C.CAL_IDEALU = A.ALU_IDENTI
WHERE
   C.CAL_NOTAXX < 40 AND
   D.CAL_NOTAXX < 40 AND
   C.CAL_CODMAT = 'MAT' AND
   D.CAL_CODMAT = 'HIS'

Con lo cual obtendríamos:

(si haces clic en la imagen la verás más grande)

 Y así sabríamos que RAQUEL se ha aplazado en ambas materias.

En este ejemplo hemos usado solamente una columna de la tabla CALIFICACIONES (la columna: CAL_IDEALU) para hacer la referencia pero hemos necesitado además de un WHERE para establecer la condición.

Conclusión:

Hay muchos casos en los cuales lo más eficiente que podemos tener es una tabla que se referencia a sí misma. Siempre que podamos meter toda la información que necesitamos en una sola tabla es una posibilidad a considerar, pues evitamos la redundancia y en general las consultas son más rápidas.

Artículos relacionados:

Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla

Autorreferenciar una tabla. Algunos ejemplos

El índice del blog Firebird21