SELF JOIN
In this page:
What is a SELF JOIN?
A SELF JOIN joins a table to itself by referencing it twice with different aliases. This is useful for comparing rows within the same table, such as comparing an employee's salary to a colleague's in the same table.
Example: What is a SELF JOIN?
CREATE TABLE employees (id INT, name TEXT, salary INT);
INSERT INTO employees VALUES (1, 'Amit', 50000), (2, 'Priya', 60000);
SELECT a.name AS employee, b.name AS colleague
FROM employees a JOIN employees b ON a.id <> b.id;Managing Hierarchy
You can use a self join to show hierarchical relationships, like linking employees to their managers in an organization chart, where both the employee and manager rows live in the same employees table.
Example: Managing Hierarchy
CREATE TABLE employees (id INT, name TEXT, manager_id INT);
INSERT INTO employees VALUES (1, 'Rahul', NULL), (2, 'Amit', 1), (3, 'Priya', 1);
SELECT emp.name AS employee, mgr.name AS manager
FROM employees emp LEFT JOIN employees mgr ON emp.manager_id = mgr.id;Finding Consecutive Records
You can use a self join to find consecutive records or detect sequences of events in your data, such as finding two log entries from the same user within a short time window of each other.
Example: Finding Consecutive Records
CREATE TABLE logins (id INT, user_id INT, login_time TEXT);
INSERT INTO logins VALUES (1, 1, '10:00'), (2, 1, '10:05'), (3, 1, '11:00');
SELECT a.id, b.id
FROM logins a JOIN logins b ON a.user_id = b.user_id AND b.id = a.id + 1;Comparing Rows with Other Rows
Self joins make it easy to compare products or find items that share the same characteristics, like products within the same price range, by joining the products table to itself on a matching category or price bracket.
Example: Comparing Rows with Other Rows
CREATE TABLE products (id INT, name TEXT, category TEXT, price INT);
INSERT INTO products VALUES (1, 'Pen', 'Stationery', 10), (2, 'Pencil', 'Stationery', 12);
SELECT a.name, b.name
FROM products a JOIN products b ON a.category = b.category AND a.id < b.id;Advanced Path Exploration
You can perform multiple self joins on a table to trace multi-step paths, like flight layovers or manufacturing steps, where each join hops to the next step in a chain stored within a single table.
Example: Advanced Path Exploration
CREATE TABLE flights (id INT, origin TEXT, destination TEXT);
INSERT INTO flights VALUES (1, 'Delhi', 'Mumbai'), (2, 'Mumbai', 'Goa');
SELECT f1.origin, f1.destination, f2.destination AS final_stop
FROM flights f1 JOIN flights f2 ON f1.destination = f2.origin;Chapter Quiz — Complete all 10 topics to unlock
0/10 topics done
Complete these topics first: