Oracle RIGHT JOIN

Summary: in this tutorial, you will learn how to use the RIGHT OUTER JOIN in Oracle to join two or more tables.

Overview of RIGHT OUTER JOIN in Oracle

Suppose we have two tables T1 and T2, the following statement shows how to join these two tables using the RIGHT OUTER JOIN clause in Oracle:

SELECT
    column_list
FROM
    T1
RIGHT OUTER JOIN T2 ON
    join_predicate;
Code language: SQL (Structured Query Language) (sql)

In this statement, T1 and T2 are the left and right tables respectively.

The OUTER keyword is optional therefore the RIGHT OUTER JOIN and RIGHT JOIN are the same.

Here is how the RIGHT OUTER JOIN works.

Each row from the T1 table is compared with rows from the T2table:

• If a pair of rows satisfy the join predicate, the column values of both rows are combined to make a result row that is then included in the result set.
• In case a row in the T2 table does not match any row in the T1 table, the column values from the row of the T2 table is combined with a NULL value for each column of the row from the T1 table to make the result row which is then also included in the result set.

In other words, a right outer join returns all rows from the right table and also the matching rows from the left table.

Oracle RIGHT OUTER JOIN examples

We will use the orders and employees tables in the sample database for the demonstration:

In this diagram, a salesman is in charge of one or more sales orders. However, some sales orders may not be in charge of any salesman.

The following example retrieves all salesman and their sales orders if any:

SELECT
    first_name,
    last_name,
    order_id,
    status
FROM
    orders
RIGHT JOIN employees ON
    employee_id = salesman_id
WHERE
    job_title = 'Sales Representative'
ORDER BY
    first_name,
    last_name;
Code language: SQL (Structured Query Language) (sql)

The result includes all employees whose job title is Sales Representative and their orders.

If a salesman is not in charge of any sales order such as Alice Wells, Charlotte Webb, the order_id and status columns are filled with NULL values.

Oracle RIGHT OUTER JOIN with USING clause

Similar to other joins such as INNER JOIN, LEFT JOIN, you can use the USING clause to specify which column to test for equality when joining tables.

The following illustrates the syntax of the RIGHT OUTER JOIN with the USING clause:

SELECT
    column_list
FROM
    T1
RIGHT OUTER JOIN T2 USING(c1,c2,c3);
Code language: SQL (Structured Query Language) (sql)

In this query, the columns listed in the USING clause must be presented in both T1 and T2 tables.

The following statement is equivalent to the one above:

SELECT
    column_list
FROM
    T1
RIGHT OUTER JOIN T2 ON
    T1.c1 = T2.c1
    AND T1.c2 = T2.c2
    AND T1.c3 = T2.c3;
Code language: SQL (Structured Query Language) (sql)

The following statement demonstrates how to use the RIGHT OUTER JOIN with the USING clause:

SELECT
    name,
    order_id,
    status
FROM
    orders
RIGHT JOIN customers
        USING(customer_id)
ORDER BY
    name;
Code language: SQL (Structured Query Language) (sql)

In this example, all customers are included in the result set. If a customer does not have a sales order, the order_id and status columns are filled with NULL values.

Oracle RIGHT JOIN: condition in ON vs. WHERE clause

The following statement gets the employee and order data of the salesman id 57.

SELECT
    employee_id,
    last_name,
    first_name,
    order_id,
    status
FROM
    orders
RIGHT JOIN employees ON
    employee_id = salesman_id
WHERE
    employee_id = 57;
Code language: SQL (Structured Query Language) (sql)

The following statement places the condition in the WHERE clause instead of the ON clause:

SELECT
    employee_id,
    last_name,
    first_name,
    order_id,
    status
FROM
    orders
RIGHT JOIN employees ON
    employee_id = salesman_id
    AND employee_id = 57;
Code language: SQL (Structured Query Language) (sql)

The query returned all employees but only employee id 57 had the related order data.

Regarding the INNER JOIN, the condition is placed in the ON clause has the same effect as it is placed in the WHERE clause.

In this tutorial, you have learned how to use the RIGHT OUTER JOIN in Oracle to query data from multiple tables.