Updated February 28, 2023
Introduction to Oracle Self Join
Oracle self-join is a type of join such as joining itself. It means that each row of the table is combined with itself and every other row of the table and oracle self-join can also be described as a join of two copies (There is no actual copy of the table from database) based on some join condition which is provided in the query to extract and return only those rows from the table which satisfies the given condition.
Syntax
Let us now look into the syntax of the Oracle Self join in the oracle database.
SELECT a.column_name, b.column_name...
FROM table a, table b
WHERE a.common_condition = b.common_condition;
Parameters:
- column_name: It refers to the name of the columns we want to extract.
- a,b: These are the alias of the same table since it is self join
- Common_condition: The condition that needs to be satisfied.
How does Oracle Self Join work?
Oracle Self Join is a special type of join unlike other joins where two different tables are joined based on some common condition but in the case of self join it is a unary relationship ( a table is joined with itself). A self join works by specifying each row of the table is combined with itself and also every other row of the same table. In other, to simpler words we can say that a self-join compares rows within a table based on a condition to query data.
Examples to Implement Oracle Self Join
To get a better understanding let us go through some examples:
Example #1
Self Join with WHERE clause: In the first example we are going to return the name of each employee along with the name of the employee’s manager from the employee table. We are going to use self join to achieve this result. Let us look at the query below:
Code:
SELECT e1.name employee, e2.name manager
FROM employee e1, employee e2
WHERE e2.employee_id = e1.manager_id
Order BY employee;
Output:
Explanation: In the above example the employee table gets called two times based on its two aliases e1 and e2. The rows are compared based on the join condition and the employee name and manager name is returned. As we can see in the output, the query is executed successfully and it shows both the columns with employee name and their respective manager name.
Example #2
In the second example of using where clause to perform self join we will use self join to match the employees present in the employee table who share the same city. We are going to retrieve the employee names and their common city. Let us look at the query.
Code:
SELECT e1.name AS EmployeeName1, e2.name AS EmployeeName2, e1.City
FROM employee e1, employee e2
WHERE e1.EMPLOYEE_ID <> e2.EMPLOYEE_ID
AND e1.City = e2.City
ORDER BY e1.City;
Output:
Explanation: In the above example if we see the table is called two times based on the aliases e1 and e2 and depending on the condition the value which satisfies both the conditions is returned. The condition, in this case, is first it checks that the employee id of the table e1 and table e2 (both are aliases of the same table employee) are not the same but their cities must be the same to satisfy the overall predicate.
One thing to remember is that both the condition should satisfy. The above screenshot shows that the query executed successfully and the result set contains three columns the first column shows the name from the e1 alias of table employee, the second column shows the name from e2 alias of table employee and the third column shows us the name of the city.
Example #3
Self joins using ON clause: In this example, we are going to use the ON clause to get the name of the employee’s manager. The requirement is the same as before that we are going to extract two columns one with the employee name and the other with its corresponding manager name but we are going to use the ON clause with a self join instead of WHERE clause as used in the earlier example. Let us look at the query:
Code:
SELECT e1.name employee, e2.name manager
FROM employee e1 JOIN employee e2
ON e2.employee_id = e1.manager_id
Order BY employee;
If we look at the above example, the ON clause has been used before the condition which compares each row present in the table based on the condition written after the ON clause.
Output:
Explanation: As we can see in the above screenshot the result shows both the columns with employee name and their respective manager name.
Example #4
Self joins using LEFT JOIN: In this query, we are going to use left join to retrieve employee name and their corresponding manager name from the employee table present in the database. Let us look at the query below:
Code:
SELECT e1.name employee , e2.name manager
FROM employee e1 LEFT JOIN employee e2
ON e2.employee_id = e1.manager_id
Order BY employee;
In this example, if we see the query references the table twice (one for employee and another for manager). The join predicate is used to compares each row present in the table and based on that shows the result.
Output:
Explanation: As we can see in the screenshot the query gets executed successfully and since we have used INNER JOIN in our query we also get to see the employees with no manager.
Conclusion
In this article we discussed the definition of Oracle self join along with the syntax and how the self joins works in the database. To understand better we also went through a few cases and examples.
Recommended Articles
This is a guide to Oracle Self Join. Here we discuss an introduction to Oracle Self Join, Syntax, How does it work, Examples with code and output. You can also go through our other related articles to learn more –
