SELF JOIN

---

Oracle - Self Join

Self Join is a specific type of Join. In Self Join, a table is joined with itself (Unary relationship). A self join simply specifies that each rows of a table is combined with itself and every other row of the table.

Syntax :

SELECT a.column_name, b.column_name...   
FROM table1 a, table1 b   
WHERE a.common_filed = b.common_field;   

Oracle SELF JOIN Example

NAME	AGE	ADDRESS	SALARY
Ajaashi	21	Hyderabad	25000
Kaushik	22	Secunderabad	32000
Maulik	23	Mumbai	56000
Nikarsh	24	Karnataka	12000

Join this table using SELF JOIN as follows:

SELECT  a.name, b.age, a.SALARY  
FROM CUSTOMERS a, CUSTOMERS b  
WHERE a.SALARY < b.SALARY; 

Output :

NAME	AGE	SALARY
Ajaashi	22	25000
Ajaashi	23	25000
Kaushik	23	32000
Nikarsh	21	12000
Nikarsh	22	12000
Nikarsh	23	12000