EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Self Join
 

Oracle Self Join

Priya Pedamkar
Article byPriya Pedamkar

Updated February 28, 2023

Oracle Self Join

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

Note: One important point to note that we can use other joins like inner join and left join with Oracle self join.

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:

Oracle Self Join1

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:

Oracle Self Join2

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:

using ON clause

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:

Left Join

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 –

  1. Oracle COMMIT 
  2. Oracle Constraints
  3. Oracle UNIQUE Constraint
  4. Oracle Aliases

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - ENROLL NOW