Self Join in Oracle

Self Join in Oracle with Examples

In this article, I am going to discuss the Self Join in Oracle with Examples. Please read our previous article where we discussed CROSS Join in Oracle with Examples. At the end of this article, you will understand what is Self-Join and when and how to use Self Join in Oracle with Examples.

What is Self-Join in Oracle?

The Self Join in Oracle is nothing but joining a table by itself. We need to use Self Join when we have some relations between the columns of the same table. If this is not clear at the moment then don’t worry, we will discuss this with an example.

When you are implementing the self-join mechanism in Oracle, then you have to create the alias for the table name. You can create any number of aliases for a single table name in Oracle. Aliases are nothing but the alternative name given to a table. The Self Join is not a different kind of join. The Self Join is like any other join except that the two instances of the same table will be joined together. So, it can be classified as any type of join, such as

  1. Inner Join
  2. Outer (Left, Right, Full) join
  3. Cross Join
Example: Self Join in Oracle

We are going to use the following Employee table to understand the Self Join concept in Oracle.

What is Self-Join in Oracle?

Please use the following SQL script to create the Employee table and populate the Employee table with the required test data.

DROP TABLE Employee;

CREATE TABLE Employee
(
  EmployeeID INT,
  FullName VARCHAR(20),
  Gender VARCHAR(10),
  ManagerID INT
);

INSERT INTO Employee VALUES(1, 'Pranaya', 'Male', 3);
INSERT INTO Employee VALUES(2, 'Priyanka', 'Female', 1);
INSERT INTO Employee VALUES(3, 'Preety', 'Female', NULL);
INSERT INTO Employee VALUES(4, 'Anurag', 'Male', 1);
INSERT INTO Employee VALUES(5, 'Sambit', 'Male', 1);
INSERT INTO Employee VALUES(6, 'Rajesh', 'Male', 3);
INSERT INTO Employee VALUES(7, 'Hina', 'Female', 3);

The Employee table contains information for both normal employees as well as managers of that employee which is the ManagerId. So, in order to find out the managers of each employee, we need a Self-Join because both Manager and Employee exist in the same table.

Left Self-Join Example in Oracle:

A MANAGER is also an EMPLOYEE. Both the NORMAL EMPLOYEE and MANAGER records are present in the same Employee table. So, here we need to join the table Employee with itself using different alias names, let say, E for Employee and M for Manager. Here we are going to use the Left Outer Join which will get the records with ManagerId NULL. You can see in the output that Preety’s record is also retrieved, but the MANAGER is NULL.

SELECT E.FullName as Employee, M.FullName as Manager
FROM Employee E
LEFT OUTER JOIN Employee M
ON E.ManagerId = M.EmployeeId;

Once you execute the above query, you will get the following output.

Left Self-Join Example in Oracle

Inner Self Join Example in Oracle:

Now, we are going to use Inner Self Join to join two instances of the same Employee table which will not retrieve the records with ManagerId NULL. You can see in the output that Preety’s record is not retrieved.

SELECT E.FullName as Employee, M.FullName as Manager
FROM Employee E
INNER JOIN Employee M
ON E.ManagerId = M.EmployeeId;

Once you execute the above SQL Query, you will get the below result set.

Inner Self Join Example in Oracle

Cross Self Join Example:

Now, we are going to use Cross Self Join to join two instances of the same Employee table. In this case, each record of instance will be multiplied with each other of other instances.

SELECT E.FullName as Employee, M.FullName as Manager
FROM Employee E
CROSS JOIN Employee M;

In the next article, I am going to discuss NATURAL JOIN in Oracle with Examples. Here, in this article, I try to explain the Self 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.

Leave a Reply

Your email address will not be published.