How to use the Oracle self-join feature?

The self-join function in Oracle databases can be achieved by using aliases. Self-join refers to connecting a table with itself within a query, typically used to compare data from different rows within the same table.

Here is an example query using self-join:

SELECT
    e1.employee_id,
    e1.first_name,
    e2.employee_id,
    e2.first_name
FROM
    employees e1,
    employees e2
WHERE
    e1.manager_id = e2.employee_id;

In the above query, the employees table is self-joined twice using the aliases e1 and e2, respectively.

The join condition e1.manager_id = e2.employee_id is used to compare the manager_id column in table e1 with the employee_id column in table e2 in order to find the direct supervisor for each employee.

The query results will include the employee_id and first_name of each employee, as well as the employee_id and first_name of their supervisor.

It is important to note that self-joins can lead to a decrease in query performance, especially when dealing with large amounts of data. Therefore, when using self-joins, optimization should be done based on actual needs and limiting the number of query results.