The concept of SQL joins is a must-have skill. Every developer should be able to use it in different use-cases with the best performance possible. This tutorial reveal the difference between Inner Join and Outer Join (Left Outer Join and Right Outer Join) in practical situations. Confusion and mixing between the concepts will be cleared up as we go on. Theoretical parts will be minimized as possible. Who loves theoretical parts anyway?!

This is the first article. It talks about general concepts about SQL Joins. In the second article (here), you can find performance comparison (benchmark) between different types of joins.

For better understanding, this article assumes that the reader understands the following:

  1. Basics of databases [Table Structure, Relationships, Keys, …]
  2. Simple SQL Select queries.

Why do we use joins?

Databases provide a great way to save data in multiple tables where each table is responsible for saving one aspect(part) of the data. This ensures data consistency and reduces data redundancy as each part of the data is saved only once.

For example, if we have a sample database for employees where we save:

  1. Employees’ basic details: (name, SSN, Birthdate).
  2. Departments at which the employee(s) work, where we assume that each emplyee works at maximum of one department.
  3. Bonuses for the employee.

Of course, a diagram worth a thousand words:

SQL Joins - Database Example Diagram

That is a good design as data are only saved once (no redundancy), but what if I want to get the details of an employee and his department?

It seems very easy and straight forward, just get his DepartmentID and search for it in the Departments table and then get the data of the department. Fair enough.

As always, the devil is in the details:

  • What if we want all the employees with their corresponding departments?
  • What if some of the employees doesn’t have a department (for example, fresh employee)?
  • And the problem is even harder for bonuses: If we want to get the bonuses of multiple employees, will we get each id and search for each of them in the table separately?!
  • What if the tables are large, no I mean huge? What will be the performance?

Not very easy huh! But Joins save the situation as we will see.

Firstly, SQL introduces multiple types of joins and the name indicates they join multiple tables together. Let’s take them one by one and study when they are used in practical situations and how we write them and Finally we make a benchmark for the performance of SQL Joins (with intro to indexes).

While the syntax for writing queries with JOIN is considered very easy, the practical concepts are usually confusing.

Database Example:

Department

ID Name Phone EMail
1 IT (005)-555 5555 it@example.com
2 Sales (005)-555 5556 sales@example.com

Employee

SSN Name Mobile Birthdate DepartmentID
001-01-1933 Bob (005)-015 4567 1981-08-21 1
004-02-1543 Alice (005)-013 5678 1972-01-01 NULL
009-02-1422 Dan (005)-014 9876 1993-03-27 2

EmployeeBonus

EmplyeeSSN Reason Bonusdate Value
001-01-1933 Over time 2017-05-05 100
001-01-1933 Early delivery 2017-05-07 150
004-02-1543 Travelling 2017-05-08 500

Let’s start with Inner Join and then we can discuss the need for Outer Join for practical use-cases.

Inner Join

It simply combines the rows in two tables based on columns having corresponding values.

Example 1: Get employees with their departments’ details:

SELECT * 
FROM Employee 
INNER JOIN Department ON Department.ID =Employee.DepartmentID
SSN Name Mobile Birthdate DepartmentID ID Name Email Phone
001-01-1933 Bob (005)-015 4567 1981-08-21 1 1 IT it@example.com (005)-555 5555
009-02-1422 Dan (005)-014 9876 1993-03-27 2 2 Sales sales@example.com (005)-555 5556

 

But wait a second: Where is Alice?!

Let’s take a look at the tables:

SQL Joins - Inner Join Example

Alice isn’t connected to any department (doesn’t have DeparmentID), so it has been omitted from the result. Inner join only shows related data in the two tables based on the [on statement] ( Department.ID =Employee.DepartmentID ).

Example 2: To get employees with employees’ bonus(es):

SELECT *
FROM Employee
INNER JOIN EmployeeBonus on Employee.SSN = EmployeeBonus.EmployeeSSN
Result:
SSN Name Mobile Birthdate DepartmentID EmplyeeSSN Reason Bonusdate Value
001-01-1933 Bob (005)-015 4567 1981-08-21 1 001-01-1933 Over time 2017-05-05 100
001-01-1933 Bob (005)-015 4567 1981-08-21 1 001-01-1933 Early delivery 2017-05-07 150
004-02-1543 Alice (005)-013 5678 1972-01-01 NULL 004-02-1543 Travelling 2017-05-08 500

What if we want to get all the employees whether they have bonuses or not AND get related bonus if there is any. The answer is simply Outer Joins (Left, Right).

