NATURAL JOIN Explained

A NATURAL JOIN, allows for tables to be joined on common column names
without explicitly naming the columns.


For example, the following two tables; share  the STATISTIC# columns
SQL> desc v$sesstat;
 Name                  
 --------------------
 SID                   
 STATISTIC#            
 VALUE                 

SQL>
SQL> desc v$statname
 Name                  
 --------------------
 STATISTIC#            
 NAME                  
 CLASS                 
 STAT_ID               


So when the following SQL is run, the tables are joined on STATISTIC#;

select max(value)
from v$sesstat natural join v$statname
where name = 'session cursor cache count';



The same applies to the following statement;

select Amount
      ,Session_Cached_Cursors*30+30 Cached_Cursors_Rounded
from
    (
     select trunc(value/30)  as Session_Cached_Cursors
           ,count(*)         as Amount
     from  
            v$sesstat natural join v$statname
     where  name = 'session cursor cache count'
     group by trunc(value/30) order by 1
    )
/