SELF JOIN — SQL Technical Interview Questions & Answers

SELF JOIN

SELF JOIN

  • A self-join is a table joined to itself with two aliases. employees e JOIN employees boss ON e.manager_id = boss.id; each person plus their manager’s name. Same table, two nicknames.
  • Need a hierarchy or ‘pair rows in the same table’. Students sitting in the same city: s1 JOIN s2 ON s1.city = s2.city AND s1.roll < s2.roll to avoid pairing Asha with Asha.
  • Trap: self-join without aliases — MySQL cannot tell which column is which. Trap: forgetting the extra AND to avoid duplicate pairs.
  • On the example next to this theory: SELF JOIN — jOIN combines rows using a match condition between tables.

Technical Interview Tip: employees e JOIN employees boss ON e.manager_id = boss.id

Code Examples for SELF JOIN

SELF JOIN — sample query

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO orders VALUES (10, 1, 500), (11, 1, 200);
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

SELF JOIN — jOIN combines rows using a match condition between tables.

How to Explain SELF JOIN to an Interviewer

Why two aliases?

Same table twice e.name vs boss.name