A SELF JOIN in SQL is a join where a table is joined with itself. This means that you treat the table as two separate entities within the same query, allowing you to compare or combine rows from the same table based on a specified condition.

Syntax

SELECT t1.column_name, t2.column_name
FROM table_name t1
JOIN table_name t2 
ON t1.common_column = t2.common_column;

Example

Consider a table named employees with columns such as employee_id and manager_id, where manager_id refers to the employee_id of the employee’s manager.

Image scaled to 45%

Now, let’s perform a SELF JOIN to retrieve information about employees and their respective managers:

Database Exercise

Database Schema:

-- Database schema would be rendered here

Exercise Script:

-- Exercise script would be rendered here

Available actions: Execute

Result

Image scaled to 45%

In this example, the SELF JOIN is performed on the manager_id column, establishing a relationship between employees and their managers within the same employees table. The result set shows the names of employees along with their corresponding managers.