What is a SELF JOIN?
A SELF JOIN is a JOIN where a table is joined with itself.
SQL treats the same table as two separate tables by using different
aliases. This is useful when rows in the same table are related to
each other.
Basic SELF JOIN Syntax
A SELF JOIN joins a table with itself. Table aliases are used to treat the
same table as two separate instances. In this example, employees are
connected to their managers using the manager_id column.
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
employee_id INTEGER,
employee_name TEXT,
manager_id INTEGER
);
INSERT INTO employees VALUES
(1, 'Anika', NULL),
(2, 'Rohan', 1),
(3, 'Meera', 1),
(4, 'Kabir', 2);
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
ORDER BY e.employee_id;Finding Employees and Their Managers
A common use of SELF JOIN is representing an employee-manager relationship when both employees and managers are stored in the same table.
CREATE TABLE staff (
staff_id INTEGER,
staff_name TEXT,
supervisor_id INTEGER
);
INSERT INTO staff VALUES
(101, 'Isha', NULL),
(102, 'Dev', 101),
(103, 'Tanya', 101),
(104, 'Arjun', 102),
(105, 'Naina', 103);
SELECT
s.staff_name AS staff_member,
p.staff_name AS supervisor
FROM staff AS s
LEFT JOIN staff AS p
ON s.supervisor_id = p.staff_id
ORDER BY s.staff_id;SELF JOIN with INNER JOIN
An INNER JOIN can be used as a SELF JOIN when you only want rows that have a matching related record.
CREATE TABLE team_members (
member_id INTEGER,
member_name TEXT,
buddy_id INTEGER
);
INSERT INTO team_members VALUES
(1, 'Aarav', 2),
(2, 'Mira', 1),
(3, 'Vihaan', 4),
(4, 'Diya', 3),
(5, 'Kunal', NULL);
SELECT
a.member_name AS member,
b.member_name AS buddy
FROM team_members AS a
INNER JOIN team_members AS b
ON a.buddy_id = b.member_id
ORDER BY a.member_id;Finding Products with the Same Price
SELF JOIN can also compare different rows in the same table. For example, you can find products that have the same price.
CREATE TABLE products (
product_id INTEGER,
product_name TEXT,
price INTEGER
);
INSERT INTO products VALUES
(1, 'Backpack', 1200),
(2, 'Desk Lamp', 800),
(3, 'Water Bottle', 800),
(4, 'Notebook Set', 500),
(5, 'Travel Pouch', 1200);
SELECT
p1.product_name AS product_one,
p2.product_name AS product_two,
p1.price
FROM products AS p1
INNER JOIN products AS p2
ON p1.price = p2.price
AND p1.product_id < p2.product_id
ORDER BY p1.price, p1.product_id;Comparing Products in the Same Category
A SELF JOIN can compare products belonging to the same category while preventing a product from being compared with itself.
CREATE TABLE catalog (
product_id INTEGER,
product_name TEXT,
category TEXT
);
INSERT INTO catalog VALUES
(1, 'Alpha Phone', 'Mobile'),
(2, 'Beta Phone', 'Mobile'),
(3, 'Gamma Tablet', 'Tablet'),
(4, 'Delta Tablet', 'Tablet'),
(5, 'Echo Camera', 'Camera');
SELECT
p1.product_name AS product_one,
p2.product_name AS product_two,
p1.category
FROM catalog AS p1
INNER JOIN catalog AS p2
ON p1.category = p2.category
AND p1.product_id < p2.product_id
ORDER BY p1.category, p1.product_id;Finding People from the Same City
SELF JOIN can compare rows based on a common value such as city, department, category, or any other column.
CREATE TABLE travelers (
traveler_id INTEGER,
traveler_name TEXT,
city TEXT
);
INSERT INTO travelers VALUES
(1, 'Riya', 'Delhi'),
(2, 'Karan', 'Mumbai'),
(3, 'Simran', 'Delhi'),
(4, 'Aman', 'Pune'),
(5, 'Neha', 'Mumbai');
SELECT
t1.traveler_name AS traveler_one,
t2.traveler_name AS traveler_two,
t1.city
FROM travelers AS t1
INNER JOIN travelers AS t2
ON t1.city = t2.city
AND t1.traveler_id < t2.traveler_id
ORDER BY t1.city, t1.traveler_id;SELF JOIN with a Salary Comparison
You can compare two employees from the same table based on their salaries or other numeric values.
CREATE TABLE workers (
worker_id INTEGER,
worker_name TEXT,
salary INTEGER
);
INSERT INTO workers VALUES
(1, 'Nikhil', 45000),
(2, 'Pooja', 62000),
(3, 'Rahul', 52000),
(4, 'Sneha', 70000);
SELECT
w1.worker_name AS lower_paid,
w1.salary AS lower_salary,
w2.worker_name AS higher_paid,
w2.salary AS higher_salary
FROM workers AS w1
INNER JOIN workers AS w2
ON w1.salary < w2.salary
AND w1.worker_id < w2.worker_id
ORDER BY w1.worker_id, w2.salary;Finding Related Categories
SELF JOIN can compare rows that share the same group. The example below finds different courses offered by the same department.
CREATE TABLE courses (
course_id INTEGER,
course_name TEXT,
department TEXT
);
INSERT INTO courses VALUES
(1, 'Algebra', 'Mathematics'),
(2, 'Calculus', 'Mathematics'),
(3, 'Physics', 'Science'),
(4, 'Chemistry', 'Science'),
(5, 'History', 'Arts');
SELECT
c1.course_name AS course_one,
c2.course_name AS course_two,
c1.department
FROM courses AS c1
INNER JOIN courses AS c2
ON c1.department = c2.department
AND c1.course_id < c2.course_id
ORDER BY c1.department, c1.course_id;SELF JOIN for Hierarchical Data
SELF JOIN is particularly useful for hierarchical data such as employees and managers, categories and parent categories, or folders and parent folders.
CREATE TABLE folders (
folder_id INTEGER,
folder_name TEXT,
parent_id INTEGER
);
INSERT INTO folders VALUES
(1, 'Documents', NULL),
(2, 'Projects', 1),
(3, 'Photos', 1),
(4, 'SQL Files', 2),
(5, 'Images', 3);
SELECT
child.folder_name AS child_folder,
parent.folder_name AS parent_folder
FROM folders AS child
LEFT JOIN folders AS parent
ON child.parent_id = parent.folder_id
ORDER BY child.folder_id;SELF JOIN with Multiple Conditions
Multiple conditions can be used when comparing rows from the same table. This example finds products that belong to the same category and have the same stock status.
CREATE TABLE inventory_items (
item_id INTEGER,
item_name TEXT,
category TEXT,
stock_status TEXT
);
INSERT INTO inventory_items VALUES
(1, 'Desk Chair', 'Furniture', 'Available'),
(2, 'Office Table', 'Furniture', 'Available'),
(3, 'Floor Lamp', 'Lighting', 'Available'),
(4, 'Wall Lamp', 'Lighting', 'Out of Stock'),
(5, 'Book Shelf', 'Furniture', 'Out of Stock');
SELECT
a.item_name AS item_one,
b.item_name AS item_two,
a.category,
a.stock_status
FROM inventory_items AS a
INNER JOIN inventory_items AS b
ON a.category = b.category
AND a.stock_status = b.stock_status
AND a.item_id < b.item_id
ORDER BY a.category, a.item_id;Common Uses of SELF JOIN
| Use Case | Example | Typical Condition |
|---|---|---|
| Employee hierarchy | Employee and manager | manager_id = employee_id |
| Duplicate comparison | Products with same price | price = price |
| Group comparison | People in same city | city = city |
| Hierarchical data | Folder and parent folder | parent_id = folder_id |
| Row comparison | Compare salaries | salary < salary |
Advantages of SELF JOIN
- Allows a table to be compared with itself.
- Useful for representing employee-manager relationships.
- Can identify related rows within the same table.
- Useful for comparing prices, salaries, categories, or other values.
- Works well with hierarchical data.
Best Practices
- Always use different aliases for the two instances of the table.
- Use meaningful aliases such as
employeeandmanagerwhen appropriate. - Use a condition such as
id < idwhen comparing pairs to avoid duplicate combinations. - Use LEFT JOIN when you also want rows without a related record.
- Make sure the JOIN condition correctly represents the relationship between the rows.
The SELF JOIN allows you to treat one table as two separate tables and compare its rows. It is especially useful for hierarchical relationships, duplicate detection, and comparing related records stored in the same table.
🧪 Test Your SQL Code
Edit the SQL code on the left and click "Run Code" to see the result on the right.
Click "Run Code" to see the result here.