What Does Self-Join Mean?
A self-join, also known as an inner join, is a structured query language (SQL) statement where a queried table is joined to itself. The self-join statement is necessary when two sets of data, within the same table, are compared.
Techopedia Explains Self-Join
As an example, there is a table named EMPLOYEES that contains three columns:
- employee name
- employee ID
- employee manager’s ID
Because the managers are also employees, the MANAGER_ID column also contains the ID of another employee that is also the manager. To write a query to extract the employee and manager names and IDs, the table must be logically split in half to run two separate queries: employees (first table) and managers (second table). This is achieved by running the following sample SQL query:
SELECT a.employee_name, b.employee_name as Manager_name
FROM employees as a, employees as b
WHERE a.manager_id = b.employee_id
Understanding the self-join concept and circumstances is essential to grasping the above SQL statement.
In the example, the second EMPLOYEES table is given the alias b, which actually is a subset of the full EMPLOYEES table. However, the WHERE condition forces the first EMPLOYEES table to query the employee manager in the second EMPLOYEES table.