RIGHT JOIN

Introduction

In SQL, a RIGHT JOIN is one of the fundamental types of joins used to combine rows from two or more tables based on a related column between them. Unlike other join types like INNER JOIN, LEFT JOIN, and FULL OUTER JOIN, a RIGHT JOIN returns all records from the right table and the matched records from the left table. If there is no match, the result is NULL on the side of the left table.

This type of join is particularly useful when you want to ensure that all records from one specific table are included in your results, regardless of whether they have corresponding entries in another table.

Concept

The basic syntax for a RIGHT JOIN is as follows:

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

Here's a breakdown of the components:

• table1: The left table.
• table2: The right table.
• common_column: The column that is common to both tables and used for matching records.

The RIGHT JOIN will return all rows from table2, along with the matched rows from table1. If there are no matches, the columns from table1 will contain NULL.

Examples

Let's illustrate the concept of a RIGHT JOIN with some practical examples. We'll use two tables: employees and departments.

Example 1: Basic RIGHT JOIN

Suppose we have the following tables:

Table: employees

Table: departments

Now, let's perform a RIGHT JOIN to get all departments and their corresponding employees:

SELECT employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

Output:

In this example, all records from the departments table are returned. The Marketing department has no corresponding employee in the employees table, so the employee_name is NULL.

Example 2: Including All Columns

You can also include all columns from both tables in your result set:

SELECT *
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

Output:

This example shows all columns from both tables, with NULL values where there are no matches.

Example 3: Filtering Results

You can also apply filters to the results of a RIGHT JOIN. For instance, let's filter to show only departments that have employees:

SELECT employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id
WHERE employees.employee_name IS NOT NULL;

Output:

This query excludes the Marketing department because it has no corresponding employee.

What's Next?

After mastering the RIGHT JOIN, you might want to explore other types of joins, such as the FULL OUTER JOIN. This type of join returns all records when there is a match in either left or right table. You can find more information on this topic in our next tutorial.

If you have any questions or need further clarification on RIGHT JOIN and its applications, feel free to ask!