SQL Joins – LEFT Join, RIGHT Join, and INNER Join Explained

If you‘ve worked with relational databases and SQL before, you‘ve likely come across the concept of joins. Joins are an essential tool that allow you to combine data from multiple tables into a single result set. Without joins, retrieving related data stored across different tables would be much more cumbersome.

In this article, we‘ll take an in-depth look at the most commonly used SQL joins – INNER JOIN, LEFT JOIN, and RIGHT JOIN. We‘ll also cover a few other join types like CROSS JOIN and SELF JOIN. By the end, you‘ll have a solid understanding of what the different joins do and when to use them.

What are SQL Joins?

SQL databases store data in tables. Often, the data you need isn‘t contained within a single table, but is instead spread out across multiple related tables. This is where joins come into play.

A SQL join allows you to create a link between two or more tables based on a related column. Tables are typically related by primary key and foreign key fields. For example, an "employees" table might have a "department_id" foreign key field that references the "id" primary key of the "departments" table.

Joins allow you to write queries that combine rows from related tables. This makes it possible to retrieve data that is stored across multiple tables in a single query. Without joins, you would have to write more complex logic to query each table separately and link the data together in your application code.

INNER JOIN

The most commonly used join is the INNER JOIN. When you perform an inner join between two tables, only the rows where the join condition is met are returned.

Let‘s look at an example. Suppose we have two tables: "employees" and "departments". The "employees" table contains employee information, and the "departments" table contains department information. Each employee is assigned to a department. Here are the table definitions:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department_id INT,
  salary INT
);

CREATE TABLE departments (
  id INT PRIMARY KEY,  
  name VARCHAR(50)
);

And here is some sample data:

INSERT INTO departments 
VALUES (1, ‘Sales‘), (2, ‘Marketing‘), (3, ‘Engineering‘);

INSERT INTO employees 
VALUES 
    (1, ‘John Smith‘, 1, 60000),
    (2, ‘Jane Doe‘, 1, 65000), 
    (3, ‘Bob Johnson‘, 2, 70000),
    (4, ‘Alice Williams‘, 3, 80000),
    (5, ‘Charlie Brown‘, 3, 75000),
    (6, ‘Eve Jones‘, 4, 85000);

To perform an INNER JOIN that returns each employee along with their assigned department name, you can use the following query:

SELECT e.name AS employee, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id;

This query will return the following result set:

+---------------+-------------+
| employee      | department  |
+---------------+-------------+
| John Smith    | Sales       |
| Jane Doe      | Sales       |
| Bob Johnson   | Marketing   |
| Alice Williams| Engineering | 
| Charlie Brown | Engineering |
+---------------+-------------+

Notice that the employee "Eve Jones" is not present in the result set. This is because her department_id of 4 does not exist in the departments table. The INNER JOIN only returns rows where the join condition is met, i.e. where the department_id exists in both tables.

INNER JOIN Use Cases

INNER JOIN is useful when you want to return only the rows where a match exists in both tables. Some examples of when to use INNER JOIN include:

• Retrieving all customers and their orders, where each returned row represents an order matched to a customer
• Retrieving all students and their enrolled classes, where each returned row represents an enrollment matched to a student
• Retrieving all articles and their authors, where each returned row represents an article matched to an author

LEFT JOIN

A LEFT JOIN, also known as a LEFT OUTER JOIN, returns all rows from the left table, even if there are no matching rows in the right table. If a match doesn‘t exist, NULL values are returned for the right table columns.

Using the same "employees" and "departments" tables from above, let‘s use a LEFT JOIN to return all employees and their departments, including employees with no assigned department:

SELECT e.name AS employee, d.name AS department  
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

This query will return:

+---------------+-------------+
| employee      | department  |
+---------------+-------------+
| John Smith    | Sales       |
| Jane Doe      | Sales       |  
| Bob Johnson   | Marketing   |
| Alice Williams| Engineering |
| Charlie Brown | Engineering |        
| Eve Jones     | NULL        |
+---------------+-------------+

Notice that "Eve Jones" is now included in the result set, even though her department_id doesn‘t match any id in the departments table. The LEFT JOIN returns all rows from the left employees table, and fills in NULLs for missing matches in the right departments table.

LEFT JOIN Use Cases

LEFT JOIN is useful when you want to return all rows from the left table, and any matching rows from the right table. Some examples of when to use LEFT JOIN include:

• Retrieving all customers and their orders, including customers who have never placed an order
• Retrieving all questions and their answers, including questions with no answers
• Retrieving all products and their categories, including products with no assigned category

RIGHT JOIN

A RIGHT JOIN, also known as a RIGHT OUTER JOIN, is the inverse of a LEFT JOIN. It returns all rows from the right table, and any matching rows from the left table. If no match is found, NULL values are returned for the left table columns.

Let‘s use a RIGHT JOIN to return all departments and their employees, including departments with no assigned employees:

SELECT e.name AS employee, d.name AS department
FROM employees e 
RIGHT JOIN departments d ON e.department_id = d.id;

This query will return:

+---------------+-------------+
| employee      | department  |
+---------------+-------------+
| John Smith    | Sales       |
| Jane Doe      | Sales       |
| Bob Johnson   | Marketing   |  
| Alice Williams| Engineering |
| Charlie Brown | Engineering |        
| NULL          | HR          |
+---------------+-------------+

In this result set, the "HR" department is included even though there are no employees assigned to it. The RIGHT JOIN returns all rows from the right departments table, and fills in NULLs for the missing matches in the left employees table.

