In a database, sometimes records in the same table are related to each other.
For example, a university may maintain an employee table containing:
A manager is also an employee of the university. Therefore, both employee and manager information is stored in the same table.
How can we display each employee’s name along with their manager’s name?
We can use a SELF JOIN.
A SELF JOIN is a technique in which a table is joined with itself.
It allows us to compare or retrieve related records from the same table.
Real-life examples:
Important: SELF JOIN is not a separate JOIN keyword. We use a regular JOIN, such as INNER JOIN or LEFT JOIN, with two aliases for the same table.
Let’s create a table named employees.
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
employee_name VARCHAR(100),
designation VARCHAR(100),
manager_id INTEGER
);
Insert sample records:
INSERT INTO employees
(employee_id, employee_name, designation, manager_id)
VALUES
(1, 'Ahmed', 'Director', NULL),
(2, 'Ali', 'Manager', 1),
(3, 'Sara', 'Manager', 1),
(4, 'Usman', 'Developer', 2),
(5, 'Ayesha', 'Developer', 2),
(6, 'Hassan', 'Accountant', 3);
View all records:
SELECT * FROM employees;
Output:
| employee_id | employee_name | designation | manager_id |
|---|---|---|---|
| 1 | Ahmed | Director | NULL |
| 2 | Ali | Manager | 1 |
| 3 | Sara | Manager | 1 |
| 4 | Usman | Developer | 2 |
| 5 | Ayesha | Developer | 2 |
| 6 | Hassan | Accountant | 3 |
Look at the manager_id column.
The manager_id column refers to another employee’s employee_id in the same table.
| [Create organizational chart with Xamarin.iOS Diagram | Syncfusion](https://images.openai.com/static-rsc-4/xOavX1xrZI9S-m6vDfVE0uF4IyI9cH80O5-gHN_qwL7bpWH4tgV_2tSTN1NoIfw1Cysug5niPo6kUKokab7DdcjyYnOLlIv-Ri_eUgMXtynoGA3WqHJ0o-igV-6PaintWKdGI6EwI14NNTNm6zrhgWGCtRBYtS9vZm2A8KOuF6g?purpose=inline) |
Suppose we want to display employee names along with their manager names.
SQL Query:
SELECT
e.employee_name AS employee,
m.employee_name AS manager
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id = m.employee_id;
Output:
| employee | manager |
|---|---|
| Ali | Ahmed |
| Sara | Ahmed |
| Usman | Ali |
| Ayesha | Ali |
| Hassan | Sara |
Explanation:
employees AS e creates an alias named e for the employee records.employees AS m creates another alias named m for the manager records.ON e.manager_id = m.employee_id matches each employee’s manager ID with another employee’s ID.e.employee_name displays the employee’s name.m.employee_name displays the manager’s name.Remember: Both e and m refer to the same employees table.
In the previous example, Ahmed was not displayed because he does not have a manager.
If we want to display all employees, including employees without managers, we can use LEFT JOIN.
SQL Query:
SELECT
e.employee_name AS employee,
m.employee_name AS manager
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.employee_id;
Output:
| employee | manager |
|---|---|
| Ahmed | NULL |
| Ali | Ahmed |
| Sara | Ahmed |
| Usman | Ali |
| Ayesha | Ali |
| Hassan | Sara |
Explanation:
LEFT JOIN keeps all employees.We can retrieve additional information from the same table.
Example: Display employee names, their designations, and their manager names.
SELECT
e.employee_name AS employee,
e.designation AS employee_designation,
m.employee_name AS manager,
m.designation AS manager_designation
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.employee_id;
This query displays:
We can use the WHERE clause to filter SELF JOIN results.
Example: Display employees who report to Ahmed.
SELECT
e.employee_name AS employee,
m.employee_name AS manager
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id = m.employee_id
WHERE m.employee_name = 'Ahmed';
Output:
| employee | manager |
|---|---|
| Ali | Ahmed |
| Sara | Ahmed |
Explanation:
| Regular JOIN | SELF JOIN |
|---|---|
| Joins two or more different tables. | Joins a table with itself. |
| Usually combines related information from different tables. | Retrieves related records within the same table. |
| Uses aliases when useful. | Uses aliases to distinguish the two references to the same table. |
INNER JOIN or LEFT JOIN.Use the employees table to solve the following exercises.
Beginner Level
Intermediate Level
Advanced Level
e.manager_id = m.employee_id?Basic SELF JOIN:
SELECT
e.employee_name AS employee,
m.employee_name AS manager
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id = m.employee_id;
SELF JOIN with LEFT JOIN:
SELECT
e.employee_name AS employee,
m.employee_name AS manager
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.employee_id;
Subqueries in PostgreSQL
Learn how to write a query inside another query to retrieve information, such as finding students whose fees are higher than the average student fee.