Poniendo los JOIN en el orden correcto

1 comentario

Muchas veces cuando escribimos un SELECT que no es muy sencillo necesitamos relacionar a una tabla con otra u otras tablas. Eso lo hacemos con las cláusulas JOIN.

Como sabemos, hay dos clases de JOIN:

  • INNER JOIN
  • OUTER JOIN

La palabra INNER puede omitirse si se desea, ya que es la que por defecto asumirá el Firebird si no se escribe algo antes de la palabra JOIN. Los OUTER a su vez pueden ser: LEFT, RIGHT, FULL

Entonces, la pregunta es: ¿importa si se escribe primero un INNER o un OUTER? ¿O es lo mismo?

Consulta 1. Poniendo primero el INNER JOIN

SELECT
   *
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiCondición1
LEFT JOIN
   MiTabla3
      ON MiCondición2

Aquí se escribió primero el INNER JOIN (la palabra INNER no es obligatorio escribir) y luego el OUTER JOIN (la palabra OUTER tampoco es obligatorio escribir, al escribir LEFT ya el Firebird sabe que se trata de un OUTER)

Consulta 2. Poniendo primero el OUTER JOIN

SELECT
   *
FROM
   MiTabla1
LEFT JOIN
   MiTabla3
      ON MiCondición2
JOIN
   MiTabla2
      ON MiCondición1

Aquí se escribió primero el OUTER JOIN (en este caso, fue un LEFT) y luego el INNER JOIN (la palabra INNER es opcional, no se requiere escribirla)

¿Cuál consulta es mejor?

¿La Consulta 1 es más eficiente que la Consulta 2? ¿La Consulta 2 es más eficiente que la Consulta 1? ¿O son igual de eficientes?

El optimizador del Firebird solamente reordena las tablas en el PLAN hasta que encuentra el primer OUTER JOIN (sea LEFT, RIGHT, o FULL).

Por lo tanto, la correcta es la Consulta 1.

Conclusión:

El orden de los JOIN sí importa en Firebird porque el optimizador reordena las tablas para obtener un mejor PLAN solamente hasta que encuentra el primer OUTER JOIN.

¿Consejo?

Revisa todos tus SELECTs y todas tus vistas para asegurarte de que siempre los INNER JOIN se encuentren antes de los OUTER JOIN, conseguirás consultas más eficientes de esa manera.

Artículos relacionados:

INNER JOIN y OUTER JOIN

Ejemplo Nº 001 – Usando INNER JOIN

Ejemplo Nº 002 – Usando INNER JOIN eficientemente

Ejemplo Nº 003 – Escribiendo varios INNER JOIN

Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla

Ejemplo Nº 006 – Usando LEFT JOIN e INNER JOIN

JOIN implícito y JOIN explícito

NATURAL JOIN y CROSS JOIN

Relacionando dos tablas: la forma vieja y la forma nueva

Teoría de Conjuntos: Unión, Intersección, Diferencia

Entendiendo las tablas autoreferenciadas

Autoreferenciar una tabla. Algunos ejemplos

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Usando una tabla autoreferenciada para averiguar números consecutivos faltantes

Deja un comentario

Hay varios métodos para conocer si una serie de números está en secuencia. Es decir si están como 1, 2, 3, 4, 5, 6, 7, 8, etc. o si falta algún número.

Uno de esos métodos es mediante el uso de una tabla autoreferenciada, como en el siguiente ejemplo:

SELECT
   P1.PRD_IDENTI
FROM
   PRODUCTOS P1
LEFT JOIN
   PRODUCTOS P2
      ON P1.PRD_IDENTI = P2.PRD_IDENTI - 1
WHERE
   P2.PRD_IDENTI IS NULL

Como puedes ver, la tabla PRODUCTOS le hace un LEFT JOIN a la tabla … PRODUCTOS. Por eso se dice que la tabla se referencia a sí misma.

Entonces, para saber si los valores de la columna PRD_IDENTI son consecutivos o no, la referencia se hace al valor de PRD_IDENTI menos uno.

Si hay números faltantes entonces la condición no se cumplirá. ¿Y cómo podemos saber que la condición no se cumplió? porque en ese caso el valor de PRD_IDENTI de la tabla relacionada será NULL.

Si nuestra tabla de PRODUCTOS tiene estos datos:

AUTOREF1

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

Entonces el SELECT anterior nos mostrará esto:

AUTOREF2

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

 ¿Qué significa? Que después del 25 no está el 26; ni después del 35 está el 36. ¿Y por qué aparece el 38? Porque es el valor que tiene PRD_IDENTI en la última fila y evidentemente como está en la última fila no habrá un valor mayor que él. Entonces siempre el valor que se encuentra en la última fila aparecerá, ese es un pequeño detalle a tener en cuenta.

