CONSULTAS SQL. PARTE I

Vamos a presentar una serie de artículos para mostar las distintas posibilidades que puede ofrecer el SQL.
En esta primera parte nos centraremos en las JOINs de tablas.

LEFT OUTER JOIN

Supongamos la siguiente situación, una lista de alumnos que no estén matriculados en alguna asignatura obligatoria.

Para ello tenemos las entidades ALUMNO (alumnos matriculados en una universidad), ASIGNATOBL (lista de asignaturas obligatorias, existente en los grados) y ASIGNATOPC (lista de asignaturas opcionales, existente en los grados).

Entidad ALUMNO.
idPersona | idAsignatura | idCurso | idGrado
       P1            OB1        C1        G1
       P1            OB2        C1        G1
       P2            OP1        C1        G1
       P3            OP2        C1        G1
       P4            OP1        C1        G1
       P4            OB2        C1        G1
       P5            OB1        C1        G1
       P6            OP2        C1        G1

Donde la clave primaria es idPerona, idAsignatura, idCurso, idGrado.

Entidad ASIGNATOBL

idAsignatura| idCurso | idGrado | Nombre   | Duración
         OB1       C1        G1   ASIGOB1    S
         OB2       C1        G1   ASIGOB2    S
         OB3       C1        G1   ASIGOB3    S

Donde la clave primaria es idAsignatura, idCurso, idGrado.

Entidad ASIGNATOPC

idAsignatura| idCurso | idGrado | Nombre   | Duración
         OP1       C1        G1   ASIGOP1    S
         OP2       C1        G1   ASIGOP2    S
         OP3       C1        G1   ASIGOP3    S

Donde la clave primaria es idAsignatura, idCurso, idGrado.

La Consulta que haríamos:

SELECT A.idPersona, B.Nombre, B.Duracion
  FROM ALUMNO A
       LEFT OUTER JOIN
       ASIGNATOBL B
    ON A.idAsignatura = B.idAsinatura
   AND A.idCurso = B.idCurso
   AND A.idGrado = B.idGrado

Esto crea una nueva tabla resultante, que tendrá tantos registros como la entidad ALUMNO. El campo idPersona proviene de la tabla ALUMNOS y por tanto vendrá siempre informado a no NULOS. Los campos Nombre y Duración provienen de la tabla ASIGNATOBL en el caso de que coincidan las claves vendrán informados con valor en otro caso vendrán informados a NULOS.

El resultado de la consulta sería:
idPersona | Nombre       | Duración
       P1   ASIGOB1        S
       P1   ASIGOB2        S
       P2   NULL           NULL
       P3   NULL           NULL
       P4   NULL           NULL
       P4   ASIGOB2        S
       P5   ASIGOB1        S
       P6   ASIGOB2        S
       P7   NULL           NULL 

Vamos a comentar un par de cosas sobre esta consulta:

El bloque sobre el que estamos uniendo ambas entidades(tablas)
   ON A.idAsignatura = B.idAsinatura
  AND A.idCurso = B.idCurso
  AND A.idGrado = B.idGrado

El bloque sobre el que estamos definiendo el tipo de interacción de las tablas:
 FROM ALUMNO A
      LEFT OUTER JOIN
      ASIGNATOBL B
Aquí también es importante decir que cuando hacemos un "A LEFT OUTER JOIN B", las diferentes claves de interacción de la tablas (A y B), tienen que estar todas ellas en la tabla A, pudiendo no estar obligatoriamente todas en la tabla B. Es decir, A contiene B.

Podríamos añadir una sentencia WHERE [WHERE B.Nombre IS NULL].
En este punto debemos hacer una pausa. La sentencia WHERE es una condición de filtrado sobre la tabla temporal resultante de unir las tablas A y B. Es decir:
    ON A.idAsignatura = B.idAsinatura
   AND A.idCurso = B.idCurso
   AND A.idGrado = B.idGrado
   AND B.Nombre IS NULL
Es distinto a:
    ON A.idAsignatura = B.idAsinatura
   AND A.idCurso = B.idCurso
   AND A.idGrado = B.idGrado
 WHERE B.Nombre IS NULL

En el primer caso [AND B.Nombre IS NULL]. Usaría una condicion más para la creación de la nueva tabla temporal (en este caso no haríamos ningún filtro pues en la tabla B no hay valores nulos sobre la columna Nombre).

En el sengundo caso [WHERE B.Nombre IS NULL]. Usaría la condición para filtrar el resultado de la nueva tabla temporal.
El resultado de la consulta sería:
idPersona | Nombre       | Duración
       P2   NULL           NULL
       P3   NULL           NULL
       P4   NULL           NULL
       P7   NULL           NULL 


INNER JOIN(modo explícito)

Supongamos la siguiente situación, nos piden la información de qué alumnos están matriculados en alguna asignatura obligatoria, e información sobre dicha asignatura.

La Consulta que haríamos:

SELECT A.idPersona, B.Nombre, B.Duracion
  FROM ALUMNO A
       INNER JOIN
       ASIGNATOBL B
    ON A.idAsignatura = B.idAsinatura
   AND A.idCurso = B.idCurso
   AND A.idGrado = B.idGrado

Esto crea una nueva tabla resultante, que tendrá aquellos registros en los que las claves de ambas tablas coincidan. A diferencia del anterior caso los campos idPersona, Nombre y Duración vendrán informado a no NULOS.

El resultado de la consulta sería:
idPersona | Nombre       | Duración
       P1   ASIGOB1        S
       P1   ASIGOB2        S
       P4   ASIGOB2        S
       P5   ASIGOB1        S
       P6   ASIGOB2        S

