Home > SQL > SQL Joins explained in simple terms

SQL Joins explained in simple terms

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:
department_tableEmployee table with data:
employee_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 :
inner_join

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:

inner_join_first
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 :
left_outer_join

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:
left_outer_join_venn

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:

left_outer_join_reverse
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:

right_outer_join

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:
right_outer_join_venn

Thanks for reading.

Advertisements
Categories: SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: