EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Oracle Self Join

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Oracle Tutorial » Oracle Self Join

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.

Syntax

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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;

Popular Course in this category
Sale
Oracle Training (14 Courses, 8+ Projects)14 Online Courses | 8 Hands-on Projects | 120+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (9,290 ratings)
Course Price

View Course

Related Courses
Oracle DBA Database Management System Training (2 Courses)All in One Financial Analyst Bundle - 250+ Courses, 40+ Projects

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

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Oracle Tutorial
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - Oracle Course Training Learn More