0

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

Employee Table

Department Table

 

Inner Joins

An inner join is used when you want to retrieve records/rows which are common in two tables.

INNER JOIN

 

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;

Inner Join Result

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.

LEFT JOIN

select 
a.employee_id, a.department_id, b.department_id 
from employee a 
left join 
department b 
on a.department_id = b.department_id;

Left Join Result

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.

RIGHT JOIN

select 
a.employee_id, a.department_id, b.department_id 
from employee a 
right join 
department b 
on a.department_id = b.department_id;

Right Join Result

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.

FULL OUTER JOIN

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;

Full Outer Join Result

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

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.