Creando una subconsulta que devuelva muchas columnas

6 comentarios

A veces necesitamos tener en un SELECT varias columnas que provienen de la misma subconsulta. Hay dos formas de conseguir algo así: la forma mala y la forma buena. He visto que muchas personas usan la “forma mala”, así que ahora explicaré la “forma buena”.

Veamos el caso:

Listado 1. La “forma mala”

SELECT
   MiColumna1,
   MiColumna2,
   (SELECT MiSubColumna1 FROM MiTabla2 JOIN MiEnlace WHERE MiCondición) AS MiColumna3,
   (SELECT MiSubColumna2 FROM MiTabla2 JOIN MiEnlace WHERE MiCondición) AS MiColumna4,
   (SELECT MiSubColumna3 FROM MiTabla2 JOIN MiEnlace WHERE MiCondición) AS MiColumna5
FROM
   MiTabla1

Si te fijas en las subconsultas del Listado 1. verás que son muy, muy, similares, la única diferencia es la columna que devuelven a la consulta principal, el resto es idéntico.

¿Cuál es el defecto de hacerlo así?

Que la subconsulta se ejecuta muchas veces (3 veces en el Listado 1., pero en otros casos podría ser más) y eso puede llegar a ser muy lento en tablas grandes.

Además, si la consulta es muy larga, deberás escribir mucho y te complicará la lectura.

Así que veamos una forma alternativa (y mejor) de obtener el mismo resultado:

Listado 2. La “forma buena”

WITH MiSubConsulta AS (
   SELECT
      MiSubColumna1,
      MiSubColumna2,
      MiSubColumna3
   FROM
      MiTabla2
   JOIN
      MiEnlace
   WHERE
      MiCondición
)

SELECT
   T1.MiColumna1,
   T1.MiColumna2,
   T2.MiSubColumna1 AS MiColumna3,
   T2.MiSubColumna2 AS MiColumna4,
   T2.MiSubColumna3 AS MiColumna5
FROM
   MiTabla1      T1
LEFT JOIN
   MiSubConsulta T2
      ON T1.MiColumna1 = T2.MiSubColumna1

¿Cuáles son las ventajas de hacerlo así?

  1. La tabla virtual CTE (es virtual porque solamente existe en la memoria de la computadora) es creada una sola vez y sus columnas pueden ser usadas muchísimas veces. En este ejemplo sus columnas (llamadas MiSubColumna1, MiSubColumna2, y MiSubColumna3) fueron usadas 3 veces pero en otros casos podrían usarse muchas más veces. Como se la crea una sola vez eso es mucho más rápido que crearla 3 veces que era el caso en el Listado 1.
  2. Es muy fácil de entender. Si miramos a la subconsulta muy fácilmente entenderemos lo que hace.

¿Qué fue lo que hicimos?

  1. Creamos una tabla virtual llamada “MiSubConsulta” (el nombre puede ser cualquiera, “MiSubConsulta” es solamente un ejemplo)
  2. En la tabla virtual colocamos todas las columnas que nos interesan
  3. En el SELECT principal hicimos un LEFT JOIN a la tabla virtual y por eso ya pudimos acceder a todas sus columnas. Si la condición de enlace no se cumple entonces tendremos NULL en las columnas que provienen de la tabla virtual.

Observación: La condición de enlace del Listado 2. (T1.MiColumna1 = T2.MiSubColumna1) es solamente un ejemplo, allí tú pondrás la condición de enlace adecuada a tu caso.

Conclusión:

Si necesitamos tener varias subconsultas en la lista de columnas de un SELECT y esas subconsultas son muy similares lo mejor es crear una tabla CTE (o sea, una tabla virtual) y así conseguiremos una mejor velocidad de respuesta y también que nuestro código sea más fácil de entender.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Usando una subconsulta en un JOIN

7 comentarios

Cuando escribimos un SELECT a veces necesitamos usar un JOIN para relacionar a un conjunto de resultados con otro conjunto de resultados. En general tiene esta forma:

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiCondición

Lo que mucha gente no sabe es que en el JOIN no es imprescindible que especifiquemos una tabla, también podemos especificar un SELECT si lo necesitamos. Y nos quedaría de esta forma:

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
JOIN
   (SELECT
       MiColumna4,
       MiColumna5,
       MiColumna6
    FROM
       MiTabla2
    WHERE
       MiCondición1) AS MiAlias
   ON MiCondición2

