SELF JOIN
A SELF JOIN joins a table to itself. This is used for hierarchical data (employees and managers), comparisons within the same table (products in the same price range), or finding related pairs of rows (users in the same city).
SELF JOIN Use Cases
- Employee → Manager (same employees table, manager_id references id)
- Find pairs of products in the same price tier
- Compare each order to others from the same user
- Organizational hierarchies - categories, departments
- Requires table aliases - both sides need different names
SELF JOIN Examples
-- Users who signed up after John (compare users to each other)
SELECT
a.name AS user_name,
a.created_at,
b.name AS reference_user
FROM users a
JOIN users b ON b.name = 'John Doe'
WHERE a.created_at > b.created_at;
-- Products in the same price tier (self-join for pairs)
SELECT
p1.name AS product_1,
p2.name AS product_2,
p1.price
FROM products p1
JOIN products p2
ON ROUND(p1.price / 100) = ROUND(p2.price / 100)
AND p1.id < p2.id; -- avoid duplicates (A-B and B-A)
-- Employee-manager hierarchy
-- (hypothetical employees table)
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
manager_id INT REFERENCES employees(id)
);
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;Quick Quiz
Tip
Tip
Practice SELF JOIN in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Inner query runs first, results used by outer query. CTEs are often more readable.
Common Mistake
Warning
A common mistake with SELF JOIN is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of SELF JOIN from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- A SELF JOIN joins a table to itself.
- Employee → Manager (same employees table, manager_id references id)
- Find pairs of products in the same price tier
- Compare each order to others from the same user