Este sería un caso particular del LEFT OUTER JOIN. Podemos representar el INNER JOIN con la siguiente consulta creada con LEFT OUTER JOIN:

SELECT A.idPersona, B.Nombre, B.Duracion
  FROM ALUMNO A
       LEFT OUTER JOIN
       ASIGNATOBL B
    ON A.idAsignatura = B.idAsinatura
   AND A.idCurso = B.idCurso
   AND A.idGrado = B.idGrado
 WHERE B.Nombre IS NOT NULL


El resultado de la consulta sería:
idPersona | Nombre       | Duración
       P1   ASIGOB1        S
       P1   ASIGOB2        S
       P4   ASIGOB2        S
       P5   ASIGOB1        S
       P6   ASIGOB2        S


INNER JOIN(modo implícito)
Veamos la INNER JOIN anterior expresada de otra forma:

SELECT A.idPersona, B.Nombre, B.Duracion
  FROM ALUMNO A
      ,ASIGNATOBL B
 WHERE A.idAsignatura = B.idAsinatura
   AND A.idCurso = B.idCurso
   AND A.idGrado = B.idGrado

Es otra manera de expresar la inner join de dos tablas (tal vez la más conocida, la que primero que se estudia).

EL resultado es el mismo:
idPersona | Nombre       | Duración
       P1   ASIGOB1        S
       P1   ASIGOB2        S
       P4   ASIGOB2        S
       P5   ASIGOB1        S
       P6   ASIGOB2        S


RIGHT OUTER JOIN
Es un caso particular del LEFT OUTER JOIN. Mientras que en la LEFT, A contiene a B, en la RIGHT B contiene a A.

Veámoslo con un ejemplo:
Cuando explicábamos la LEFT OUTER JOIN, en el caso que proponíamos ("obtener la información de qué alumnos están matriculados en alguna asignatura obligatoria, e información sobre dicha asignatura"), llegamos a la siguiente consulta:

SELECT A.idPersona, B.Nombre, B.Duracion
  FROM ALUMNO A
       LEFT OUTER JOIN
       ASIGNATOBL B
    ON A.idAsignatura = B.idAsinatura
   AND A.idCurso = B.idCurso
   AND A.idGrado = B.idGrado

Esta consulta se puede expresar con la RIGHT OUTER JOIN obteniendo el mismo resultado. La consulta sería:

SELECT A.idPersona, B.Nombre, B.Duracion
  FROM ASIGNATOBL A
       RIGHT OUTER JOIN
       ALUMNO B
    ON A.idAsignatura = B.idAsinatura
   AND A.idCurso = B.idCurso
   AND A.idGrado = B.idGrado


FULL OUTER JOIN
Para este ultimo caso vamos a hacer un estudio entre dos alumnos. En este estudio nos piden información sobre qué asignaturas comparten, y cuales no.

Para esto primero seleccionamos las asignaturas en las que están matriculados cada uno de ellos. Haremos las siguientes consultas:

SELECT idAsinatura, B.Nombre, B.Duracion
  FROM ALUMNO
 WHERE A.idPersona = 'P5'

Análogamente hacemos la misma consulta para el segundo alumno:

SELECT idAsinatura, B.Nombre, B.Duracion
  FROM ALUMNO
 WHERE A.idPersona = 'P6'

Para extraer la información deseada hacemos una FULL OUTER JOIN entre ambas tablas temporales creadas. Nuestra consulta sería:

SELECT A.idAsignatura AS ASIGNP5, B.idAsignatura AS ASIGNP6
  FROM (
       SELECT idAsignatura
         FROM ALUMNO
        WHERE idPersona = 'P5') A
       FULL OUTER JOIN (     
       SELECT idAsignatura
         FROM ALUMNO
        WHERE idPersona = 'P6') B
    ON A.idAsignatura = B.idAsignatura   

NOTA: En esta consulta hemos introducido un nuevo concepto de SELECT anidadas, que explicaremos en un artículo posterior. Quedémonos con la idea de que estamos haciendo una FULL OUTER JOIN sobre dos tablas, tablas temporales que tienen su raiz en una SELECT.

Esta consulta creará una nueva tabla que tendrá tantos registros como claves dierentes existan entre las dos tablas usadas para crear el FULL OUTER JOIN. Así nuestra nueva tabla temporal, será la siguiente:

ASIGNP5 | ASIGNP6
   NULL      OP2
   OB1       NULL

Esta tabla sería la base para nuestro estudio pedido:

Si añadiésemos una sentencia WHERE [WHERE ASIGNP5 IS NOT NULL AND ASIGNP6 IS NOT NULL ] tendríamos la lista de las asignaturas en las que ambos están matriculados. En este caso la consulta sería vacía (no comparten las asiganturas en las que están matriculados).

NOTA: La sentencia WHERE tiene el mismo comportamiento que explicamos anteriormente con la LEFT OUTER JOIN.

Si añadiésemos una sentencia WHERE [WHERE ASIGNP5 IS NOT NULL] tendriamos la lista de las asignaturas en las que está matriculado el alumno P5 pero no el alumno P6. En este caso la consulta nos devolvería la asignatura OB1.

Si añadiésemos una sentencia WHERE [WHERE ASIGNP6 IS NOT NULL] tendriamos la lista de las asignaturas en las que está matriculado el alumno P6 pero no el alumno P5. En este caso la consulta nos devolvería la asignatura OP2.


Hasta aquí hemos hecho un breve repaso de los maneras más frecuentes que nos podemos encontrar a la hora de relacionar dos tablas en una consulta SQL. Espero que os sirva de ayuda y, si teneis alguna duda, no dudéis en preguntarla.