The SELF-JOIN is relationship where the match fields are in same table. Here a table is joined with itself when table has a FOREIGN KEY which references its own PRIMARY KEY. Since there is only one table being joined, it is said to have unary relationship. It extracts a sub-set of current table based on the relationship created between records of same table.

SELF-JOIN is executed as if two copies of same table were made and joined, based on satisfying conditions. An ALIAS name is given to the table to distinguish the two column names of same table. Syntax for INNER JOIN is:

Syntax

SELECT a.columnName, b.columnName ? 

FROM table1 a, table1 b 

WHERE a.commonField = b.commonField;

Here table1 is named as 'a' and 'b' after 'FROM' to distinguish the same table.

Consider a table having employee id(EMP_ID), employee name(EMP_NAME), employee team(EMP_TEAM), employee supervisor(EMP_SUPV) as columns. To get a table containing employee name and his supervisor name we perform INNER JOIN of the TABLE. EMP_SUPV is FOREIGN_KEY referencing EMP_ID the PRIMARY_KEY. Every employee can be supervisor of other or to herself (i.e. no supervisor to her).

SELECT a.emp_id AS "EMP_ID", a.emp_name AS "Employee Name", 

b.emp_id AS "Supervisor ID", b.emp_name AS "Supervisor Name" 

FROM employee a, employee b 

WHERE a.emp_supv = b.emp_id;