SQL · Theory

SELF JOIN

Theory

64/439

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.

Diagram

  students.id
       =
  marks.student_id
        │
        ▼
     one result row

Exam tip

employees e JOIN employees boss ON e.manager_id = boss.id

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.

Short notes

DefJoin a table to itself with two aliases.
Rulee and boss (or s1 and s2). Extra condition to avoid duplicates.
TrapNo aliases.

SQL · Theory

SELF JOIN

← All stacks

Theory

64/439

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.

Diagram

  students.id
       =
  marks.student_id
        │
        ▼
     one result row

Exam tip

employees e JOIN employees boss ON e.manager_id = boss.id

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.

Short notes

DefJoin a table to itself with two aliases.
Rulee and boss (or s1 and s2). Extra condition to avoid duplicates.
TrapNo aliases.

SELF JOIN

SELF JOIN — sample query

Short notes

Questions

SELF JOIN

SELF JOIN — sample query

Short notes

Questions