[26ai] JOIN_TO_ME

If you prefer to read in Spanish Spanish version.

Today, let's talk about the new join syntax available in the last realease 26ai. It was introduced to simplify our joins avoiding common mistakes, writting every sql make it more legible and explicit, this kind of sql is a Row Widening Only Queries (RWOQs), ensuring every sql only add cols and not creating addicional rows.

This join is available in the last release 23.26.2.

SQL> Select banner from v$version;

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

Before getting our hands dirty with this small laboraty. Let's load the sample HR schema in our PDB. These scripts are the github of Oracle Sample Projects.

Here the link:

Github Oracle Sample Projects

The HR diagram is as follows:

Let's focus which are childs tables because Oracle will use it in order to resolve this join.

The kind of the join when we use it by default is LEFT OUTER JOIN.

Some of the benefits are as follows:

• Simplifies SQL in order to write complex querys.

• Keep filter explicit both level (where or inner).

• Resolve join uniqueness at runtime when need it.

Simple Query

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

Below, you can see the explain plan in order to compare when we use the new join:

Now, let's write the SQL with the new JOIN.

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

Below, you can see the explain plan:

Thus the explain plan are differents. The first sql resolved by NESTED LOOP but the new JOIN resolved by MERGE JOIN OUTER, How come? Because JOIN TO ONE resolves by LEFT OUTER JOIN.

Whether we use LEFT OUTER JOIN instead of INNER, the explain plan are equals:

Complex query

SQL> 
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 /

Let's write the SQL with the new JOIN:

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;

Let's look at this, both examples, left table would be the main while the child-table would be in the right. But, what happen whether the main table don't have FK about child-table?.

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/

Thus, LOCATIONS table don't have FK towards DEPARTMENTS table. The FK is defined in DEPARTMENTS table, not LOCATIONS table. That's why, our query failed.

Looking forward to seeing you in the next article :)