Question 1

What is a self join?

Accepted Answer

A self join is just a regular JOIN where both sides reference the same table. You must use aliases (e.g. e1, e2) so the database can distinguish which copy of the table each column comes from. Self joins are common for hierarchical data (employee-manager) and for finding pairs within the same table (e.g., employees in the same department).

Question 2

Employee-manager self join

Accepted Answer

The table is joined to itself: 'e' represents the employee and 'm' represents the manager. For Bob (manager_id=1), the join finds id=1 which is Alice. Same for Carol. Alice has NULL manager_id, so her row finds no match in the INNER JOIN and is excluded. Use LEFT JOIN if you want Alice in the result with NULL for manager.

Question 3

Self join with LEFT JOIN for hierarchy

Accepted Answer

LEFT JOIN preserves all rows in the left table even when no match exists in the right copy. Alice has no manager (NULL manager_id), so the join produces NULL for m.name - she still appears in the result. Bob and Carol both resolve to Alice as their manager.