¿Qué podemos notar acá?

  1. Que la subconsulta (o sea, el segundo SELECT ) se encuentra rodeada por paréntesis
  2. Que la subconsulta tiene un alias (que se encuentra después del AS y puede ser cualquiera, “MiAlias” es sólo un ejemplo)
  3. Que en “MiCondición2” debemos usar ese alias

Ejemplo 1:

SELECT
   M.MVC_FECHAX,
   M.MVC_TIPDOC,
   M.MVC_NRODOC,
   M.MVC_IDECLI,
   MJ.CLI_NOMBRE
FROM
   MOVIMCAB M
JOIN
   (SELECT
       CLI_IDENTI,
       CLI_NOMBRE
    FROM
       CLIENTES) AS MJ
   ON M.MVC_IDECLI = MJ.CLI_IDENTI

Este ejemplo es muy sencillo, y solamente sirve para ilustrar el concepto. No es necesario usar una subconsulta para algo tan simple pero está ahí para que se entienda como funciona.

Como puedes ver, la subconsulta tiene una alias (en este caso ese alias se llama MJ) y es ese alias el que usamos en la condición que hace la relación (o sea, lo que escribimos después del ON).

Ejemplo 2:

Ahora queremos saber los siguientes datos para cada venta realizada:

  1. La fecha de la venta
  2. El número de la Factura de venta
  3. El nombre del cliente
  4. La cantidad de productos distintos que le vendimos
  5. El total de la Factura
SELECT
   C.MVC_FECHAX,
   C.MVC_NRODOC,
   L.CLI_NOMBRE,
   D.CANTIDAD,
   D.TOTALVENTA
FROM
   MOVIMCAB C
JOIN
   (SELECT
       MOV_IDECAB,
       COUNT(*) AS CANTIDAD,
       SUM(MOV_CANTID * MOV_PRECIO) AS TOTALVENTA
    FROM
       MOVIMDET
    GROUP BY
       MOV_IDECAB) AS D
   ON C.MVC_IDENTI = D.MOV_IDECAB
JOIN
   CLIENTES L
      ON C.MVC_IDECLI = L.CLI_IDENTI

Como puedes ver, ahora la subconsulta es más compleja pero también mucho más útil: ya nos da la información que estábamos necesitando.

Nuestra tabla MOVIMCAB tiene estas filas:

JOIN1

Captura 1. Si haces clic en la imagen la verás más grande

Nuestra tabla MOVIMDET tiene estas filas:

JOIN2

 

Captura 2. Si haces clic en la imagen la verás más grande

Nuestra tabla de CLIENTES tiene estas filas:

JOIN3

Captura 3. Si haces clic en la imagen la verás más grande

Y el resultado que obtenemos es el siguiente:

JOIN4Captura 4. Si haces clic en la imagen la verás más grande

Entonces aquí tenemos lo que estábamos buscando.

Desde luego que no es la única forma de resolver este problema pero este ejemplo sirve para ilustrar el concepto de que podemos tener subconsultas en un JOIN y que eso puede sernos muy útil en algunas ocasiones. En otras palabras, tenemos una técnica más para utilizar con los SELECTs.

Conclusión:

Podemos tener subconsultas en los JOINs y eso es algo muy bueno porque nos abre un buen abanico de posibilidades. Lo que debemos recordar es que la subconsulta debe estar rodeada por paréntesis y que debemos asignarle un alias. Solamente eso, el resto es igual a los demás JOINs que ya conocemos.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Usando subconsultas en expresiones

1 comentario

Antes de la versión 2.5 del Firebird no se podían usar subconsultas en expresiones aunque dichas subconsultas retornaran un solo valor. Esto nos obligaba a usar la construcción SELECT … INTO

Ahora, estas asignaciones ya son posibles dentro de un stored procedure, un trigger o un execute block:

MiVar = (SELECT … FROM …);

IF ((SELECT … FROM …) = 1) THEN …

IF(25 = ANY(SELECT … FROM …)) THEN …

IF(2104 IN (SELECT … FROM …)) THEN …

Por supuesto que en los dos primeros ejemplos el SELECT debe retornar una sola fila.

lcNomCli = (SELECT CLI_NOMBRE FROM CLIENTES WHERE CLI_IDENTI = 11111);     /* Nombre del cliente */

lnPreVta = (SELECT PRD_PREVTA FROM PRODUCTOS WHERE PRD_IDENTI = 12345);     /* Precio de venta del producto */

Fíjate que debes rodear al SELECT con paréntesis o recibirás un mensaje de error.