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

Employees Table

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:

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.

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!