Lección 15 - Reunión interna y externa (INNER / OUTER JOIN)

Curso SQL para principiantes


En la lección anterior se trató la operación de reunión entre tablas que guardan una relación. Existe una sintaxis más concreta para realizar la operación de reunión, donde la cláusula WHERE se usa únicamente para filtrar registros y no para reunir registros.

Reunión interna - cláusulas inner join / on
Esta cláusula está diseñada precisamente para reunir registros de varias tablas, en ella intervienen las claves primarias y foráneas, y no intervienen, o lo hacen en la cláusula WHERE, los filtros propiamente dichos. Veamos una de las consultas que se expuso en la lección anterior usando esta sintaxis.

Consulta que realiza la reunión entre los profesores y los cursos que imparte cada uno usando INNER JOIN / ON:

Código: Seleccionar todo
select *
  from CURSOS C inner join PROFESORES P 
    on C
.ID_PROFE = P.ID_PROFE 



Si antes se dijo que el SGBD realiza el producto cartesiano entre dos tablas y posteriormente mediante la cláusula WHERE ignora aquellos registros que carecen de sentido y muestra los que guardan una relación, ahora podemos verlo del siguiente modo: el SGBD recorrerá la tabla hijo(CURSOS) y para cada uno asociará el registro de la tabla padre(PROFESORES) que satisface la cláusula ON. Para asociar el profesor no es necesario realizar, para cada curso, un recorrido secuencial sobre la tabla PROFESORES hasta encontrarlo, puesto que en la cláusula ON estamos indicando su clave primaria, por lo que el motor SQL usuará el índice que la clave lleva implicito para localizar un profesor de forma mucho más eficiente. Igual que haría usted para localizar un capítulo concreto de un libro, usando el índice.

Algunos puristas afirman que este es el modo correcto de construir las consultas, porque el motor SQL trabaja de un modo más eficiente, a otros sin embargo les resulta incomodo o simplemente menos atractivo. Lo ideal sería que todo esto fuese transparente al desarrollador. El motor SQL debe interpretar la consulta y devolver el resultado de la forma más eficiente, obviamente realizando productos cartesianos y posteriormente filtrando los registros no es un método eficiente, pero esto es algo que consideramos en este curso como herramienta de aprendizaje o método de comprensión, y que en realidad los SGBD no hacen a no ser que no les quede más remedio, ya sea porque se lo pidamos explícitamente omitiendo la cláusula WHERE, no se hayan establecido las relaciones, o sean poco "inteligentes". Dicho de otro modo, yo espero de un SGBD que sea eficaz no solo al ejecutar la consulta, sino al interpretarla y al elaborar un plan de ejecución adecuado y eficaz, y poder usar la sintaxis que más cómoda me resulte, sin tener que pensar si la consulta es costosa o no para el motor SQL. Desafortunadamente no siempre se podrá pasar esto por alto, en ocasiones se deberá optimizar la consulta para ayudar al SGBD a ser más eficiente. En cualquier caso la optimización queda fuera del alcance de este curso. Por ahora basta con que usted sepa que es importante crear las claves primarias y foráneas debidamente, tanto por una cuestión de eficiencia como de integridad referencial.

Veamos otro ejemplo de la lección anterior usando esta cláusula, concretamente del apartado de ejercicios, donde se pedía los cursos en que se ha matriculado el alumno con identificador 1:
Código: Seleccionar todo
select C.TITULO CURSO
  from ALUMNOS_CURSOS AC inner join CURSOS C
    on AC
.ID_CURSO = C.ID_CURSO
 where AC
.ID_ALUMNO = 1



Observe como en la cláusula WHERE se establece un filtro propiamente dicho, y en la cláusula ON se establece la condición de reunión que el motor debe aplicar entre las tablas a ambos lados de la cláusula INNER JOIN.

