Una de las complicaciones al aplicar un outer join es determinar en qué lugar debemos colocar las condiciones de filtrado para ni perder tuplas que deberían aparecer en el resultado, ni hacer que en éste aparezcan tuplas de más.
Condiciones where / on en inner join
Cuando presentamos el concepto de join (de tipo inner join, aunque entonces no lo llamáramos así) dijimos que la condición de relación la podíamos poner tanto en el on del join como en el where del select. Y que, aunque no era recomendable, la condición de filtrado la podíamos poner en el where o en el on del join. Para aumentar la legibilidad de las consultas, os aconsejamos poner en el on las condiciones de relación (en las que participan campos de ambas tablas); y dejar para el where las condiciones en las que se usase solo un campo de alguna de las tablas.
Un ejemplo. Supón esta BD con datos sobre pilotos de Fórmula 1 y Grandes Premios (los datos no serán reales).
Queremos un listado de Grandes Premios celebrados en Europa, junto con sus respectivos ganadores. Para ello escribimos esta consulta SQL, que respeta las recomendaciones que hemos apuntado anteriormente: la condición de relación estará, obviamente, en el on, y la condición de filtrado (continente='Europa') en el where:
select GP.nombre, P.nombre, escuderia
from GranPremio GP
join Piloto P on GP.ganador = P.id
where continente='Europa';Pero, también podríamos haber escrito, obteniendo el mismo resultado, la consulta de esta otra manera (con la condición del where escrita en el on). Es una consulta más dificil de entender, con un join un poco raro, pero que devuelve lo que tiene que devolver:
select GP.nombre, P.nombre, escuderia
from GranPremio GP
join Piloto P on GP.ganador = P.id
and continente='Europa';
Condiciones where / on en outer join
Sin embargo, cuando se trata de outer joins, ya no es lo mismo escribir las condiciones de filtrado en el on del join o en el where. Incluso, para complicarlo un poco más, hay algunas condiciones de filtrado que se deben poner en el where, y otras en el on. Hay que tener cuidado, porque, depende de dónde las escribamos, podemos hacer que aparezcan, o desaparezcan, tuplas del resultado.
Recordemos dos cosas importantes antes de poner un ejemplo y explicar por qué ocurren las cosas:
- Nunca son ciertas las condiciones que comparan valores nulos con otros valores, por lo que una tupla que tenga un valor nulo en un campo que intervenga en una condición del
wherenunca aparecerá en el resultado - El
outer jointiene dos fases: (1) se hace uninner joinque empareja las tuplas que cumplen la condición de relación; y (2) se añaden al resultado las tuplas de la tabla hacia la que se ha inclinado eljoiny que no han cumplido la condición de relación (se añaden al resultado con valores nulos en los campos que corresponden a la tabla del otro lado)
Estos dos aspectos son importantes porque, una condición de más, incluida en el on de un outer join, puede hacer que aparezcan tuplas extra añadidas en el resultado en el segundo paso del outer join (por no haber cumplido la condición del join); o una condición incluida de más en el where puede hacer que desaparezcan tuplas que habían sido añadidas previamente en el segundo paso de un outer join (si la condición se refiere a un campo de la tabla hacia la que NO se inclina el outer join).
Veamos un ejemplo, sobre la misma BD de antes. Queremos listar a todos los pilotos de la escudería Ferrari, junto con los Grandes Premios que hayan ganado en Europa (y si no han ganado ninguno, que salgan también). Hay que fijarse en que hay dos condiciones de filtrado: (1) la escudería de los pilotos debe ser Ferrari, y (2) los Grandes Premios se deben haber celebrado en Europa.
Por clarificar un poco más el ejemplo, vamos a suponer que tenemos los siguientes datos en las tablas (puedes ver el ejemplo completo aquí)
Piloto +----+-----------------+--------------+ | id | nombre | escuderia | +----+-----------------+--------------+ | 8 | Max Verstappen | Red Bull | | 11 | Fernando Alonso | Aston Martin | | 15 | Carlos Sainz | Ferrari | | 16 | Charles Leclerc | Ferrari | +----+-----------------+--------------+
GranPremio +----+----------------+------------+---------+ | id | nombre | continente | ganador | +----+----------------+------------+---------+ | 3 | Australia | Oceanía | 15 | | 6 | Emilia Romagna | Europa | 8 | | 7 | Mónaco | Europa | 8 | | 8 | España | Europa | 15 | | 11 | Gran Bretaña | Europa | 8 | | 13 | Bélgica | Europa | 15 | +----+----------------+------------+---------+
Hacemos notar que, de los pilotos de Ferrari, el piloto 16 no ha ganado ningún Gran Premio, y que el piloto 15 ha ganado Grandes Premios tanto en Europa como en otros continentes. Es decir, el resultado esperable de la consulta sería el siguiente (la tercera tupla es añadida en el segundo paso del left join):
+-----------------+---------+------------+ | nombre | nombre | continente | +-----------------+---------+------------+ | Carlos Sainz | España | Europa | | Carlos Sainz | Bélgica | Europa | | Charles Leclerc | NULL | NULL | +-----------------+---------+------------+
Si no tuviésemos cuidado, podríamos escribir una consulta como la siguiente, que cumple las reglas de los inner join, es decir, la condición de relación en el on y las de filtrado en el where:
select P.nombre, GP.nombre
from Piloto P
left join GranPremio GP on P.id = GP.ganador
where escuderia='Ferrari' and GP.continente='Europa';
Pero, como ves en el siguiente listado, esta query es errónea. Excluye del resultado las tuplas que habían sido incluidas en el segundo paso del left join (la de Charles Leclerc)
+-----------------+---------+------------+ | nombre | nombre | continente | +-----------------+---------+------------+ | Carlos Sainz | España | Europa | | Carlos Sainz | Bélgica | Europa | +-----------------+---------+------------+
La tupla de Charles Leclerc debería aparecer, pero no lo hace porque al evaluar la condición GP.continente='Europa', el continente de la tupla de Leclerc es NULL, y NULL='Europa' no es cierto.
Probemos entonces a poner ambas condiciones en el on del left join:
select P.nombre, GP.nombre, continente
from Piloto P
left join GranPremio GP on P.id = GP.ganador
and GP.continente='Europa'
and escuderia='Ferrari';Que resulta en:
+------------------+---------+------------+ | nombre | nombre | continente | +------------------+---------+------------+ | Max Verstappen | NULL | NULL | | Fernando Alonso | NULL | NULL | | Carlos Sainz | España | Europa | | Carlos Sainz | Bélgica | Europa | | Charles Leclerc | NULL | NULL | +------------------+---------+------------+
En este caso, hemos añadido al resultado muchas más tuplas de las que deberían estar, con pilotos de otras escuderías que no son Ferrari. Darse cuenta de que en el primer paso del outer join, se añaden al resultado las dos tuplas de Carlos Sainz en grandes premios europeos (cumple ambas condiciones de filtrado). Y, en el segundo paso del left join, se añaden tuplas que no cumplen la condición del on: (a) tuplas de pilotos que solo incumplen la segunda condición de filtrado (otros pilotos de Ferrari pero que no han ganado en grandes premios europeos); y (b) tuplas de pilotos que cumplen ambas condiciones de filtrado (no son de Ferrari y no han ganado en grandes premios europeos). Notar que la tupla Carlos Sainz y su victoria en un gran premio no europeo no es añadida, porque este piloto ya fue añadido en el primer paso del left join.
Entonces, ¿cuál es la solución correcta?
La que pone en el where la condición que refiere al campo de la tabla hacia la que SI está inclinado el join (para poder comprobar la condición y no quitar tuplas que deberíamos dejar); y deja en el on del outer join la condición que refiere al campo de la tabla hacia la que NO está inclinado el join (para incluir las tuplas que no la cumplen).
select P.nombre, GP.nombre, continente
from Piloto P
left join GranPremio GP on P.id = GP.ganador
and GP.continente='Europa'
where escuderia='Ferrari';
+-----------------+---------+------------+ | nombre | nombre | continente | +-----------------+---------+------------+ | Carlos Sainz | España | Europa | | Carlos Sainz | Bélgica | Europa | | Charles Leclerc | NULL | NULL | +-----------------+---------+------------+
Es decir, gracias al join se incluyen las tuplas de los pilotos que han ganado grandes premios europeos; gracias al left join se incluyen pilotos que no han ganado premios europeos (pero con valores nulos en los campos correspondientes a los premios), para así tener todos los pilotos en el listado; y gracias al where nos quedamos finalmente con los pilotos de Ferrari.
No hay comentarios:
Publicar un comentario