Explain the join? Different types of joins
Inner Join or simple join
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.
Natural Join
The Natural Join shows the table from two or more table without any duplciate data.
NON-EQUI JOIN
A join which contains an operator other than equal to ‘=’ in the joins condition.
SELF JOIN
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;