Oracle Right Join with Practical Examples

We will be learning the usage of RIGHT OUTER JOIN in Oracle in this tutorial for joining more than two tables.

There are three types of Outer Joins, i.e, Left Outer Join, Right Outer Join and Full Outer Join which are used when we want to keep all rows from either left, right or both tables in the result set after join.

RIGHT JOIN or RIGHT OUTER JOIN returns all rows from right table and only matching rows from left table.

Syntax:

The syntax of RIGHT JOIN is as follows:

SELECT column_names
FROM Table_1
RIGHT OUTER JOIN Table_2 ON
    join_condition;

Here,

Table_1 and Table_2 – Tables that we want to join. We assume Table_1 to be the left table and Table_2 to be right.

join_condition – The join condition which is based on the common column between both the tables.

The right join query returns all the rows that are present in right table and only the matching rows from left table.

The Venn Diagram of Oracle Right Join is as follows:

Oracle Right Join Examples

1. Oracle Right Join Example

Let’s use RIGHT JOIN for joining Employees and Departments table:

SELECT first_name, last_name, 
       department_name, salary
FROM employees e
RIGHT JOIN departments d 
ON e.department_id= d.department_id;

Result:

The query returns all rows from the Departments table and only matching rows from the Employees table. If we look closely, we can see that we got total 122 rows which is more than the total number of total number of rows in Employees table which is 107.

So, we get all the rows from right table and just the matching rows from Employee table. There were few departments in which no employee has been assigned, so the query returned NULL values instead of Employee name for these departments in the result set.

2. Right Join with Multiple Tables

We can use RIGHT JOIN to join multiple tables, lets have a look:

SELECT first_name, last_name,
       salary,department_name, city
FROM employees e
RIGHT JOIN departments d 
ON e.department_id= d.department_id
RIGHT JOIN locations l
ON d.location_id=l.location_id;

Result:

We used RIGHT JOIN to join 3 tables which are Employees, Departments and Locations. We get all the rows from the right most table and only the matching rows from tables on the left.

3. RIGHT Join with Using Clause

Syntax:

The syntax of using RIGHT JOIN with USING Clause is as follows:

SELECT
    column_names
FROM
    Table_1
RIGHT JOIN Table_2 USING(c1,c2,c3, ...);

Here,

c1, c2, c3 etc. – These are the common columns with same data type that are present in both tables that we are about to join.

We will be using RIGHT JOIN with USING clause in this following query:

SELECT first_name, last_name, 
       salary,department_name 
FROM employees 
RIGHT JOIN departments USING (department_id)
ORDER BY department_id;

Result:

The query right joins the Employee table with Department table and displays all the columns we mentioned.

Additional conditions in Join WHERE vs AND

WHERE

We use the following query to find all the employees earning less than 4000 with the name of their departments.

SELECT first_name, last_name, 
       salary,department_name 
FROM employees e
RIGHT OUTER JOIN departments d 
ON e.department_id=d.department_id
WHERE salary < 4000
ORDER BY department_name;

Result:

Same condition with AND

Let’s see the result when we use AND instead of WHERE

SELECT first_name, last_name, 
       salary,department_name 
FROM employees e
RIGHT OUTER JOIN departments d 
ON e.department_id=d.department_id
AND salary < 4000
ORDER BY department_name;

Result:

Here, the query returned all departments but only the employees who have salary less than 4000, will show in results.

This is because we added AND condition on ON join condition. We can see a similar result set even while using INNER JOIN.

In this query, we learned how to use RIGHT JOIN to join two tables and understood the nuances that will help us while using it.