Self Joins and Multi-Table Queries

What Is It?

What Are Self Joins and Multi-Table Joins?

A self join is a JOIN where a table is joined with itself. This sounds strange — why join a table with itself? Because many real-world relationships live inside a single table. An employee's manager is also an employee. A reply on a forum references the parent post, which is also a post. A folder has a parent folder. To fetch both ends of the relationship in one query, we alias the same table twice.

A multi-table join chains three or more tables together in a single query. Real applications rarely answer a question using only two tables. A sales report needs customers, orders, and order_items. A student transcript needs students, enrollments, courses, and grades. JOIN is associative: you stack them left-to-right.

Sample Tables Used Throughout This Chapter

-- Employees with a self-referencing manager_id
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT,
    salary DECIMAL(10,2),
    department VARCHAR(30)
);

INSERT INTO employees VALUES
(1, 'Ananya Reddy',  NULL, 250000, 'CEO'),
(2, 'Rahul Verma',      1, 180000, 'Engineering'),
(3, 'Sneha Kapoor',     1, 170000, 'Marketing'),
(4, 'Arjun Pillai',     2, 120000, 'Engineering'),
(5, 'Divya Nair',       2, 190000, 'Engineering'),  -- earns more than her manager!
(6, 'Karthik Raja',     3, 110000, 'Marketing'),
(7, 'Pooja Menon',      3,  95000, 'Marketing');

-- For 3-table join examples
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(50), city VARCHAR(30));
CREATE TABLE orders    (id INT PRIMARY KEY, customer_id INT, order_date DATE);
CREATE TABLE order_items (id INT PRIMARY KEY, order_id INT, product_id INT, qty INT, price DECIMAL(8,2));
CREATE TABLE products  (id INT PRIMARY KEY, name VARCHAR(50), category VARCHAR(30));

INSERT INTO customers VALUES
(1, 'Aarav Kumar', 'Bengaluru'),
(2, 'Priya Sharma', 'Mumbai');

INSERT INTO orders VALUES
(101, 1, '2026-03-10'),
(102, 2, '2026-03-11');

INSERT INTO products VALUES
(1, 'Laptop',  'Electronics'),
(2, 'Mouse',   'Electronics'),
(3, 'Notebook','Stationery');

INSERT INTO order_items VALUES
(1001, 101, 1, 1, 55000),
(1002, 101, 2, 2,   500),
(1003, 102, 3, 5,   200);

Why Does It Matter?

Why Self Joins and Multi-Table Joins Matter

1. Hierarchical Data Is Everywhere

Org charts, category trees, threaded comments, file systems, geographical regions. Every real system contains hierarchies. The cleanest way to store them in a relational database is a self-referencing foreign key (manager_id points to id in the same table). Self joins are how you query this structure at one level. Recursive CTEs (covered later) handle unlimited depth.

2. Real Queries Always Touch Multiple Tables

A well-normalized database has many narrow tables. A 'show me top 10 products sold in Bengaluru in March' query easily touches 4 tables: customers, orders, order_items, products. You cannot avoid multi-table JOINs by writing simpler queries — you must master them.

3. Self Join Is a Famous Interview Topic

The 'find employees earning more than their manager' question appears in SQL interviews at least as often as 'Nth highest salary.' Recruiters use it to test whether you understand that a single table can represent a graph.

4. Join Order Affects Performance, Not Results

Logically, (a JOIN b) JOIN c equals a JOIN (b JOIN c). But the optimizer picks an execution order based on statistics. Understanding how joins compose lets you write clean queries and trust the optimizer, while knowing when to force a different order with hints for pathological cases.

Detailed Explanation

Detailed Explanation

1. Self Join Basics — Alias the Same Table Twice

You cannot join a table with itself unless each reference has a distinct alias, because otherwise column names would be ambiguous.

-- List each employee with their manager's name
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Here e is the 'employee' role of the table and m is the 'manager' role. LEFT JOIN is used so that the CEO (who has no manager) still appears, with NULL for manager name.

Output on the sample data:

+---------------+--------------+
| employee      | manager      |
+---------------+--------------+
| Ananya Reddy  | NULL         |
| Rahul Verma   | Ananya Reddy |
| Sneha Kapoor  | Ananya Reddy |
| Arjun Pillai  | Rahul Verma  |
| Divya Nair    | Rahul Verma  |
| Karthik Raja  | Sneha Kapoor |
| Pooja Menon   | Sneha Kapoor |
+---------------+--------------+

2. Employees Earning More Than Their Manager

A classic interview problem:

SELECT e.name AS employee, e.salary AS emp_salary,
       m.name AS manager, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Result:

+------------+------------+-------------+------------+
| employee   | emp_salary | manager     | mgr_salary |
+------------+------------+-------------+------------+
| Divya Nair |  190000.00 | Rahul Verma |  180000.00 |
+------------+------------+-------------+------------+

The logic: join each employee to the row of their manager. Compare salaries. The CEO (no manager_id) is excluded by the INNER JOIN, which is correct because the CEO has no manager to compare to.

3. Going Beyond Two Levels — Preview of Recursive CTEs

Self join gives one level of hierarchy. To list all ancestors (manager's manager's manager) without knowing the depth, you need a recursive CTE (covered in the CTE chapter):

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 0 AS level FROM employees WHERE name = 'Pooja Menon'
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.level + 1
    FROM employees e JOIN chain c ON e.id = c.manager_id
)
SELECT * FROM chain;

