SQL Joins Explained: Case Example
Tags: sql, datascience, joins, programming
Published: 2023-09-13 01:55:44

Structured Query Language(SQL) is a computer language for storing, manipulating, and retrieving data stored in a relational database.

SQL Joins are like clauses used to combine records from two or more tables in a database, just as the name(join) means.

In this article, you will learn, using a case example, the different types of joins, when, and how they are used. Be sure to check for “**bonus joins”** included at the end of the article.

## Example Data

We will use data from these 2 tables to show various ways to display employees from John Smith's company.

### Departments Table

| Department_Id | Department_Name |
| --- | --- |
| 1 | Executive |
| 2 | HR |
| 3 | Sales |
| 4 | Support|
| 5 | Sales |
| 6 | Research |

### Employees Table

| Employee_Id  | Full_Name | Department_Id | Job_Role | Manager_Id |
| --- | --- | --- | --- | --- |
| 1 | John Smith | 1 | CEO | Null |
| 2 | Sarah Goodes | 1 | CFO | 1 |
| 3 | Wayne Ablett | 1 | CIO | 1 |
| 4 | Michelle Carey | 2 | HR Manager | 1 |
| 5 | Chris Matthews | 3 | Sales Manager  | 2 |
| 6 | Andrew Judd | 4 | Development Manager | 3 |
| 7 | Danielle McLeod | 5 | Support Manager | 3 |
| 8 | Matthew Swan | 2 | HR Representative | 4 |
| 9 | Stephanie Richardson | 2 | Salesperson | 5 |
| 10 | Tony Grant | 3 | Salesperson | 5 |
| 11 | Jenna Lockett | 4 | Front-End Developer | 6 |
| 12 | Michael Dunstall | 4 | Back-End Developer | 6 |
| 13 | Jane Voss | 4 | Back-End Developer | 6 |
| 14 | Anthony Hird | Null | Support | 7 |
| 15 | Natalie Rocca | 5 | Support | 7 |

The code below shows the syntax for writing a JOIN:

```sql
SELECT columns
FROM table1
JOIN table2 ON table1.column1=table2.column1
```

**Types of Joins**

There are four major types of joins in SQL, as listed below:

1. Inner Join
2. Left Join
3. Right Join
4. Full Join

***Inner Join***

It creates a result table that displays information **common** between two tables based on a shared piece of information. 

It is the most important and frequently used of the joins.

In this case, we could use it to display employees from the departments listed in the first table.

```sql
SELECT employees.Full_Name, employees.Job_Role, departments.Department_Name
FROM departments
INNER JOIN employees ON departments.Department_Id = employees.Department_Id
 -- You can replace the keyword INNER JOIN with JOIN
```

You should get the results below

- Result
    
    
    | Full_Name | Job_Role | Department_Name |
    | --- | --- | --- |
    | John Smith | CEO | Executive |
    | Sarah Goodes | CFO | Executive |
    | Wayne Ablett | CIO | Executive |
    | Michelle Carey | HR Manager | HR |
    | Chris Matthews | Sales Manager  | Sales |
    | Andrew Judd | Development Manager | Development |
    | Danielle McLeod | Support Manager | Support |
    | Matthew Swan | HR Representative | HR |
    | Stephanie Richardson | Salesperson | HR |
    | Tony Grant | Salesperson | Sales |
    | Jenna Lockett | Front-End Developer | Development |
    | Michael Dunstall | Back-End Developer | Development |
    | Jane Voss | Back-End Developer | Development |
    | Natalie Rocca | Support | Support |

***Left Join***

A *LEFT JOIN* returns **all** rows from the left table, plus matched values from the right table or NULL in case of no match.

> Note: The left table refers to the table that appears before the "LEFT JOIN" keywords in your SQL query. Same case applies when using RIGHT JOIN
> 

We could use left join to retrieve a list of all employees along with their department names. If an employee doesn't belong to a department, display NULL

```sql
SELECT employees.Full_Name, departments.Department_Name
FROM employees 
LEFT JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Full_Name ASC

```

You should get the results below

- Result
    
    
    | Employee_Id | Full_Name | Department_Name |
    | --- | --- | --- |
    | 1 | John Smith | Executive |
    | 2 | Sarah Goodes | Executive |
    | 3 | Wayne Ablett | Executive |
    | 4 | Michelle Carey | HR |
    | 5 | Chris Matthews | SALES |
    | 6 | Andrew Judd | Development |
    | 7 | Danielle McLeod | Support |
    | 8 | Matthew Swan | HR |
    | 9 | Stephanie Richardson | HR |
    | 10 | Tony Grant | SALES |
    | 11 | Jenna Lockett | Development |
    | 12 | Michael Dunstall | Development |
    | 13 | Jane Voss | Development |
    | 14 | Anthony Hird | NULL |
    | 15 | Natalie Rocca | Support |

You can use the COALESCE() function to replace NULL values with more relatable user-defined values.

i.e. If an employee doesn't belong to a department, display "No Department" instead.

```sql
SELECT employees.Employee_Id, employees.Full_Name, 
COALESCE(departments.Department_Name, "No Department") AS Department_Name
FROM employees 
LEFT JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Full_Name ASC
```

You should get the results below

