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.
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;