java2practice

Joins are used to join two or more tables in SQL based on the common data.
In this post I have explained below joins

1. Inner Join
2. Left outer join
3. Right outer join

Example:  Let’s say we have an Employee table and Department table. Where every employee is associated with Department, new employees many not have Department.
And Every Department will have employees, for new Department, employees may not be there.

Department Table with data:
Employee table with data:

Here department_id is the foreign key to Department table, Department 3 doesn’t have any employees associated with it.  And Employees 4,5 are not associated with any Department.

Now will perform different type of joins and examine the results:

1. Inner Join: Intersection of two tables is called inner join, Only the matched data will be returned from the two tables

Query: select e.*, d.* from employee e inner join department d on e.department_id = d.id
Resulted data :

The employees who doesn’t have any department and Department which doesn’t have employees won’t be returned.
In Venn diagram it looks like this:

2. Left outer join: returns all the data from the left table and only matched data from the right table.

Query: select e.*, d.* from employee e left outer join department d on e.department_id = d.id
Resulted data :

Returns all the 5 records from the Employee table and only 3 records from the Department table.
In the place of Department details for the employees 4,5( who doesn’t have department associated ) gives NULL values:
Venn diagram looks like this:

If we do left outer join from Department to Employee the result would be:
Query:  select  d.*, e.* from  department d left outer join employee e on  d.id = e.department_id

Resulted Data:

Returns all the data from Department table and in the place of employee details for Department 3 gives NULL values:

3. Right Outer Join:

Returns all the data from right side table and only matched data from the left side table.
Query: select e.*, d.* from employee e right outer join department d on e.department_id = d.id

Resulted data:

Returned all records from Department table and NULL values in the place Department 3 as no employees associated with it.
Venn diagram looks like this:

Thanks for reading.