A plain self join can only look up one level at a time.

4. Joining Three Tables

Chain JOINs left to right. Each JOIN adds a new table referring to something already in scope.

-- Total revenue per customer
SELECT c.name, SUM(oi.qty * oi.price) AS total
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.name;

Output:

+--------------+----------+
| name         | total    |
+--------------+----------+
| Aarav Kumar  | 56000.00 |
| Priya Sharma |  1000.00 |
+--------------+----------+

Aarav's total: 1*55000 + 2*500 = 56000. Priya's: 5*200 = 1000.

5. Joining Four Tables

-- Top-selling products overall
SELECT p.name AS product, SUM(oi.qty) AS units_sold, SUM(oi.qty * oi.price) AS revenue
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p     ON oi.product_id = p.id
GROUP BY p.id, p.name
ORDER BY revenue DESC;

6. Does Join Order Matter?

Results: no. INNER JOIN is associative and commutative (ignoring LEFT/RIGHT nuances). a JOIN b JOIN c produces the same rows regardless of how you group them.

Performance: yes, it can matter. The optimizer picks an execution plan based on row counts and indexes. Usually it gets this right. For complex queries with 5+ tables, you might inspect EXPLAIN output and consider hints. Beginners should trust the optimizer and write the most readable order.

7. Implicit (Old-Style) Joins — Avoid

Before SQL-92 formalized JOIN syntax, people wrote:

-- OLD STYLE (avoid)
SELECT c.name, o.id
FROM customers c, orders o
WHERE c.id = o.customer_id;

Why avoid? Three reasons:

1. Forget the WHERE and you get a cartesian product — millions of rows on a production DB.
2. LEFT/RIGHT/FULL OUTER JOIN cannot be expressed in this syntax.
3. Join condition and filter condition get mixed in WHERE, making intent unclear.

Always use explicit JOIN ... ON .... Some style guides even fail builds that use comma-joins.

8. NATURAL JOIN — Risky, Avoid

NATURAL JOIN automatically joins on all columns with the same name in both tables:

SELECT * FROM customers NATURAL JOIN orders;

Why risky? If someone adds a column to one table that happens to match an unrelated column in the other (say, both tables get a created_at), the NATURAL JOIN silently adds an extra condition and changes the result set. You get no warning. Explicit JOIN ... ON is always safer.

9. Self Join with Multiple Conditions

Combine conditions for richer queries.

-- Pairs of employees in the same department
SELECT e1.name AS emp1, e2.name AS emp2, e1.department
FROM employees e1
JOIN employees e2
  ON e1.department = e2.department
  AND e1.id < e2.id;
-- The id < id trick avoids duplicates (A,B) and (B,A) and self-pairs (A,A)

10. Multi-Table Join Pitfalls — Cartesian Blowup

A missing JOIN condition in a multi-table query silently blows up the row count. 1000 customers x 1000 orders x 1000 items = 1 billion rows. Always verify row counts after writing multi-table queries during development. Add SELECT COUNT(*) as a sanity check. Use LIMIT 10 while testing.

11. Filtering Early vs Late

Logically, you can put a filter in ON or WHERE (for INNER JOIN they are equivalent; for OUTER JOIN they differ). The optimizer usually pushes filters down to the earliest possible point. For readability, put JOIN conditions in ON and value filters in WHERE.

-- Preferred style
SELECT c.name, SUM(oi.qty * oi.price) AS total
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE c.city = 'Bengaluru'
  AND o.order_date >= '2026-03-01'
GROUP BY c.id, c.name;

Code Examples

SELECT e.name AS employee, e.salary, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

SELECT e.name AS employee, e.salary AS emp_salary,
       m.name AS manager, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

SELECT m.id, m.name AS manager, COUNT(e.id) AS reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY reports DESC;

SELECT c.name AS customer, o.id AS order_id, oi.qty, oi.price,
       (oi.qty * oi.price) AS line_total
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
ORDER BY c.id, o.id;

SELECT p.name AS product, p.category,
       SUM(oi.qty) AS units_sold,
       SUM(oi.qty * oi.price) AS revenue
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p     ON oi.product_id = p.id
GROUP BY p.id, p.name, p.category
ORDER BY revenue DESC;

-- Every pair of employees in the same department, without duplicates
SELECT e1.name AS emp1, e2.name AS emp2, e1.department
FROM employees e1
JOIN employees e2
  ON e1.department = e2.department
 AND e1.id < e2.id
ORDER BY e1.department, e1.id;

SELECT e.name AS employee, m.name AS manager, gm.name AS grandmanager
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.id
LEFT JOIN employees gm ON m.manager_id = gm.id;

SELECT DISTINCT c.name
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p     ON oi.product_id = p.id
WHERE c.city = 'Bengaluru'
  AND p.category = 'Electronics';

Common Mistakes

SELECT name, name AS manager
FROM employees
JOIN employees ON employees.manager_id = employees.id;

SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

-- 'List all employees with their manager'
SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

SELECT c.name, p.name
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi
JOIN products p     ON oi.product_id = p.id;

SELECT c.name, p.name
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p     ON oi.product_id = p.id;

SELECT * FROM customers NATURAL JOIN orders;
-- If both tables have a 'created_at' column, they become part of the join!

SELECT * FROM customers c
JOIN orders o ON c.id = o.customer_id;

Summary