[26ai] JOIN_TO_ME

El artículo también se encuentra publicado en Ingles English version.

En el artículo de hoy vamos a ver la nueva sintaxis introducida para simplificar aún más los joins evitando errores comunes, haciendo cada sql más legible y explicita, este tipo de consultas son Row Widening Only Queries (RWOQs) garantizando que cada join solo añade columnas y no crea filas adicionales.

Este nuevo tipo de join esta disponible desde la última versión que ha lanzado Oracle, 23.26.2.

SQL> Select banner from v$version;

BANNER
--------------------------------------------------------------
Oracle AI Database 26ai Enterprise 
 Edition Release 23.26.2.0.0 - Production

Antes de meternos en faena con este laboratorio, además de estar con última versión de Oracle vamos a cargar en nuestra PDB el schema HR. Los scripts de este schema se encuentra en el github de Oracle Sample Projects.

Dejo aquí el enlace:

Github Oracle Sample Projects

Este el modelo que hemos cargado:

Fijémonos cual son las tablas principales y cual son las tablas hijas ya que Oracle va usar las propias FK para resolver este join.

El tipo de join que aplica por defecto es LEFT OUTER JOIN.

Algunos beneficios son:

• Simplicidad SQL para complejas consultas.

• Mantiene filtros explícitos a nivel de where o inner.

• Confirma en tiempo de ejecución la unicidad de los join en tiempo de ejecución.

Sin más, veamos algún ejemplo:

Consulta simple

SQL> 
select FIRST_NAME, LAST_NAME, EMAIL, JOB_TITLE  
  from hr.employees aa 
 inner join hr.jobs bb 
    on aa.job_id = bb.job_id
 fetch first 10 rows only

Veamos también el explain plan, para compararlo con el nuevo join:

Ahora realizamos la misma consulta pero usando la nueva sintaxis.

SQL>
select FIRST_NAME, LAST_NAME, EMAIL, JOB_TITLE  
  from hr.employees JOIN TO ONE ( hr.jobs )
 fetch first 10 rows only

El explain plan:

Pues los planes son diferentes, en el primer caso resuelve por NESTED LOOP pero el nuevo join resuelve por MERGE JOIN OUTER ¿motivo? Pues porque el nuevo JOIN resuelve por LEFT OUTER JOIN.

Si ejecutamos la misma query, pero por LEFT OUTER JOIN en vez de INNER, los planes son idénticos:

Consulta compleja

SQL> r
Select STREET_ADDRESS, 
       POSTAL_CODE, 
       CITY,  
       COUNTRY_NAME, 
       REGION_NAME
  from locations aa
 inner join countries cc
    on aa.country_id  = cc.country_id
 inner join regions dd
    on cc.region_id   = dd.region_id
fetch first 10 rows only
/

Ahora realizamos la misma consulta pero usando la nueva sintaxis.

SQL> 
Select STREET_ADDRESS, POSTAL_CODE, CITY,  COUNTRY_NAME, REGION_NAME
FROM
    locations aa
    JOIN TO ONE
        (INNER join countries cc
            on aa.country_id  = cc.country_id
         inner join regions dd
            on cc.region_id   = dd.region_id
)
fetch first 10 rows only

Si nos fijamos, en ambos ejemplos, siempre la tabla de la izquierda es la que hace de tabla principal, y la derecha la hija. Pero ¿Qué pasaría si la tabla principal no tiene FK directa con la tabla hija?.

SQL> 
    Select STREET_ADDRESS, POSTAL_CODE, CITY
          from locations JOIN TO ONE (departments)
      fetch first 10 rows only
      from locations JOIN TO ONE (departments)
                                  *
ERROR at line 2:
ORA-18641: No join key found for "DEPARTMENTS"
Help: https://docs.oracle.com/error-help/db/ora-18641/

Si nos fijamos en el modelo, la tabla LOCATIONS no tiene FK hacía DEPARTMENTS. La FK está definida en DEPARTMENTS, no en LOCATIONS.

Espero que os guste. ¡Nos vemos en el próximo artículo!