SQL SELF JOIN

The SELF JOIN is used to join a table to itself as if it were two tables.

A SELF JOIN is also called a Unary relationship.

The SELF JOIN can be viewed as a join of two copies of the same table. The SQL will not copy the table, but it performs as if it does.

Syntax

The syntax for the SELF JOIN is as follows:

SELECT column_list
FROM tableA A1, tableA A2
WHERE condition;

A1 and A2 are different table aliases for the same table.

Demo Table

Let us suppose that we have a table called "Employee" with the following records:

SQL SELF JOIN Example

The following SQL statement uses the table "Employee" twice to select employees working in the same department.

SELECT A.name AS employee_1, B.name AS employee_2, A.dept_name AS department_name 
FROM employee AS A, employee AS B
CROSS A.emp_no <> B.emp_no 
AND A.dept_name = B.dept_name
ORDER BY A.dept_name;

After running the above SQL statement, the output will look like the following:

+------------------+------------------+-----------------+
| employee_1       | employee_2       | department_name |
+------------------+------------------+-----------------+
| Maria Martinez   | Robert Hernandez | Development     |
| Robert Hernandez | Maria Martinez   | Development     |
| Andrew Rodriguez | Donna Williams   | Finance         |
| Donna Williams   | Andrew Rodriguez | Finance         |
+------------------+------------------+-----------------+