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 table 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 satisfies 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 columns values from the row of the T2 table are 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:

Oracle RIGHT JOIN - Orders & Employees Tables

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)
RIGHT OUTER JOIN in Oracle Example

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)
RIGHT OUTER JOIN in Oracle - Customers and Orders Example

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)
Oracle RIGHT JOIN WHERE condition

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)
Oracle RIGHT JOIN ON condition

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

A note that 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.

Was this tutorial helpful?