SELF JOIN

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

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

Oracle SELF JOIN Example

Let's take a table "customers".

Oracle Self Join

Join this table using SELF JOIN as follows:

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

Output

Oracle Self Join 2