# [26ai] JOIN_TO_ME

*If you prefer to read in Spanish* [***Spanish version***](https://dbaenlasombra.com/26ai-join-to-me)*.*

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
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](https://github.com/oracle-samples/db-sample-schemas)

The HR diagram is as follows:

![](https://cdn.hashnode.com/uploads/covers/65605419d28f19cc44df7ef1/f4ddc664-d84c-4964-9211-a3bfc7dcd04a.png align="center")

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
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
```

![](https://cdn.hashnode.com/uploads/covers/65605419d28f19cc44df7ef1/fcf60c4f-42b6-4b00-a86e-921dc5036222.png align="center")

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

![](https://cdn.hashnode.com/uploads/covers/65605419d28f19cc44df7ef1/97c82fec-9ede-4214-852e-607f20781068.png align="center")

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

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

```

![](https://cdn.hashnode.com/uploads/covers/65605419d28f19cc44df7ef1/9aca33e4-f5f7-4db1-959e-f4bbb02c9a59.png align="center")

Below, you can see the explain plan:

![](https://cdn.hashnode.com/uploads/covers/65605419d28f19cc44df7ef1/901ac589-75a2-4df0-bf76-24731fd2a2d1.png align="center")

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:

![](https://cdn.hashnode.com/uploads/covers/65605419d28f19cc44df7ef1/1018291a-bb7c-45d8-8314-23abea992a4c.png align="center")

### Complex query

```sql
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 /
```

![](https://cdn.hashnode.com/uploads/covers/65605419d28f19cc44df7ef1/39df7264-be40-4131-b846-725e25eca738.png align="center")

Let's write the SQL with the new JOIN:

```sql
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;
```

![](https://cdn.hashnode.com/uploads/covers/65605419d28f19cc44df7ef1/9837cf1f-44a8-4223-8679-a2ab343eae42.png align="center")

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
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 :)