The following are the most commonly used joins
- Inner Joins
- Right Outer Joins
- Left Outer Joins
- Full Outer Joins
Before going deeper into the topic
For example two table Employee & Department table. So based on this I will explain all the joins
Inner Joins
An inner join is used when you want to retrieve records/rows which are common in two tables.
Suppose if we to retrieve the corresponding department name as well then the below query needs to be executed.
select a.employee_id, a.employee_name, a.department_id, b. department_name from employee a inner join department b on a.department_id = b.department_id;
or
select a.employee_id, a.employee_name, a.department_id, b. department_name from employee a join department b on a.department_id = b.department_id;
or
select a.employee_id, a.employee_name, a.department_id, b. department_name from employee a, department b where a.department_id = b.department_id;
Here if you notice it has retrieved only two records of employee table because there is no department_id 300 in the department table. As well as Employee is marked to department_id 102 (finance)
Left Outer Join/ Left Join
An left join is used when you want to retrieve all records/rows from left table.
select a.employee_id, a.department_id, b.department_id from employee a left join department b on a.department_id = b.department_id;
Here if you notice department_id of department (right) table is null for the employee_id 1002 but has retrieved all records from the left table
Right Outer Join/ Right Join
An right join is used when you want to retrieve all records/rows from right table.
select a.employee_id, a.department_id, b.department_id from employee a right join department b on a.department_id = b.department_id;
Here if you notice 3rd row’s first two columns which are retrieved from left side table(employee) so its blank and all rows from department table (right table)
Full Outer Join
An full outer join is used when you want to retrieve all records/rows from both the tables.
select a.employee_id, a.department_id, b.department_id from employee a full outer join department b on a.department_id = b.department_id;
Here if you notice records from both the tables are retrieved also please note 2nd and 3rd columns has same records except for other records null is displayed in the result.