10.7 SELF JOIN

Level: 10 | Version: v1.1 | Author: Meptrasoft

Overview

A SELF JOIN joins a table with itself.

It is useful when rows in the same table are related to each other, such as an employee and their manager.

EMPLOYEES
   ↓
SELF JOIN
   ↓
EMPLOYEE ↔ MANAGER

SELF JOIN → Join a table to itself.

SELF JOIN — Employee and Manager hierarchy — Figure 1: A SELF JOIN uses two aliases of the same table to connect related rows, such as employees and their managers.

Practice Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT REFERENCES employees(employee_id)
);

INSERT INTO employees
(employee_id, employee_name, manager_id)
VALUES
(1, 'Oliver', 3),
(2, 'Sophia', 3),
(3, 'Daniel', 4),
(4, 'Michael', NULL),
(5, 'Emily', 4);

Here, manager_id refers to another employee_id in the same table.

Example: Employee and Manager

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM employees e
JOIN employees m
    ON e.manager_id = m.employee_id;

Result:

employee	manager
Oliver	Daniel
Sophia	Daniel
Daniel	Michael
Emily	Michael

Here:

e → employee
m → manager

Both aliases refer to the same employees table.

How SELF JOIN Works

The relationship is:

employees e
     |
     | e.manager_id
     ↓
employees m
     |
     | m.employee_id

The join condition:

e.manager_id = m.employee_id

matches each employee's manager ID with another employee's ID.

Michael has manager_id = NULL, so he has no manager to match.

SELF JOIN vs Normal JOIN

Normal JOIN
→ Table A + Table B

SELF JOIN
→ Same Table + Same Table

The table is not duplicated physically. Aliases allow us to treat it as two logical instances in the query.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
SELF JOIN requires two physical tables	It uses the same table twice in the query
`e` and `m` are separate tables	They are aliases of the same table
SELF JOIN always returns every row	Unmatched rows can be excluded with an inner join
`manager_id` must always have a value	It can be `NULL` for employees without a manager
SELF JOIN creates duplicate table data	It only combines rows in the query result

Placement Quick Points

SELF JOIN
→ SAME TABLE TWICE

e
→ EMPLOYEE

m
→ MANAGER

e.manager_id = m.employee_id
→ MATCH RELATED ROWS

A SELF JOIN joins a table with itself.
Table aliases distinguish the two logical roles.
It is useful for hierarchical data such as employee-manager relationships.
The same table does not need to be physically duplicated.
NULL manager IDs represent employees without a matching manager in this example.

Interview Questions

What is a SELF JOIN?: A SELF JOIN joins a table with itself to compare or connect rows within the same table.
Why are aliases needed?: Aliases allow the same table to be referenced as two different logical roles.
Give a real-world example of SELF JOIN.: An employee-manager hierarchy is a common example.
Does SELF JOIN require two physical tables?: No. It uses the same table twice in the query.
Why doesn't Michael appear in the INNER JOIN result?: His manager_id is NULL, so there is no manager row to match.

Practice & Hands-On Exercises

Using the employees table:

Display each employee with their manager:

SELECT e.employee_name AS employee, m.employee_name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

Find employees who have a manager.
Explain why Michael is not returned by the INNER JOIN.
Identify the two aliases used in the query.
Explain the join condition e.manager_id = m.employee_id.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

SAME TABLE
   ↓
TWO ALIASES
   ↓
EMPLOYEE ↔ MANAGER

A SELF JOIN uses the same table with different aliases to connect related rows within that table.

End of SELF JOIN