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>