RIGHT JOIN

Initially, I strongly considered having this page simply convey the common notion that RIGHT JOINs are rarely used because the tables involved in the join operation can be rearranged to make use of a LEFT JOIN. Although that is true in my experiences, too, I feel compelled to illustrate where a RIGHT JOIN can be useful. Before doing that, let’s get the formal definition out of the way and then walk through a quick real-world problem highlighting the use of a RIGHT JOIN. This will be helpful for those finding themselves learning the RIGHT JOIN prior to the LEFT JOIN.

What does a RIGHT JOIN do? The RIGHT JOIN returns records from the right table and those with a matching value in the left table. Now that we’ve got that out of the way, let’s dive into the first example. The data used throughout the examples on this page can be found at livesql.oracle.com.

Real-world Problem 1
In our first real-world problem, the task is to retrieve a list of all departments names and corresponding employees. Additionally, the task requires all departments to be included in the list, even if no employees are currently assigned to the department. For this example task, we’ll use two tables, employees and departments. They are provided below.

Employees
The employees table contains one row per employee.

Departments
The departments table contains one row per department.

The relationship between these two tables is: an employee is assigned to zero or one department and a department consists of zero or more employees. Department ID serves as a primary key in the departments table and a foreign key in the employees table. We’ll soon see how the ON clause takes advantage of this primary key-foreign key relationship.

The code below fulfills the example task by specifying two things: the departments table as the table on the right side of the RIGHT JOIN keywords and an ON clause evaluating whether the employee’s department ID value can be found in the department ID column within the departments table. This is important because the task requires all departments to be represented in the result set. In the result set, there will be one row per department, per employee assigned. For those departments without any employees, the department will be represented within a single row and the employee ID value will not be populated (i.e., NULL). If a department consists of a single employee, then the department will still be represented within a single row; however, the employee ID value will be populated. If a department consists of multiple employees, the department name will be replicated across each of the rows and each row will have an employee ID value populated. For clarity, examine the result set provided below.

-- Return a departments and corresponding employees.
SELECT
    hr.departments.department_name,
    employees.employee_id
FROM
    hr.employees
    RIGHT JOIN
    hr.departments
    	ON hr.employees.department_id = hr.departments.department_id;

The Utility of the RIGHT JOIN
As I alluded to earlier, the extent of the RIGHT JOIN’s usefulness is not limited to the role it played in solving for tasks like the one above. Again, you’d likely find the tables to be rearranged and utilize a LEFT JOIN. Now, let’s explore an example illustrating where a RIGHT JOIN shines.

Real-world Problem 2
In this example, the initial reporting requirements are to provide a report containing details of each product. These details include the product name, product description, product listing price, category name, category description, parent category name, and parent category description. The code written to generate this specific report and corresponding result set is below. If you find the use of multiple copies of the same table to capture the parent-child relationship between product categories and product parent categories, then consider checking out my page on the SELF JOIN.

-- Retrieve product information for each product.
SELECT
    oe.product_information.product_name,
    oe.product_information.product_description,
    oe.product_information.list_price,
    child_category.category_name,
    child_category.category_description,
    parent_category.category_name AS parent_category_name,
    parent_category.category_description AS parent_category_description
FROM
    oe.product_information
    INNER JOIN
    oe.categories_tab child_category
        ON oe.product_information.category_id = child_category.category_id
    LEFT JOIN
    oe.categories_tab parent_category
    	ON child_category.parent_category_id = parent_category.category_id;

Now, imagine some duration of time has passed and the reporting requirements change slightly. In addition to the initial requirements, the report should only include those products that have been purchased at least once and the total quantity purchased across all purchases should be provided. Instead of rewriting our initial solution, we can simply build onto it.

To meet these new requirements, an additional table is needed. This table, order items, and supporting information is below.

Order Items
This table contains one row per order, per item in an order.