SQL - script - subqueries

From Ibbddunq

Revision as of 13:38, 4 May 2009

Concepto

Básicamente es lo que ya saben de AR: puedo hacer jugar el resultado de una consulta en otra, combinando operaciones.

Muchas veces se hace innecesario por la sintaxis básica de la sentencia SELECT de SQL, que permite hacer muchos joins, una selección (que se optimiza, entonces es como si fueran varias) y una proyección todo en una sola sentencia, y además lo del nombre compuesto de atributo evita la necesidad de renombrado.

Por otro lado, aparecen casos en donde la técnica de meter una consulta adentro de otra es muy útil. Veamos algunos.

Subqueries en el FROM

Una fácil: UNION ordenado, p.ej. quiero las funciones donde aparecen osos o leones ordenadas por fecha.

 select *
 from
   (select diaFn, horaFn from formaParte where nomActo = 'osos'
    union
    select diaFn, horaFn from formaParte where nomActo = 'leones') ososOLeones
 order by diaFn, horaFn;

Si quiero usar funciones agrupadas sobre grupos, entonces mi tabla es la de grupos

 -- la duracion de la funcion mas larga
 select max(duracionFuncion)
 from
   (select diaFn, horaFn, sum(duracion) duracionFuncion
    from formaParte
    group by diaFn, horaFn) as duracionPorFuncion) as duracionPorFuncion;

Otro ejemplo: promedio de población de las ciudades más pobladas de cada provincia.

Subqueries en el WHERE

Tabla de 1x1 en comparaciones

Si quiero el cachet más alto que tiene un artista no necesito nada de subqueries

 select max(cachet) from artista;

... si quiero el (o los) artista/s de máximo cachet, ahí sí

 select nomArt, cachet
 from artista
 where cachet = (select max(cachet) from artista);

(o una opción con subquery en el FROM)

 select nomArt, cachet
 from artista
   natural join (select max(cachet) cachet from artista) as maxCachet;

Otra: quiero las ciudades de Córdoba con más población que cualquier ciudad de San Juan

 select nomCiudad
 from ciudad
 where provincia = 'Cordoba'
 and poblacion > (select max(poblacion) from ciudad where provincia = 'San Juan');

Otros ejemplos

• día y hora de las funciones que hayan tenido un acto de anillos que haya durado más que cualquier acto de leones.
• (combinando las dos cosas que vimos hasta ahora)
  día, hora y duración total de las funciones que hayan durado más que cualquier función del año 2007 (sirve para motivar vistas).

IN / NOT IN

Con estos simulamos intersecciones y uniones, en algunos casos sencillos pero que cubren un montón.

Vamos con una: ciudades en las que se hicieron funciones en 2008 y en 2009. Haciendo un join moñito sale

 select distinct f2008.nomCiudad
 from funcion f2008
     join funcion f2009 on f2008.nomCiudad = f2009.nomCiudad
 where year(f2008.diaFn) = 2008 and year(f2009.diaFn) = 2009;

pero también sale con

1. una intersección: ciudades de funciones 2008 inters. ciudades de funciones 2009; que también se puede expresar como
2. las ciudades 2008 que estén en el conjunto de ciudades 2009.

De la forma 1 no se puede decir en SQL, de la forma 2 sí.

 select distinct nomCiudad
 from funcion
 where year(diaFn) = 2008
 and nomCiudad in (select nomCiudad from funcion where year(diaFn) = 2009);

Análogo con la resta, lo vamos a decir así:

• las ciudades 2008 que no estén en el conjunto de ciudades 2009

¿cómo? bien fácil, en vez de IN, se pone NOT IN

 select distinct nomCiudad
 from funcion
 where year(diaFn) = 2008
 and nomCiudad not in (select nomCiudad from funcion where year(diaFn) = 2009);

Uno parecido: las ciudades donde no se haya hecho ninguna función

 select * 
 from ciudad 
 where nomCiudad not in
   (select nomCiudad from funcion);

pero ... ¡ojo! si nomCiudad pudiera ser null hay que hacer la salvedad, porque respecto de las comparaciones null se porta de una forma muy loca

 select * 
 from ciudad 
 where nomCiudad not in
   (select nomCiudad from funcion where nomCiudad is not null);

digo: si hacen comparaciones y no les da lo que esperan, sepan que tal vez el problema es que está comparando con null, excluyen el caso null y listo.

Otros ejemplos: provincias por las que no va a pasar el circo en 2009, días en que se hicieron funciones pero no actos publicitarios.

NOT EXISTS

Ahora queremos las funciones en las que haya habido leones pero no clowns. ¿Cómo hacemos? ... con un NOT IN no podemos, porque fecha y hora son dos campos separados.

Destacar la aparición de queries correlacionados.

Para pensar

Algunos queries que pueden iluminar

• Artistas que viven en trailers de exactamente 4 ocupantes.
• Idem: actos de las funciones con exactamente cuatro actos.
• Día y hora de la función más larga.
• Días que se hizo función y en el día siguiente no - acá ver ADDDATE.
• Días en que se se hizo función y en los 10 días siguientes no.
• Para cada función: día/hora/acto que la cierra.

Resumen - a revisar

Repaso de situaciones donde puede ser útil

• en el FROM
  • UNION ordenado
  • JOIN con UNION
  • funciones de agrupación sobre grupos (la función más larga)
  • cuando quiero hacer un join con una tabla agrupada
• tabla de 1x1 en comparaciones en el WHERE
• IN con tabla de 1 atributo
• NOT EXISTS vs NOT IN, queries correlacionados