- Result
    
    
    | Employee_Id | Full_Name | Department_Name |
    | --- | --- | --- |
    | 1 | John Smith | Executive |
    | 2 | Sarah Goodes | Executive |
    | 3 | Wayne Ablett | Executive |
    | 4 | Michelle Carey | HR |
    | 5 | Chris Matthews | SALES |
    | 6 | Andrew Judd | Development |
    | 7 | Danielle McLeod | Support |
    | 8 | Matthew Swan | HR |
    | 9 | Stephanie Richardson | HR |
    | 10 | Tony Grant | SALES |
    | 11 | Jenna Lockett | Development |
    | 12 | Michael Dunstall | Development |
    | 13 | Jane Voss | Development |
    | 14 | Anthony Hird | No Department |
    | 15 | Natalie Rocca | Support |

***Right Join***

The *SQL RIGHT JOIN* returns all rows from the right table, even if there are no matches
in the left table. Not so different from the LEFT JOIN.

In this case, we could view employees and their various departments

```sql
SELECT Department_Name,
COALESCE (employees.Full_Name, "No Employee") AS Full_Name 
FROM employees 
RIGHT JOIN departments ON departments.Department_Id = employees.Department_Id;
```

You should get the results below

- Result
    
    
    | Department_Name  | Full_Name |
    | --- | --- |
    | Executive | Wayne Ablett |
    | Executive | Sarah Goodes |
    | Executive | John Smith |
    | HR | Stephanie Richardson |
    | HR | Matthew Swan |
    | HR | Michelle Carey |
    | Sales | Tony Grant |
    | Sales | Chris Matthews |
    | Development | Jane Voss |
    | Development | Michael Dunstall |
    | Development | Jenna Lockett |
    | Development | Andrew Judd |
    | Support | Natalie Rocca |
    | Support | Danielle McLeod |
    | Research | No Employee |

***Full Join***

The *SQL FULL JOIN* combines the results of both left and right outer joins.

The joined table will contain all records from both tables and fill in NULLs for missing
matches on either side.

```sql
SELECT employees.Employee_Id, employees.Full_Name, employees.Job_Role, 
departments.Department_Id, departments.Department_Name
FROM employees 
FULL JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Employee_Id ASC
```
You should get the results below

- Result
    
    
    | EMPLOYEE_ID | FULL_NAME | JOB_ROLE | DEPARTMENT_ID | DEPARTMENT_NAME |
    | --- | --- | --- | --- | --- |
    | 1 | John Smith | CEO | 1 | Executive |
    | 2 | Sarah Goodes | CFO | 1 | Executive |
    | 3 | Wayne Ablett | CIO | 1 | Executive |
    | 4 | Michelle Carey | HR Manager | 2 | HR |
    | 5 | Chris Matthews | Sales Manager | 3 | Sales |
    | 6 | Andrew Judd | Development Manager | 4 | Development |
    | 7 | Danielle McLeod | Support Manager | 5 | Support |
    | 8 | Matthew Swan | HR Representative | 2 | HR |
    | 9 | Stephanie Richardson | Salesperson | 2 | HR |
    | 10 | Tony Grant | Salesperson | 3 | Sales |
    | 11 | Jenna Lockett | Front-End Developer | 4 | Development |
    | 12 | Michael Dunstall | Back-End Developer | 4 | Development |
    | 13 | Jane Voss | Back-End Developer | 4 | Development |
    | 14 | Anthony Hird | Support | NULL | NULL |
    | 15 | Natalie Rocca | Support | 5 | Support |
    | NULL | NULL | NULL | 6 | Research |

## Bonus Joins

  ***Self Join***

A *SELF JOIN* is typically not a join type but a special way of joining a table to itself. You may want to combine rows in a table based on a related column present in the table.

It is commonly used when you need to traverse a hierarchical structure where each row references another row in the same table 

OR

When comparing data within rows in the same table.

In this case, we will use self-join in the employees’ table to display the employee-manager relationship. Each employee record contains a reference to the manager's ID, allowing us to retrieve more information about the managers by adding another column, “Manager_Name”. In Data Science, this is an example of ***feature engineering.***

```sql
SELECT
e.Employee_Id,
e.Full_Name,
e.Job_Role,
m.Employee_Id AS Manager_Id,
m.Full_Name AS Manager_Name
FROM employees e, employees m 
WHERE e.Manager_Id = m.Employee_Id;
```

You should get the results below

- Result
    
    
    | EMPLOYEE_ID | FULL_NAME | JOB_ROLE | DEPARTMENT_ID | MANAGER_NAME |
    | --- | --- | --- | --- | --- |
    | 2 | Sarah Goodes | CFO | 1 | John Smith |
    | 3 | Wayne Ablett | CIO | 1 | John Smith |
    | 4 | Michelle Carey | HR Manager | 1 | John Smith |
    | 5 | Chris Matthews | Sales Manager  | 2 | Sarah Goodes |
    | 6 | Andrew Judd | Development Manager | 3 | Wayne Ablett |
    | 7 | Danielle McLeod | Support Manager | 3 | Wayne Ablett |
    | 8 | Matthew Swan | HR Representative | 4 | Michelle Carey |
    | 9 | Stephanie Richardson | Salesperson | 5 | Chris Matthews |
    | 10 | Tony Grant | Salesperson | 5 | Chris Matthews |
    | 11 | Jenna Lockett | Front-End Developer | 6 | Andrew Judd |
    | 12 | Michael Dunstall | Back-End Developer | 6 | Andrew Judd |
    | 13 | Jane Voss | Back-End Developer | 6 | Andrew Judd |
    | 14 | Anthony Hird | Support | 7 | Danielle McLeod |
    | 15 | Natalie Rocca | Support | 7 | Danielle McLeod |

That's most of what you need to know about SQL Joins. Hopefully, you found this information helpful. Feel free to share it with anyone who's having a hard time with Joins, and keep learning! 😊

If you have any questions, please leave them in the comments section below.

**Thanks for reading!**