How to Use LEFT JOIN and RIGHT JOIN in SQL?

Welcome back to our SQL tutorial series! In this article, we’re going to explore the LEFT JOIN and RIGHT JOIN operations, which are used to combine rows from two or more tables based on a related column. Understanding how to use these joins is essential for working with relational databases, as they allow you to retrieve and analyze data spread across multiple tables.

Understanding LEFT JOIN

The LEFT JOIN (or LEFT OUTER JOIN) operation returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.

Basic Syntax of LEFT JOIN

The basic syntax of the LEFT JOIN operation is as follows:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Understanding RIGHT JOIN

The RIGHT JOIN (or RIGHT OUTER JOIN) operation returns all rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the side of the left table.

Basic Syntax of RIGHT JOIN

The basic syntax of the RIGHT JOIN operation is as follows:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

Let’s look at some practical examples to understand how to use the LEFT JOIN and RIGHT JOIN operations effectively.

Example Tables: employees and departments

We’ll use the following employees and departments tables for our examples:

employees

departments

Using LEFT JOIN

Example 1: Basic LEFT JOIN

To retrieve all employees along with their department names, you use:

SELECT e.name, e.position, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

This command returns:

Sarah Wilson does not have a department, so the department name is NULL.

Example 2: LEFT JOIN with WHERE Clause

You can combine the LEFT JOIN with a WHERE clause to filter the results. For example, to find employees who do not belong to any department:

SELECT e.name, e.position
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

This command returns:

Using RIGHT JOIN

Example 1: Basic RIGHT JOIN

To retrieve all departments along with their employees, you use:

SELECT e.name, e.position, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;

This command returns:

The Marketing department does not have any employees, so the name and position are NULL.

Example 2: RIGHT JOIN with WHERE Clause

You can combine the RIGHT JOIN with a WHERE clause to filter the results. For example, to find departments that do not have any employees:

SELECT d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.id IS NULL;

This command returns:

Conclusion

The LEFT JOIN and RIGHT JOIN operations are powerful tools for combining data from multiple tables in SQL. They allow you to retrieve and analyze related data, even when some records do not have matches in the other table. Understanding how to use these joins effectively is essential for working with relational databases and performing comprehensive data analysis.

In our next tutorial, we’ll explore more advanced SQL topics and commands. Stay tuned and happy coding!