In SQL, the self join is a join technique where we join a table with itself. Other join types will join a table with another table, but the self join simply joins with itself.
Self joins can be useful when working with hierarchical or recursive data within a single table.
For example, we can use self joins to create:
- Organizational hierarchies
- Friend relationships in social networks
- Category trees in e-commerce systems
- Task dependencies in project management
- Geographical hierarchies (e.g., Country > State > City)
- File and folder structures in file systems
Below are examples of two of these use-cases.
Organizational Hierarchical Example
Let’s start with an organizational hierarchical example. We’ll use an employees table, and we’ll use it to find out who each employee’s manager is. This is on the basis that each employee’s manager is in the same table (because the managers are also employees).
First, let’s set up our table and populate it with some data:
-- Create the employees table
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
manager_id INT
);
-- Insert sample data
INSERT INTO employees (employee_id, first_name, last_name, manager_id)
VALUES
(1, 'Hank', 'Blankfield', NULL),
(2, 'Boz', 'Whittle', 1),
(3, 'Mike', 'Johnson', 1),
(4, 'Emily', 'Fields', 2),
(5, 'David', 'Pease', 2),
(6, 'Sesh', 'Lee', 3),
(7, 'Fitch', 'Davis', 3);This script creates an employees table with a self-referencing manager_id column. The manager_id refers to the employee_id of the employee’s manager.
Now, let’s use a self join to retrieve each employee along with their manager’s name:
SELECT
e.employee_id,
e.first_name AS employee_first_name,
e.last_name AS employee_last_name,
m.first_name AS manager_first_name,
m.last_name AS manager_last_name
FROM
employees e
LEFT JOIN
employees m ON e.manager_id = m.employee_id
ORDER BY
e.employee_id;This query joins the employees table with itself, using aliases e for employees and m for managers. The LEFT JOIN ensures that employees without a manager (like Hank Blankfield, the top-level manager) are included in the results.
The query produces the following output:
employee_id employee_first_name employee_last_name manager_first_name manager_last_name
----------- ------------------- ------------------ ------------------ -----------------
1 Hank Blankfield null null
2 Boz Whittle Hank Blankfield
3 Mike Johnson Hank Blankfield
4 Emily Fields Boz Whittle
5 David Pease Boz Whittle
6 Sesh Lee Mike Johnson
7 Fitch Davis Mike Johnson
So we can see that we were able to distinguish between each employee and their manager by using the m alias in the SELECT list. This allowed us to put managers in their own columns, separate from the employees, even though they’re all from the same table.
This result set clearly shows the hierarchical relationship between employees and their managers.
Category Tree (Multiple Self Joins)
Let’s create a more complex example using a hierarchical product category structure for an e-commerce platform. This example will demonstrate how to use self joins to navigate through multiple levels of categories.
First, let’s create and populate our sample table:
-- Drop the table if it exists
DROP TABLE IF EXISTS product_categories;
-- Create the product_categories table
CREATE TABLE product_categories (
category_id INT PRIMARY KEY,
category_name VARCHAR(100),
parent_category_id INT,
category_level INT
);
-- Insert sample data
INSERT INTO product_categories (category_id, category_name, parent_category_id, category_level)
VALUES
(1, 'Electronics', NULL, 1),
(2, 'Computers', 1, 2),
(3, 'Smartphones', 1, 2),
(4, 'Laptops', 2, 3),
(5, 'Desktop PCs', 2, 3),
(6, 'Gaming Laptops', 4, 4),
(7, 'Business Laptops', 4, 4),
(8, 'Android Phones', 3, 3),
(9, 'iOS Phones', 3, 3),
(10, 'Accessories', 1, 2),
(11, 'Phone Cases', 10, 3),
(12, 'Chargers', 10, 3);This script creates a product_categories table with a self-referencing parent_category_id column and a category_level column to indicate the depth of each category.
Now, let’s use multiple self joins to retrieve the full category path for each item:
SELECT
c1.category_id,
c1.category_name,
CONCAT_WS(' > ',
c4.category_name,
c3.category_name,
c2.category_name,
c1.category_name
)
AS full_category_path
FROM
product_categories c1
LEFT JOIN
product_categories c2 ON c1.parent_category_id = c2.category_id
LEFT JOIN
product_categories c3 ON c2.parent_category_id = c3.category_id
LEFT JOIN
product_categories c4 ON c3.parent_category_id = c4.category_id
ORDER BY
c1.category_id;Result:
category_id category_name full_category_path
----------- ---------------- ----------------------------------------------------
1 Electronics Electronics
2 Computers Electronics > Computers
3 Smartphones Electronics > Smartphones
4 Laptops Electronics > Computers > Laptops
5 Desktop PCs Electronics > Computers > Desktop PCs
6 Gaming Laptops Electronics > Computers > Laptops > Gaming Laptops
7 Business Laptops Electronics > Computers > Laptops > Business Laptops
8 Android Phones Electronics > Smartphones > Android Phones
9 iOS Phones Electronics > Smartphones > iOS Phones
10 Accessories Electronics > Accessories
11 Phone Cases Electronics > Accessories > Phone Cases
12 Chargers Electronics > Accessories > Chargers
I tested this query in SQL Server, MySQL, MariaDB, and PostgreSQL. You may need to tweak it, depending on your RDBMS.
The query uses multiple self joins to reconstruct the full category path for each item. We used the CONCAT_WS() function to concatenate the categories, and to insert the > character in between them.
Subquery & Join Basics
If you prefer to see some less complex subqueries, take a look at my article Understanding the SQL Subquery.
To learn more about joins, see my SQL Joins Tutorial.