Oracle SQL - Table Join Natural Joins

Introduction

You can use the NATURAL JOIN operator in the FROM clause as shown in the following code.

select ename, beginyear, msal, deptno
from   emp
       natural join
       history;

The behavior of the NATURAL JOIN operator:

The NATURAL JOIN operator determines which columns the two tables ( EMPLOYEES and HISTORY) have in common. In this case, these are the three columns EMPNO, MSAL, and DEPTNO.
It joins the two tables using an equijoin over all columns they have in common.
It suppresses the duplicate columns resulting from the join operation in the previous step.
The NATURAL JOIN operator evaluates the remaining query clauses.

Demo

SQL>
SQL>-- from w  w  w .  ja v a 2 s.co m
SQL> drop table emp;

Table dropped.

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> drop table history;

Table dropped.

SQL> alter session  set NLS_DATE_FORMAT='DD-MM-YYYY';

Session altered.

SQL> create table history(
  2  empno      NUMBER(4)  ,
  3  beginyear  NUMBER(4)  ,
  4  begindate  DATE       ,
  5  enddate    DATE       ,
  6  deptno     NUMBER(2)  ,
  7  msal       NUMBER(6,2),
  8  comments   VARCHAR2(60)) ;
SQL>
SQL> insert into history values (7001,2000,'01-01-2000','01-02-2000',40, 950,'');
SQL> insert into history values (7003,2000,'01-02-2000', NULL       ,20, 800,'');
SQL> insert into history values (7002,1988,'01-06-1988','01-07-1989',30,1000,'');
SQL> insert into history values (7005,1989,'01-07-1989','01-12-1993',30,1300,'');
SQL>
SQL> select ename, beginyear, msal, deptno
  2  from   emp
  3         natural join
  4         history;

no rows selected

SQL>