What is Self-Join
in SQL?
A self-join is a join
in which a table is joined with itself (which is also called Unary
relationships), especially when the table has a FOREIGN KEY which references
its own PRIMARY KEY. To join a table it-self means that each row of the table
is combined with itself and with every other row of the table.
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.
The syntax of the
command for joining a table to itself is almost same as that for joining two
different tables. To distinguish the column names from one another, aliases for
the actual the table name are used, since both the tables have same name. Table
name aliases are defined in the FROM clause of the SELECT statement. See the syntax:
SELECT a.column_name, b.column_name…..
FROM table1 a, table1 b
WHERE a.common_filed = b.common_field;
Example
of SQL SELF JOIN
In the following example we will use the table EMPLOYEE twice
and in order to do this we will use the alias of the table.
To get the list of employees and their Manager the following sql
statement has used:
SELECT a.emp_id AS
"Emp_ID",a.emp_name
AS "EmployeeName",
b.emp_id
AS "Manager ID",b.emp_name AS "ManagerName"
FROM employee a, employee b
WHERE a.emp_supv = b.emp_id; 