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:
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:
- Inner Join
- Left Join
- Right Join
- 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.
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
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.
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
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.
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.
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!
Top comments (1)
Thanks for this very useful post. I just wanted to add that, there is a very easy way now, to test all the SQLs described here, using the free & portable tools, mentioned in my latest post here : dev.to/linuxguist/learn-sql-quickl...