SQL Self Join

A SELF JOIN is a regular join, but the table is joined with itself.

Syntax

SELECT a.column_name, b.column_name
FROM table_name a, table_name b
WHERE condition;

Example: SELF JOIN

SELECT a.name AS Employee, b.name AS Manager
FROM employees a, employees b
WHERE a.manager_id = b.id;

Sample Table

Consider the following employees table:

| id | name        | manager_id |
|----|-------------|------------|
| 1  | John Doe    | 2          |
| 2  | Jane Smith  | 4          |
| 3  | Mike Johnson| 2          |
| 4  | Emily Davis | NULL       |

Result of SELF JOIN

| Employee     | Manager     |
|-------------|-------------|
| John Doe    | Jane Smith  |
| Mike Johnson| Jane Smith  |
| Jane Smith  | Emily Davis |

All employees and their corresponding managers are returned. If an employee does not have a manager, they are not included in the result.