Oracle self join | self join - oracle tutorial - sql tutorial
What is Oracle self join ?
- A self join is a join in which a table is joined with itself.
- To join a table itself means that each row of the table is combined with itself and with every other row of the table.
- The table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition.
- The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.
- To perform a self join, Oracle Database combines and returns rows of the table that satisfy the join condition
![Oracle self join query](https://wikitechy.com/tutorials/oracle/img\oracle-images\sql-self-join.png)
Oracle self join query
![self join](https://wikitechy.com/tutorials/oracle/img\oracle-images\self-join.png)
Syntax:
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_filed = b.common_field;
click below button to copy the code. By - oracle tutorial - team
Note: Rename at least one table name temporarily in the sql statement.
WHERE clause could be any given expression based on your requirement.
Sample Database:
- We have a table called wikitechy_employee with four fields (id, name, position, and city). It contains the following data:
![left outer join in oracle](https://wikitechy.com/tutorials/oracle/img\oracle-images\left-outer-join-in-oracle.png)
![joins in dbms](https://wikitechy.com/tutorials/oracle/img\oracle-images\joins-in-dbms.png)
Using select Statement we are selecting name and id from the table.
Again we have selected the name column from the same table using reference from name column
- We temporarily created a new table called wikitechy_employee c2 and joining them using join keyword.
- C1.Name <> c2.Name to ensure that we don’t get the same customer.
The output returns all the employees from the same city.