Dudas de SQL

En el ejercio 3 de la sesión 5, nombre de cada cliente de la provincia de Castellón junto con su dirección y número de facturas que tienen el año pasado, al hacer la consulta sólo aparecen los clientes que tienen alguna factura. ¿Cómo podría hacer para que también mostrara los que no tienen ninguna y que les asignara 0 facturas?

A ver, si no salen los que no tienen facturas el año pasado, habrás hecho una consulta como esta:

¿Por qué no salen los que no tienen facturas? Pues porque al hacer el join entre clientes y facturas, los clientes que no tienen ninguna factura se pierden, ya que el join concatena las filas de las dos tablas que en codcli tienen los mismos valores.

Bien, es posible que haya clientes que sí tengan facturas pero que no sean del año pasado ¿por qué tampoco salen estos? Pues porque al hacer la restricción sobre la fecha, las facturas que no son del año pasado se pierden y, con ellas, su cliente.

¿Cuál es la solución? En principio podemos pensar en hacer un join externo. El join externo de clientes y facturas concatenará a los clientes sin facturas con la factura "fantasma". Lo que tenemos que hacer es añadir la factura fantasma a la tabla de facturas poniéndole el (+) al lado. El único cambio que hay que hacer es este:

WHERE f.codcli (+) = c.codcli AND c.codpue = p.codpue

Sin embargo, si probamos la consulta, vemos que sigue obteniendo el mismo resultado: los clientes sin facturas no salen. ¿Y por qué? Veamos: después de hacer el join
externo y el resto de los join, los clientes sin facturas están concatenados con la factura fantasma. Esa factura fantasma es una fila con nulos en todas las columnas. Una vez hechos los join, tenemos una restricción:

to_number(to_char(f.fecha,'yyyy')) = to_number(to_char(sysdate,'yyyy'))-1

Esta restricción se hace sobre la fecha de la factura, que en los clientes sin facturas es un nulo. Esta restricción compara el año pasado con un nulo ¿son iguales? No. Por lo tanto los clientes que estaban acompañados de la factura fantasma se vuelven a perder.

Así que mucho ojo, porque si hacemos un join externo, hay que tener precaución de que no haya ninguna restricción sobre las columas donde se añade el fantasma, porque entonces es como si no hubiéramos hecho nada.

Una solución podría ser la de convertir las fechas "fantasma" (las nulas) en una fecha del año pasado. Por ejemplo si a la fecha de hoy le quitamos 12 meses tenemos una fecha del año pasado: add_months(sysdate,-12). El cambio sería el siguiente (lo pongo en mayúsculas):

to_number(to_char(NVL(F.FECHA,ADD_MONTHS(SYSDATE,-12)),'yyyy')) = to_number(to_char(sysdate,'yyyy'))-1

Pero si probamos la consulta, vemos que sigue sin salir el resultado deseado. ¿Por qué? Bueno, pues porque con este "truco" pescamos a los clientes que NUNCA han comprado nada (y casualmente todos los de Castellón han comprado algo). PERO se nos siguen escapando los clientes que sí han comprado alguna vez, pero no durante el año pasado.

Podríamos jugar un poco con la función DECODE para pescar a estos clientes, pero una solución más fácil y, seguramente, más rápida en ejecución (el join externo suele ser caro) es utilizar la unión.

A la primera consulta que hemos escrito vamos a unirle los clientes de Castellón que no tienen facturas del año pasado (y aquí se incluyen los que nunca han comprado y los que lo han hecho alguna vez, pero no el año pasado). Estos clientes los sacamos así:

SELECT c.nombre cliente, c.direccion, p.nombre pueblo
FROM clientes c, pueblos p, provincias pr
WHERE c.codpue = p.codpue
AND p.codpro = pr.codpro AND pr.nombre = 'CASTELLON'
AND c.codcli NOT IN (SELECT f.codcli
FROM facturas f
WHERE to_number(to_char(f.fecha,'yyyy')) = to_number(to_char(sysdate,'yyyy'))-1);

Pero si intentamos hacer la unión con la primera consulta que hemos escrito nos sale un error: la primera consulta obtiene como resultado 4 columnas mientras que la segunda obtiene sólo 3 columnas. ¿Solución? Añadir esa cuarta columna, que es la que indica el número de facturas que tiene el cliente. ¿Cuántas facturas tienen estos clientes que hemos pescado ahora? ¡Cero! Bueno, pues ponemos un cero y tan contentos. Lo que debe funcionar finalmente es esto:

SELECT c.nombre cliente, c.direccion, p.nombre pueblo, count(*) facturas
FROM facturas f, clientes c, pueblos p, provincias pr
WHERE f.codcli = c.codcli AND c.codpue = p.codpue
AND p.codpro = pr.codpro AND pr.nombre = 'CASTELLON'
AND to_number(to_char(f.fecha,'yyyy')) = to_number(to_char(sysdate,'yyyy'))-1
GROUP BY c.codcli, c.nombre, c.direccion, p.nombre
UNION
SELECT c.nombre cliente, c.direccion, p.nombre pueblo, 0 facturas
FROM clientes c, pueblos p, provincias pr
WHERE c.codpue = p.codpue
AND p.codpro = pr.codpro AND pr.nombre = 'CASTELLON'
AND c.codcli NOT IN (SELECT f.codcli
FROM facturas f
WHERE to_number(to_char(f.fecha,'yyyy')) = to_number(to_char(sysdate,'yyyy'))-1);

Siguiendo con el mismo tema, en el examen del 25/1/2002 la pregunta 12 ¿sería asi?

SELECT   C.nomequipo, count (*) maillots
FROM     LLEVA L, CICLISTA C
WHERE    L.dorsal = C.dorsal
GROUP BY C.nomequipo
UNION
SELECT   nomequipo, 0 maillots
FROM     CICLISTA
WHERE    nomequipo NOT IN (SELECT nomequipo
                            FROM   CICLISTA C, LLEVA L
                            WHERE C.dorsal = L.dorsal);

¡Sí! ¿Te atreves con el join externo?

SELECT   C.nomequipo, count (L.dorsal) maillots
FROM     LLEVA L, CICLISTA C
WHERE    L.dorsal (+) = C.dorsal
GROUP BY C.nomequipo;

Si en un equipo ningún ciclista ha llevado maillot, el equipo sobrevive al join porque cada uno de sus ciclistas se concatena con la fila "fantasma" de LLEVA. El count(L.dorsal) cuenta 0 para los equipos cuyos ciclistas no han llevado ningún maillot porque para esos ciclistas tenemos que L.dorsal es nulo.