To Using Natural Join In Oracle

Suppose we have 2 tables called EMPLOYEE & DEPARTMENT like this :

SQL> SELECT * FROM EMPLOYEE;

ID     NAME              SALARY HIRE      EMAIL                 DEPT_ID   MANAGER_ID
------ ------------- ---------- --------- --------------------- --------- ----------
1      TEDY            12500000 05-JAN-09 tedy@apt.co.id               6
2      BUDY             2750000 01-SEP-02 budy@apt.co.id               1           1
3      EDDY            49750000 09-MAY-03 eddy@apt.co.id               1           1
4      LILIS            5200000 02-JUN-01 lilis@apt.co.id              2           2
5      MANUS            1950000 29-OCT-10 manus@apt.co.id              3           2
6      TONY             2300000 02-NOV-03 tony@apt.co.id               4           1
7      YANNI            1500000 01-MAR-10 yanni@apt.co.id              3           2

7 rows selected.

SQL> SELECT * FROM DEPARTMENT;

DEPT_ID DEPARTMENT_NAME
------- --------------------
 1      Consulting
 2      Support
 3      Sales
 4      Finance
 5      Legal

SQL>

We can using natural join directly, like this :

SQL> SELECT NAME,DEPARTMENT_NAME
  2  FROM EMPLOYEE
  3  NATURAL JOIN DEPARTMENT;

NAME			  DEPARTMENT_NAME
----------------- --------------------
TEDY			  Legal
BUDY			  Consulting
EDDY			  Consulting
LILIS			  Support
MANUS			  Sales
TONY			  Finance
YANNI			  Sales

7 rows selected.

SQL>

Frequently, natural join involves primary and foreign key columns. To use natural join set set the dept_id column on department table as primary key & set the dept_id column on employee table as foreign key. To do that we use the following commands :

SQL> ALTER TABLE DEPARTMENT
  2     ADD CONSTRAINT PK_DEPARTMENT PRIMARY KEY (DEPT_ID);

Table altered.

SQL> ALTER TABLE EMPLOYEE
  2     ADD CONSTRAINT PK_DEPT FOREIGN KEY (DEPT_ID)
  3  	REFERENCES DEPARTMENT (DEPT_ID);

Table altered.

SQL>

After that we can using natural join statement like this :

SQL> SELECT NAME,DEPARTMENT_NAME
  2  	FROM EMPLOYEE
  3  	NATURAL JOIN DEPARTMENT;

NAME              DEPARTMENT_NAME
----------------- --------------------
TEDY              Legal
BUDY              Consulting
EDDY              Consulting
LILIS             Support
MANUS             Sales
TONY              Finance
YANNI             Sales

7 rows selected.

SQL>

Leave a Reply