Desventajas de este método

Este método funcionará siempre pero tiene una gran desventaja: está usando un LEFT JOIN y eso hará que la cantidad de filas leídas sea muy grande (no olvides que un JOIN es la multiplicación de las filas de la tabla de la izquierda por las filas de la tabla de la derecha). En tablas grandes este método puede ser increíblemente lento, pero para tablas pequeñas puede ser útil.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

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

Relacionando dos tablas: la forma vieja y la forma nueva

2 comentarios

En Firebird, cuando queremos relacionar dos tablas (o conjuntos de resultados) entre sí tenemos dos posibilidades:

  1. Usando la forma vieja
  2. Usando la forma nueva

Caso 1. Usando la forma vieja

Esta sintaxis fue establecida en el año 1989. Las tablas se listan separadas por comas después de la cláusula FROM y la condición que las relaciona se pone en la cláusula WHERE. No hay una sintaxis especial que distinga cuales de las condiciones del WHERE son para filtrar filas y cuales son para relacionar tablas, se supone que mirando la sentencia el desarrollador sabrá cual es cual.

¿Problemas?

  • La cláusula WHERE puede volverse muy larga y muy complicada de leer porque se la usa para relacionar tablas y también para filtrar filas
  • Solamente se pueden relacionar tablas que tengan valores idénticos, si una de las tablas tiene NULL en una columna no se podrá hacer la relación. En otras palabras: solamente se puede hacer un INNER JOIN, no se pueden hacer OUTER JOIN.

Sintaxis:

SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla1,
   MiTabla2
WHERE
   MiCondición

Caso 2. Usando la forma nueva

Esta sintaxis fue establecida en el año 1992. La tabla principal se coloca después de la cláusula FROM y la tabla secundaria se coloca después de la cláusula JOIN, la condición que las relaciona se coloca después de ON. Por lo tanto se puede distinguir fácilmente cual es la condición usada para relacionar a la tablas y cual es la condición usada para filtrar filas, no hay confusión posible.

¿Ventajas?

  • Es muy fácil saber cual es la condición usada para relacionar a las dos tablas
  • Es muy fácil saber cual es la condición usada para filtrar filas
  • La cláusula WHERE es más corta y por lo tanto más fácil de leer que cuando se usa la forma vieja
  • Se puede usar tanto INNER JOIN como OUTER JOIN

Sintaxis:

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

Cuidado:

Tú puedes elegir cualquiera de las dos formas para relacionar tablas pero NUNCA debes mezclarlas. En un SELECT o usas la forma vieja o usas la forma nueva, no las mezcles, porque si las mezclas eso solamente te ocasionará problemas y ningún beneficio.

Recomendación:

La forma nueva es mejor, por eso se la inventó, por eso existe. Si ya tienes SELECTs escritos con la forma vieja y funcionan bien entonces déjalos como están, no los toques, pero para escribir todos tus nuevos SELECTs usa la forma nueva porque es la que verás cada vez más en todos los libros y documentos sobre SQL. Y es además la que siempre se usa en este blog.

Artículos relacionados:

Entendiendo a los JOIN

JOIN implícito y JOIN explícito

El índice del blog Firebird21

 

¿Importa dónde se ponen las condiciones de un JOIN?

Deja un comentario

Cuando escribes una consulta que tiene JOIN y que también tiene WHERE tienes la posibilidad de poner las condiciones tanto en el JOIN como en el WHERE, o sea donde prefieras.

¿Eso le importa al optimizador de consultas del Firebird?

Es decir, ¿te conviene poner las condiciones en el JOIN, en el WHERE, o es indiferente?

La buena noticia es que es indiferente, no importa donde pongas la condición porque el optimizador elegirá la que devuelva los resultados más rápido. Un optimizador sería muy malo si para hacer bien su tarea dependiera del lugar donde pones las condiciones. Yel optimizador de Firebird es muy bueno.

Entonces, no te preocupes donde pones la condición, puedes ponerla en el JOIN o en el WHERE, que el optimizador de Firebird siempre elegirá la mejor alternativa.

Artículo relacionado:

El índice del blog Firebird21

 

Optimizando los JOIN

9 comentarios

En muchas de nuestras consultas tenemos que relacionar dos o más tablas usando la cláusula JOIN. En general Firebird recupera muy rápido los datos, pero podemos conseguir aún una mayor velocidad con la técnica mostrada a continuación.

