Premature optimization is the root of all evil in programming. C.A.R. Hoare
The employee table contains the following fields
Name Null Type -------------- -------- ------------ EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
desc HR.departments; Name Null Type --------------- -------- ------------ DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)
An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have equivalent values for the specified columns. The simple join or EQUI JOIN fetch the data when two tables have the sample set of data. When the data stores in multiple table, it provides the scalability, more efficient storage. The data should not be duplicated and can be stores in multiple tables.
The Natural Join shows the table from two or more table without any duplciate data.
A join which contains an operator other than equal to '=' in the joins condition.
Joining the table itself is called self join.
-- Inner Join or simple join select employee_id, first_name, last_name, email, department_name from HR.employees e join HR.departments d on e.DEPARTMENT_ID=d.DEPARTMENT_ID; -- Multiple joins select employee_id, first_name, last_name, email, department_name, job_title from HR.employees e join HR.departments d on e.DEPARTMENT_ID=d.DEPARTMENT_ID join HR.JOBS j on e.JOB_ID = j.JOB_ID; -- Natural Join SELECT * FROM HR.employees NATURAL JOIN HR.departments; -- NON-EQUI JOIN select * from HR.employees e, HR.departments d where e.DEPARTMENT_ID > d.DEPARTMENT_ID; -- SELF JOIN select e2.FIRST_NAME, e2.EMPLOYEE_ID, e2.MANAGER_ID from HR.employees e1,HR.employees e2 where e1.EMPLOYEE_ID=e2.MANAGER_ID;