Usando una subconsulta en un JOIN

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:

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

Nuestra tabla MOVIMDET tiene estas filas:

 

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

Nuestra tabla de CLIENTES tiene estas filas:

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

Y el resultado que obtenemos es el siguiente:

Captura 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