Right Outer Join in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Right Outer Join in Oracle with Examples

In this article, I am going to discuss the Right Outer Join in Oracle with Examples. Please read our previous article where we discussed Left Outer Join in Oracle with Examples. At the end of this article, you will understand the following pointers in detail.

1. What is Right Outer Join?
2. How to implement Right Outer Join in Oracle?
3. When do we need to use Right Outer Join in Oracle?
4. Examples to Understand Right Outer Join
5. How to use Right Join along with Where Condition?
6. How to retrieve only the non-matching rows from the Right table?
7. Joining three Tables in Oracle using Right Outer Join

What is Right Outer Join in Oracle?

The Right Outer Join in Oracle is used to retrieve all the matching records from both the tables as well as non-matching records from the right-side table. In that case, the non-matching data will take a null value. The RIGHT OUTER JOIN or RIGHT JOIN keyword is used to perform the right join operation in Oracle.

The question that should come to your mind is which is the left table and which is the right table? The answer is, the table which is mentioned to the left of the RIGHT OUTER JOIN keyword is the left table, and the table which is mentioned to the right of the RIGHT OUTER JOIN keyword is the right table. The following diagram shows the pictorial representation of Right Outer Join.

How to implement Right Outer Join in Oracle?

The following images show how to use Right Outer Join in Oracle. As you can see in the below image, we can use either the RIGHT OUTER JOIN or RIGHT JOIN keyword to perform the Right Join operation in Oracle. That means if you use the RIGHT JOIN keyword, then also it is going to perform Right Outer JOIN Operation in Oracle.

When do we need to use Right JOIN in Oracle?

If you want to retrieve all the matching rows from both the tables involved in the join as well as all the non-matching rows from the right-side table in the result set then you need to use Right Outer Join in Oracle. In that case, the non-matching rows will take a null value.

Examples to Understand RIGHT OUTER JOIN:

Let us understand how to implement Right Outer Join in Oracle with Examples. To understand Right Outer Join, we are going to use the following Employee, Address, and Projects tables. Here, EmployeeId is the common column that is available in all the tables and based on this column we are going to perform the Right Outer Join Operations.

Please use the following SQL Script to create the Employee, Address, and Projects tables with the required data.

set linesize 500;
DROP TABLE Employee;
CREATE TABLE Employee (
  EmployeeId INT PRIMARY KEY,
  FullName VARCHAR(20),
  Department VARCHAR(10),
  Salary INT,
  Gender VARCHAR(10),
  Age INT
);

INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1007, 'Priyanka Dewangan', 'HR', 45000, 'Female', 27);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26);

DROP TABLE Projects;
CREATE TABLE Projects (
    ProjectId INT PRIMARY KEY,
    ProjectName VARCHAR(50),
    EmployeeId INT
);

INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (1, 'Develop Ecommerse Website from Scratch', 1003);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (2, 'WordPress Website for our company', 1002);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (3, 'Manage our Company Servers', 1007);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (4, 'Hosting account is not working', 1009);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (5, 'MySQL database from my desktop application', 1010);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (6, 'Develop WordPress plugin for my business website', NULL);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (7, 'Migrate web application and database to new server', NULL);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (8, 'Android Application development', 1004);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (9, 'Hosting account is not working', 1001);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (10, 'MySQL database from my desktop application', 1008);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (11, 'Develop WordPress plugin for my business website', NULL);

DROP TABLE Address;
CREATE TABLE Address
(
    AddressId INT PRIMARY KEY,
    EmployeeId INT,
    Country VARCHAR(10),
    State VARCHAR(20),
    City VARCHAR(10)
);

INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (1, 1001, 'India', 'Odisha', 'BBSR');
INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (2, 1002, 'India', 'Maharashtra', 'Mumbai');
INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (3, 1003, 'India', 'Maharashtra', 'Pune');
INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (4, 1004, 'India', 'Odisha', 'Cuttack');
INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (5, 1005, 'India', 'Maharashtra', 'Nagpur');
INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (6, 1006, 'India', 'Odisha', 'Cuttack');

Right Outer Join Example in Oracle:

Our requirement is to retrieve the EmployeeId, FullName, Department, Gender, ProjectName from the Employee and Projects tables. The SQL query should return the following result set. As you can see in the below image, we want to retrieve all the Project details from the Projects table irrespective of whether the project is assigned to any employee or not.

Following is the SQL Query that is Right Outer Joining the Employee and Projects tables based on the EmployeeID column and will give you the required results as shown in the above image.

SELECT Projects.ProjectId, ProjectName, Employee.EmployeeID, FullName, Department, Gender
FROM Employee
RIGHT OUTER JOIN Projects
ON Employee.EmployeeID = Projects.EmployeeId;

If you look at the output, here, we got 11 rows i.e. all the records from the Projects table. Those Projects which is not assigned to any employee will have null values. So, this proves, the right outer join in Oracle will retrieve all the matching rows from both the table as well as non-matching rows from the right-hand side table. In our example, Projects is the left-hand side table.

In Oracle, instead of using the RIGHT OUTER JOIN keyword, you can also use the RIGHT JOIN keyword as shown in the below SQL Query and it will also give you the same result as the previous SQL Query.

SELECT Projects.ProjectId, ProjectName, Employee.EmployeeID, FullName, Department, Gender
FROM Employee
RIGHT JOIN Projects
ON Employee.EmployeeID = Projects.EmployeeId;

Right Outer Join with Where Condition:

Let us understand how to use Right Outer Join in Oracle with the WHERE filtering condition. Our requirement is to retrieve all the Projects along with the Employee details from the Employee and Projects tables by using the Right Outer Join where the gender is Male. The following is the SQL Script.

SELECT Projects.ProjectId, ProjectName, Employee.EmployeeID, FullName, Department, Gender
FROM Employee
RIGHT JOIN Projects
ON Employee.EmployeeID = Projects.EmployeeId
WHERE Employee.Gender = 'Male';

Once you execute the above SQL query, you will get the following output which includes only the Male employees.

How to retrieve only the non-matching rows from the left table?

There are three projects which are not assigned to any employee. So, what our requirement is, we want to retrieve only those three projects from the Projects table. So, basically, we want to retrieve only the non-matching records from the right-side table. The query should return the following result set.

The following Left Outer Join Query with the where clause does the same.

SELECT Projects.ProjectId, ProjectName, Employee.EmployeeID, FullName, Department, Gender
FROM Employee
RIGHT JOIN Projects
ON Employee.EmployeeID = Projects.EmployeeId
WHERE Projects.EmployeeId IS NULL;

Joining three Tables in Oracle using Right Outer Join:

It is also possible in Oracle to join more than two tables. Let us understand how to JOIN three tables in Oracle using Right Outer Join. The following is the syntax in Oracle to join three tables using Right Outer Join.

Right Outer Joining Employee, Projects, and Address tables

Now, our requirement is to fetch the details of all employees along with the project name and address if they have one. The following SQL Query Right Outer Joining Employee, Projects, and Address tables to fetch the Employee details along with their projects and address.

SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName, Country, State, City
FROM Employee
RIGHT OUTER JOIN Projects ON Employee.EmployeeID = Projects.EmployeeId
RIGHT JOIN Address ON Projects.EmployeeID = Address.EmployeeId;

Once you execute the above SQL query, you will get the following result set.

In the next article, I am going to discuss Full Outer Join in Oracle with examples. Here, in this article, I try to explain the Right Outer Join in Oracle with Examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.