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
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:
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.