SQL Joins

Reading Time: 4 minutes

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&amp;nbsp;a.department_id = b.department_id;

select 
a.employee_id, a.employee_name, a.department_id, b. department_name 
from employee a 
join 
department b 
on&amp;nbsp;a.department_id = b.department_id;

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.

Leave a Reply Cancel reply