Un JOIN normal sería algo como:

SELECT
   *
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiRelación_entre_MiTabla1_y_MiTabla2
WHERE
   MiCondición

Funciona muy bien y obtendremos lo que queremos pero podemos aumentar la velocidad de respuesta si a esa misma consulta la escribimos así:

SELECT
   *
FROM
   (SELECT
       MiColumna1,
       MiColumna2,
       MiColumna3
    FROM
       MiTabla1
    WHERE
       MiCondición
   ) AS MiFiltro
JOIN
   MiTabla2
      ON MiRelación_entre_MiFiltro_y_MiTabla2

¿Qué hicimos aquí?

  • Primero, filtramos los datos de la tabla principal para obtener solamente las filas que nos interesan. O sea que construimos lo que se llama una tabla derivada
  • Segundo, relacionamos el filtro de la tabla principal (o sea, la tabla derivada) con la tabla secundaria

 En un JOIN normal primero se relacionan las dos tablas y luego se filtra el conjunto resultado. En el JOIN optimizado en cambio primero se filtra la tabla principal y luego se relacionan las dos tablas. Eso en general (pueden existir excepciones) nos permitirá obtener más rápidamente el conjunto resultado.

Conclusión:

Muchas veces podemos obtener más rápidamente el conjunto resultado si usamos una tabla derivada para filtrar las filas de la tabla principal antes de relacionarla con la tabla secundaria. Si necesitas mayor velocidad en un SELECT que tiene JOIN y que tiene WHERE, prueba esta técnica y verifica el tiempo de respuesta.

Artículos relacionados:

Tablas derivadas

Entendiendo subconsultas y tablas derivadas

El índice del blog Firebird21

El foro del blog Firebird21

Hallar los movimientos ocurridos entre dos fechas (otro método)

5 comentarios

En este artículo ya habíamos visto un método para hallar todos los movimientos (compras, ventas, cobranzas, pagos, etc.) que ocurrieron entre dos fechas dadas, y si en una fecha no hubo movimientos entonces mostrarla igual pero con un total de cero.

https://firebird21.wordpress.com/2014/04/29/hallando-todas-las-ventas-entre-dos-fechas-dadas/

Ahora, veremos otro método con el cual podremos obtener los mismos resultados, gracias a la colaboración de Claudio Martín.

Aquí, lo que haremos será crear un stored procedure seleccionable que nos devolverá todas las fechas de un rango dado, y luego cuando las necesitemos mediante un LEFT JOIN o un RIGHT JOIN las obtendremos.

Este método tiene la ventaja de que el stored procedure seleccionable que escribimos es uno solo y lo podemos utilizar en multitud de ocasiones diferentes, todas las veces que necesitemos un rango de fechas.


CREATE PROCEDURE RANGO_FECHAS(
      tdFecIni DATE,
      tdFecFin DATE)
   RETURNS(
      ftdFecha DATE)
AS
   DECLARE VARIABLE ldFecha DATE;
BEGIN

   ldFecha = tdFecIni;

   WHILE (ldFecha <= tdFecFin) DO BEGIN
      ftdFecha = ldFecha;
      SUSPEND;
      ldFecha = ldFecha + 1;
   END

END;

Entonces, usando la misma tabla que en el artículo anterior, escribiríamos:

SELECT
   R.ftdFecha,
   SUM(COALESCE(M.MVC_TOTALX, 0)) AS TOTAL_VENTAS_DIA
FROM
   RANGO_FECHAS('01/01/2014', '01/07/2014') R
LEFT JOIN
   MOVIMCAB M
      ON R.ftdFecha = M.MVC_FECHAX
GROUP BY
   R.ftdFecha

Y obtendríamos este resultado:

VENTAS1

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

El cual, como puedes verificar, es exactamente igual al obtenido con el otro método. Pero la ventaja de este nuevo método es que el stored procedure seleccionable podemos usarlo siempre que necesitemos obtener todas las fechas de un rango dado.

 Conclusión:

Una de las muchas cosas buenas que tiene el Firebird es que nos permite obtener los mismos resultados usando métodos diferentes, queda a nuestro criterio elegir el que nos parece más conveniente para cada situación particular.

En este caso, podemos tener un stored procedure que cuando las necesitemos nos devolverá todas las fechas de un rango, para ello simplemente lo juntamos mediante LEFT JOIN o RIGHT JOIN a la otra tabla y listo, ya está.

Artículos relacionados:

Hallando todas las ventas entre dos fechas dadas

Entendiendo a los Stored Procedures

El índice del blog Firebird21

 

Older Entries