Archive June 2018

Explaining Consepts of Joins in MySQL DataBase

Explaining Consepts of Joins in MySQL DataBase

As name implies, “Joins” are used for fetching data by joining two or more tables and written using SELECT statements. This only relates to relational database systems such as MySQL, PostgreSQL, Firebird, SQLite, SQL Server and Oracle. Joining two or more tables is important because, in most practical scenarios, we require to grab information which is not stored in one table.
In this post, We are going to understand the concepts and basics  of  “Joins” using MySQL database system. “Joins” are basically four types.

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • OUTER JOIN (FULL JOIN)

Before we explore join methods one by one, lets consider the following scenario. We have two entities “Employees” and “Projects”. Employees are assigned to zero or more projects. Wise versa Project has zero or more employees assigned. In this moment, Our database reflects the above scenario as follows.

Please note that this example is taken only for explaining joins. Employee and Project tables  relates with many to many relationship which is not a best practice used designing a database architecture .We can simplify the relationship as one to many by adding intermediate table like “employee_project” having columns “emp_id” and “project_id”.

Employee Table

emp_id emp_name project_id
1 Sanjaya NULL
2 Ian 5
3 Udara 1
4 Marlon 5
5 Supun 2
6 Tharindu 3
5 Danushka 3
5 Dinusha 4
5 Kalum NULL

Project Table

project_id project_name
1 WordPress Event Plugin
2 SEO for Company site
3 Online ticket booking system
4 Mobile app for online ticket booking system
5 Designing Reservation System
6 Envato WordPress Theme development

If we take a look at above two tables, they make relationship in between using project_id of a Employee table and the project_id of a Project table. This is called the foreign key relationship between two tables.

 

INNER JOIN

This produces a record set which matches in both the Employee and Projects tables.In other words, all employees who are assigned to a project.

SELECT employee.emp_name, project.project_name
FROM employee
INNER JOIN project on employee.project_id= project.project_id;
ORDER BY employee.emp_id ASC

Above MySQL code section do the inner join of our two tables. We filter the two columns employee name from Employee table and  project name from Project table. We will get the following data set as a result.

emp_name project_name
Ian Designing Reservation System
Udara WordPress Event Plugin
Marlon Designing Reservation System
Supun SEO for Company site
Tharindu Online ticket booking system
Danushka Online ticket booking system
Dinusha Mobile app for online ticket booking system

 

LEFT JOIN

A LEFT JOIN produces a record set which matches every row in the left table (here Employee) regardless of any matching entry in the right table (here Projects).

SELECT employee.emp_name, project.project_name
FROM employee
LEFT JOIN project on employee.project_id= project.project_id;
ORDER BY employee.emp_id ASC

Here is our result.

emp_name project_name
Sanjaya NULL
Ian Designing Reservation System
Udara WordPress Event Plugin
Marlon Designing Reservation System
Supun SEO for Company site
Tharindu Online ticket booking system
Danushka Online ticket booking system
Dinusha Mobile app for online ticket booking system
Kalum NULL

 

RIGHT JOIN

A RIGHT JOIN do the apposite thing of LET JOIN. It produces a record set which matches every entry in the right table (Project) regardless of any matching row in the left table (Employee).

SELECT employee.emp_name, project.project_name
FROM employee
RIGHT JOIN project on employee.project_id= project.project_id;
ORDER BY employee.emp_id ASC

Here is our result:

emp_name project_name
Udara WordPress Event Plugin
Supun SEO for Company site
Tharindu Online ticket booking system
Danushka Online ticket booking system
Dinusha Mobile app for online ticket booking system
Ian Designing Reservation System
Marlon Designing Reservation System
NULL Envato WordPress Theme development

 

FULL JOIN(FULL JOIN)

A OUTER JOIN which returns all records in both tables regardless of any match. Where no match exists, the missing side will contain NULL.

Though  FULL JOIN method exist conceptually, it is not implemented on MySQL database system. But we can achieve this using the UNION of a LEFT and RIGHT JOIN.

SELECT employee.emp_name, project.project_name
FROM employee
LEFT JOIN project on employee.project_id= project.project_id;

UNION

SELECT employee.emp_name, project.project_name
FROM employee
LEFT JOIN project on employee.project_id= project.project_id;

Here is our result:

emp_name project_name
Sanjaya NULL
Ian Designing Reservation System
Udara WordPress Event Plugin
Marlon Designing Reservation System
Supun SEO for Company site
Tharindu Online ticket booking system
Danushka Online ticket booking system
Dinusha Mobile app for online ticket booking system
Kalum NULL
NULL Envato WordPress Theme development