Veamos un último ejemplo de reunión interna en la que aparezcan tres tablas, para ello tomemos otro ejemplo de la lección anterior, la reunión de los alumnos con los cursos que cursa cada uno. Tomando ejemplos equivalentes construidos únicamente con la cláusula WHERE se pueden observar mejor las diferencias.

Código: Seleccionar todo
  select C.TITULO CURSO , concat(A.APELLIDOS,', ',A.NOMBRE ) ALUMNO
    from ALUMNOS_CURSOS AC inner join ALUMNOS A
      on AC
.ID_ALUMNO = A.ID_ALUMNO inner join CURSOS C
      on AC
.ID_CURSO  = C.ID_CURSO
order by C
.TITULO , A.NOMBRE , A.APELLIDOS



Si ahora sobre este consulta se quisiera reducir el resultado a un curso o un alumno en concreto, se añadiría la pertinente cláusula WHERE con el filtro deseado justo antes de la cláusula ORDER BY.

* * *

Reunión externa - left outer join / right outer join

La reunión externa puede verse en este caso como una reunión interna donde no es necesario que el registro hijo tenga informada la clave foránea para ser mostrado, por ejemplo, cuando se mostraban los cursos junto a los profesores que los imparten, como uno de los cursos no tiene padre, es decir, no tiene un profesor asignado, o lo que es lo mismo, el campo ID_PROFE de la tabla CURSOS está a nulo, este curso no se muestra dado que no satisface la cláusula ON. Bien, este recurso nos ofrece la posibilidad de mostrar estos registros con los campos del registro padre a nulo.

La reunión externa siempre se realizaran por la izquierda o por la derecha, una de las dos. De este modo expresamos el deseo de considerar todos los registros de la tabla a la izquierda o a la derecha de la cláusula OUTER JOIN, aunque no se hallen coincidencias con la otra tabla según la cláusula ON. Veamos la consulta que muestra los cursos y sus profesores aunque el curso no tenga profesor asignado:

Código: Seleccionar todo
select *
  from CURSOS C left outer join PROFESORES P 
    on C
.ID_PROFE = P.ID_PROFE



Como en este caso usamos LEFT OUTER JOIN, la tabla que de la izquierda, es decir, la tabla CURSOS, será considerada por completo aunque no tenga éxito la cláusula ON, en cuyo caso los campos de la tabla situada a la derecha de la cláusula se mostrarán a nulo.

Si invertimos el orden de las tablas y usamos RIGHT OUTER JOIN, o simplemente RIGHT JOIN, expresión equivalente simplificada aplicable también a LEFT JOIN, el resultado es el mismo.

Código: Seleccionar todo
select *
  from PROFESORES P right join CURSOS C
    on C
.ID_PROFE = P.ID_PROFE



En la consulta anterior se están considerando todos los cursos aunque estos no tengan un profesor definido, si ahora usted quisiera obtener esto mismo pero añadiendo un filtro sobre la tabla PROFESORES, por ejemplo que el apellido del profesor contenga una "E", cabe esperar hacerlo en la cláusula WHERE, sin embargo también es posible aplicar el filtro en la cláusula ON. En realidad elegiremos una u otra cláusula en función de lo que deseemos obtener. Si lo hacemos en la clausula ON de un OUTER JOIN se estarán obteniendo todos los cursos con los campos de la tabla PROFESORES a nulo si la condicion establecida en la cláusula ON no tiene éxito. Si se hace en la cláusula WHERE se estará forzando a que se cumpla dicha cláusula y por tanto la reunión externa se rompe. Veamos esto con un ejemplo:

Consulta que muestra todos los cursos acompañados del profesor que lo imparte. Si el curso no tiene profesro definido o bien el campo APELLIDOS no contiene una "E", los campos de la tabla PROFESORES se mostrarán a nulo:
Código: Seleccionar todo
select *
  from PROFESORES P right join CURSOS C
    on C
.ID_PROFE = P.ID_PROFE
   and P
.APELLIDOS like '%E%' 



El resultado presenta para el curso 3 los campos de la tabla PROFESORES a nulo porque el campo APELLIDOS del profesor que lo imparte no contiene un "E". Para el curso 5 ocurre lo mismo pero en este caso el motivo es además que no tiene profesor definido, con que mucho menos podrá ser cierta la otra condición.

Ahora aplicamos el filtro del apellido en la cláusula WHERE:

Código: Seleccionar todo
select *
  from PROFESORES P right join CURSOS C
    on C
.ID_PROFE = P.ID_PROFE
 where P
.APELLIDOS like '%E%' 



Observamos como la reunión externa se rompe puesto que la cláusula WHERE exige que el apellido del profesor contenga una "E", dado que los cursos que no tienen profesor definido la consulta devuelve el apellido a nulo, esta cláusula no se satisface por lo que oculta el registro y la reunión externa carece de sentido, o si usted quiere, la clausula WHERE es aplicable a la tabla CURSOS pero no a la tabla PROFESORES, puesto que en este caso no tiene sentido realizar una reunión externa para que luego un filtro en la cláusula WHERE la anule.

* * *

Vamos ahora a ver los recuentos sobre reuniones externas, por ejemplo los alumnos que hay matriculados en cada curso. Esta consulta se presentó en la lección anterior, en el apartado de ejercicios, sin embargo los cursos sin alumnos matriculados eran ignorados en lugar de aparecer con un cero como sería de esperar. Esto es debido a que no satisfacen la clausula ON de una reunión interna, por lo que se debe usar la reunión externa para este propósito, pero cuidado, ahora no nos sirve el recuento de registros, puesto que pueden venir cursos sin alumnos, o lo que es lo mismo, cursos con los datos del alumno a nulo, de modo que si contamos registros los datos no serán verídicos, deben contarse alumnos. Revise si lo cree conveniente la lección 10 donde se trataron las particularidades del valor NULO.

Alumnos matriculados en cada curso, aunque estos sean cero:

Código: Seleccionar todo
  select C.TITULO CURSO, count(AC.ID_ALUMNO) ALUMNOS, count(1) REGISTROS
    from ALUMNOS_CURSOS AC right join CURSOS C
     on AC
.ID_CURSO = C.ID_CURSO
group by C
.TITULO



En la anterior consulta se han contado tanto alumnos como registros para poder observar la diferencia. La única fila en que estos dos valores difieren es para el curso de SQL avanzado. Dado que la reunión externa devuelve la fila con los datos del alumno a nulo para los cursos sin alumnos, al realizar un recuento de registros el valor es uno, el registro existe, pero al realizar el recuento del campo ID_ALUMNO este es ignorado por la función COUNT por ser nulo.

Observe que en este caso la tabla que interesa tratar por completo mostrando todos sus registros es la tabla padre(CURSOS), y la tabla donde no importa que halla aciertos es la tabla hijos(ALUMNOS_CURSOS). Es decir, la consulta devuelve todos los registros de la tabla CURSOS aunque para ellos no existan hijos en la tabla ALUMNOS_CURSOS. En los ejemplos anteriores a este último, también interesaba tratar por completo la tabla CURSOS, pero esta ejercía de hijo y no de padre, y los campos de la tabla PROFESORES podían venir a nulo no porque no existiera el registro en la tabla PROFESORES que también, sino como consecuencia de que el campo ID_PROFE de la tabla CURSOS contenía un valor nulo.

* * *

Por último comentar que la reunión externa no es posible hacerla usando únicamente la cláusula WHERE, debemos forzosamente usar la cláusula OUTER JOIN. Esto es así en MySQL, sin embargo en Oracle, que originalmente solo se podían construir consultas con las sintaxis basada en cláusula WHERE, si es posible realizar reuniones externas con esta sintaxis, para ello indicamos el símbolo"(+)" tras los campos de la clausula WHERE pertenecientes a la tabla que devolverá los campos a nulo en el caso de no cumplirse la condición, por ejemplo la consulta anterior en Oracle se construiría de la siguiente manera.