RIGHT JOIN Use Cases

RIGHT JOIN is useful when you want to return all rows from the right table, and any matching rows from the left table. Some examples of when to use RIGHT JOIN include:

• Retrieving all classes and their enrolled students, including classes with no enrollments
• Retrieving all orders and their line items, including orders with no line items
• Retrieving all articles and their comments, including articles with no comments

Combining JOINs

You aren‘t limited to joining just two tables. JOINs can be chained together to combine data from multiple tables in a single query.

For example, let‘s say we add a "salaries" table that contains salary information for each employee:

CREATE TABLE salaries (
  employee_id INT PRIMARY KEY,
  salary INT
);

INSERT INTO salaries
VALUES
    (1, 60000),
    (2, 65000),  
    (3, 70000),
    (4, 80000), 
    (5, 75000);

To retrieve each employee‘s name, department, and salary, we can join all three tables together:

SELECT e.name AS employee, d.name AS department, s.salary
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN salaries s ON e.id = s.employee_id;

This query performs an INNER JOIN between employees and departments, and then joins the result with the salaries table. The result set will include only employees that have a matching department and salary.

CROSS JOIN

A CROSS JOIN, also known as a Cartesian join, returns the Cartesian product of the joined tables. In other words, it returns every possible combination of rows from the two tables.

Let‘s look at an example. Suppose we have a "sizes" table and a "colors" table:

CREATE TABLE sizes (
  id INT PRIMARY KEY,
  name VARCHAR(20)  
);

CREATE TABLE colors (
  id INT PRIMARY KEY,
  name VARCHAR(20)
);

INSERT INTO sizes VALUES (1, ‘Small‘), (2, ‘Medium‘), (3, ‘Large‘);
INSERT INTO colors VALUES (1, ‘Red‘), (2, ‘Green‘), (3, ‘Blue‘);  

To get every possible combination of sizes and colors, we can use a CROSS JOIN:

SELECT s.name AS size, c.name AS color 
FROM sizes s
CROSS JOIN colors c;

This will return:

+--------+-------+
| size   | color |  
+--------+-------+
| Small  | Red   |
| Small  | Green |
| Small  | Blue  | 
| Medium | Red   | 
| Medium | Green |
| Medium | Blue  |
| Large  | Red   |
| Large  | Green | 
| Large  | Blue  |
+--------+-------+

The result set contains 9 rows (3 sizes * 3 colors), representing every possible pairing of size and color.

CROSS JOIN Use Cases

CROSS JOIN is less commonly used than the other joins, but it can be useful in certain scenarios:

• Generating test data: CROSS JOIN can create every possible combination of rows, which is helpful for testing.
• Creating permutations: If you need to analyze every possible grouping or combination of certain data sets, CROSS JOIN can generate those permutations.

SELF JOIN

A SELF JOIN is a join in which a table is joined with itself. Self joins are useful for comparing rows within the same table, or for traversing hierarchical data stored in a single table.

For example, suppose we have an "employees" table that includes each employee‘s manager:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT
);

INSERT INTO employees
VALUES 
    (1, ‘John Smith‘, NULL),
    (2, ‘Jane Doe‘, 1),
    (3, ‘Bob Johnson‘, 1),  
    (4, ‘Alice Williams‘, 2),
    (5, ‘Charlie Brown‘, 2),
    (6, ‘Eve Jones‘, 3);  

To retrieve each employee along with their manager‘s name, we can use a self join:

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

The result set will be:

+---------------+------------+
| employee      | manager    |
+---------------+------------+
| John Smith    | NULL       |
| Jane Doe      | John Smith |
| Bob Johnson   | John Smith |
| Alice Williams| Jane Doe   |  
| Charlie Brown | Jane Doe   |
| Eve Jones     | Bob Johnson|
+---------------+------------+

In this self join, the employees table is joined with itself to match each employee with their manager. Note the use of table aliases (e1 and e2) to distinguish the two instances of the employees table in the join.

SELF JOIN Use Cases

SELF JOIN is useful when you need to compare rows within the same table. Some examples include:

• Querying hierarchical data: Self joins can traverse "parent-child" relationships stored in a single table, like the employee-manager relationship in the example above.
• Comparing rows: Self joins can be used to find duplicate rows, or to find rows that match certain conditions relative to other rows in the table.

Other Types of JOINs

In addition to the joins we‘ve covered, there are a few other less commonly used join types worth mentioning:

• FULL OUTER JOIN: Returns all rows from both tables, filling in NULLs for missing matches on either side. It‘s like a combination of LEFT JOIN and RIGHT JOIN.
• NATURAL JOIN: A join that automatically matches columns in both tables that have the same name. Generally avoided in practice due to its implicit nature.

Conclusion

In this article, we‘ve taken a deep dive into the world of SQL joins. We‘ve covered the most common types of joins – INNER, LEFT, and RIGHT – and explored their use cases with practical examples. We‘ve also looked at some more advanced join scenarios, like joining multiple tables, CROSS JOIN, and SELF JOIN.

Joins are a fundamental concept in SQL, and a solid understanding of how they work is crucial for anyone working with relational databases. By mastering joins, you‘ll be able to retrieve data efficiently and flexibly from multiple tables.

I hope this article has been helpful in demystifying SQL joins for you. If you have any questions or want to share your own experiences with joins, feel free to reach out. Happy querying!