Outer Joins

There are two types of Outer Joins (Left Outer Join and Right Outer Join). Instead of having the same effect on the results as in Inner Joins, In outer joins, the two tables affects the results differently:

  • Main Table from which all the rows are retrieved.
  • Related-Only Table from which only rows related to Main Table are retrieved.

In Left Joins: The Main Table is LeftTable.

SELECT ….
FROM LeftTable
LEFT JOIN RightTable  ON …;

Gets all the rows in the LeftTable and if related rows exist in the RigthTable get them also.

In Right Joins: The Main Table here is RightTable.

SELECT ….
FROM LeftTable
Right JOIN RightTable ON …;

Gets all the rows in the RightTable and if related rows exist in the LeftTable get them also.

Example 1: Get all employees and get related department(s) if exists

  • Using Left Join:

SELECT Employee.SSN, Employee.Name as 'Employee Name', 
Department.Name as 'Department Name', Department.Email as 'Department Email' 
FROM Employee   
LEFT OUTER JOIN Department ON Department.ID = Employee.DepartmentID

SQL Joins - Left Outer Join Example

  • Using Right Join:

We just exchange Employee and Department in the FROM part:

SELECT Employee.SSN, Employee.Name as 'Employee Name', Department.Name as 'Department Name', Department.Email as 'Department Email'
FROM Department
RIGHT OUTER JOIN Employee ON Department.ID = Employee.DepartmentID

SQL Joins - Right Outer Join

Result:
SSN Employee Name Department Name Department Email
001-01-1933 Bob IT it@example.com
004-02-1543 Alice NULL NULL
009-02-1422 Dan Sales sales@example.com

 

Example 2: Get all employees with bonus(es) if exist.

  • Using Left Join:

SELECT Employee.SSN, Employee.Name as 'Employee Name', EmployeeBonus.Reason , EmployeeBonus.BonusDate,EmployeeBonus.Value
FROM Employee
LEFT OUTER JOIN EmployeeBonus  ON Employee.SSN = EmployeeBonus.EmployeeSSN
  • Using Right Join:

SELECT Employee.SSN, Employee.Name as 'Employee Name', EmployeeBonus.Reason , EmployeeBonus.BonusDate,EmployeeBonus.Value
FROM EmployeeBonus
RIGHT OUTER JOIN Employee ON Employee.SSN = EmployeeBonus.EmployeeSSN
Result:
SSN Employee Name Reason BonusDate Value
001-01-1933 Bob Over Time 2017-05-05 100
001-01-1933 Bob Early delivery 2017-05-07 150
004-02-1543 Alice Travelling 2017-05-08 500
009-02-1422 Dan NULL NULL NULL

 

Example 3: Get all employees with all details (bonus(es) and department) if there exists.

SELECT Employee.SSN, Employee.Name as 'Employee Name', Department.Name as 'Department Name', Department.Email as 'Department Email', EmployeeBonus.Reason , EmployeeBonus.BonusDate,EmployeeBonus.Value
FROM Employee
LEFT OUTER JOIN EmployeeBonus  ON Employee.SSN = EmployeeBonus.EmployeeSSN
LEFT OUTER JOIN Department ON Department.ID = Employee.DepartmentID
SSN Employee Name Department Name Department Email Reason BonusDate Value
001-01-1933 Bob IT it@example.com Over Time 2017-05-05 100
001-01-1933 Bob IT it@example.com Early delivery 2017-05-07 150
004-02-1543 Alice NULL NULL Travelling 2017-05-08 500
009-02-1422 Dan Sales sales@example.com NULL NULL NULL

 

Other Use Cases in real life:

  1. if we want to make a report to get all the details for all the employees and related data [Outer Join].
  2. If we want to get the details of an Bob and his bonuses(if exists) [Outer Join].
  3. If we want to get the details of a non-empty department with the details of the employees who work in it (Department inner join Employee) [Why?]

 

Conclusion:

Joins are very important for extracting related details from different table in any database systems.

Inner Join: Gets ONLY the common rows between two tables.

Outer Joins (Left/ Right): Get ALL the rows from one table [Main Table] and related rows from the other table [Related-Only Table].

You can find the used database here, so that you can try all the code yourself.

In the second article (here), you can find performance comparison (benchmark) between different types of joins with tips and tricks on Indexes.

TiTrias Co Founder, with 5+ years of teaching experience, Youssef has great experience in neural network and loves writing articles!