Consulta Oracle:

Código: Seleccionar todo
  select C.TITULO CURSO, count(AC.ID_ALUMNO) ALUMNOS, count(1) REGISTROS
    from ALUMNOS_CURSOS AC 
,CURSOS C
   where AC
.ID_CURSO(+) = C.ID_CURSO
group by C
.TITULO


Con ello estamos indicando el SGBD Oracle que aunque no encuentre el registro en la tabla ALUMNOS_CURSOS, devuelva el registro de la tabla CURSOS con los datos de la tabla ALUMNOS_CURSOS a nulo, es decir, se esta realizando una reunión externa. Por ejemplo, en la consulta en la que se devolvía todos los cursos con el profesor que imparte cada curso y ademas incluíamos el filtro de que el apellido del profesor tuviese una "E" se construiría de la siguiente manera en Oracle:

Consulta Oracle:

Código: Seleccionar todo
select *
  from PROFESORES P , CURSOS C
 where C
.ID_PROFE = P.ID_PROFE(+)
   and P.APELLIDOS(+) like '%E%' 


En general en una reunión externa debemos tratar siempre la tabla cuyos campos pueden venir a nulo, si es en Oracle, aunque actualmente es posible usar la sintaxis OUTER JOIN, pondremos el símbolo "(+)" a cada campo de dicha tabla que aparezca en la cláusula WHERE. Si usamos OUTER JOIN, pondremos todos los campos que establecen condiciones de dicha tabla en la cláusula ON, si lo hacemos en la cláusula WHERE como filtro corriente la reunión externa se rompe y carece de sentido.

* * *

Resumen
La reunión interna permite reunir registros de tablas relacionadas ignorando los registros que no satisfacen la condición de reunión especificada en la cláusula WHERE o bien en la cláusula ON en el caso de usar la sintaxis INNER JOIN.

La reunión externa permite reunir registros de tablas relacionadas considerando todos los registros de una primera tablas aunque ninguno de los registros de una segunda tabla presente aciertos contra la primera, obviamente en ese caso los campos de esta última tabla vendrán a nulo.

Existen dos sintaxis para realizar las operaciones de reunión ya sea externa o interna, dependiendo del SGBD. Basada en cláusula WHERE o bien basada en cláusula INNER JOIN / OUTER JOIN. Lo ideal sería dejar a criterio del desarrollador el uso de cualquiera de ellas siempre y cuando el SGBD lo soporte.

En una reunión externa debemos tratar siempre la tabla cuyos campos pueden venir a nulo poniendo todos los campos que establecen condiciones de dicha tabla en la cláusula ON del OUTER JOIN, si lo hacemos en la cláusula WHERE como filtro corriente la reunión externa se rompe y carece de sentido.

* * *

Ejercicio 1
Construya una consula que resuelva el número de cursos que imparte cada profesor usando la cláusula INNER JOIN.

Ejercicio 2
Realice una consulta entre las tablas CURSOS, ALUMNOS y ALUMNOS_CURSOS de modo que aparezcan los alumnos matriculados en cada curso pero mostrando todos los cursos aunque no tengan alumnos matriculados.




    Lección 14 - Relaciones, claves primarias y foráneas
  • Lección 16 - El modelo entidad-relación

Creative Commons License Creative Commons License

Este curso está sujeto a la licencia Reconocimiento-NoComercial-SinObraDerivada 3.0 de Creative Commons. Usted puede copiarla, distribuirla y comunicarla públicamente siempre que especifique su autor y deletesql.com; no la utilice para fines comerciales; y no haga con ella obra derivada. Puede usted consultar la licencia completa aquí.




Volver a Curso SQL desde cero

¿Quién está conectado?

Usuarios navegando por este Foro: No hay usuarios registrados visitando el Foro y 5 invitados

cron