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á?
- Que la subconsulta (o sea, el segundo SELECT ) se encuentra rodeada por paréntesis
- Que la subconsulta tiene un alias (que se encuentra después del AS y puede ser cualquiera, “MiAlias” es sólo un ejemplo)
- 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:
- La fecha de la venta
- El número de la Factura de venta
- El nombre del cliente
- La cantidad de productos distintos que le vendimos
- 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:
Nov 20, 2014 @ 19:37:18
buenos dias walter
si ponemos la subconsula antes del FROM tambien funciona ?
Ejemplo
SELECT Nombre_Cliente,
(SELECT SUM(Total) FROM FACTURAS WHERE
CLIENTES.NumeroCli = FACTURAS.NumeroCli ) AS SumaTotal
FROM
CLIENTES
Gracias y que tengas un buen dia.
Nov 20, 2014 @ 20:42:05
Sí, como en la mayoría de los casos hay varias consultas distintas que nos permiten llegar al mismo resultado final.
Saludos.
Walter.
Nov 20, 2014 @ 22:01:35
gracias walter, en firebird si funciona en Foxpro da el error de
invalid use of subquery,
sabes por que ?
saludos
Nov 20, 2014 @ 22:16:18
Sí, el motivo es que no todos los motores están obligados a tener esa característica, por eso te encontrarás con algunos que sí la tienen y algunos que no la tienen. Firebird es de los que sí la tienen.
Saludos.
Walter.
Nov 20, 2014 @ 22:30:12
ah entiendo, me estaba matando tratando de encontrar el error
firebird es mas amigable.
gracias walter.
Jun 25, 2015 @ 15:06:35
Alguien sabe si los alias se pueden utilizar para hacer otras operaciones en el mismo select?. es decir, algo como esto: SELECT A.articulonombre,A.precioventa, B.impuesto, (A.Costo + B.Impuesto) as CostoNeto, (CostoNeto – B.PreciodeVenta) as Utilidad, (Utilidad/B.PreciodeVenta) as PorcentajeUtilidad
FROM DocumentosVentas A
Join Costos B on B.Articulo_id=A.articulo_id
Me refiero a reutilizar una columna calculada, para crear otra columna calculada, apartir de la primera. ESto lo comento por que no me permite reutilizar una columna de este tipo. tengo que calcularla de nuevo si deseo usarla.
Jun 25, 2015 @ 19:45:21
No, por el momento no existe esa posibilidad (tengo entendido que Firebird 3 sí la tendrá, así que habrá que esperar unos meses más).
Lo que normalmente se hace en casos así es crear tablas CTE.
Puedes leer más sobre las tablas CTE en este artículo:
https://firebird21.wordpress.com/2013/11/17/usando-cte-common-table-expression/
Si tienes cualquier duda sobre las tablas CTE o algún otro tema relacionado con Firebird lo más conveniente siempre es que hagas tu pregunta en el foro de este blog, porque allí más gente la verá y podrá responderte. El foro encontrarás en:
http://firebird21.yoforeo.com/
Saludos.
Walter