SQL · Theory

SELF JOIN

← All stacks

Theory

64/439

SELF JOIN

SELF JOIN is simple if you keep it on paper. Sara uses it for student + marks in a report.

Keep SELF JOIN small. Sara should finish student + marks in a few lines, not a 40-line dump.

Without SELF JOIN, a report gets messy and student + marks is hard to trust.

SELF JOIN shows up in a report. Name student + marks, not “a real-world scenario”.

SELF JOIN miss — missing ON → cartesian product. Fix it before you talk about advanced SQL.

SELF JOIN is not a lonely heading. Sara ties it to student + marks.

One breath for SELF JOIN, then student + marks, then missing ON → cartesian product. Sit down.

Diagram

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

Exam tip

Say SELF JOIN in one breath, then JOIN … ON s.roll = m.roll, then missing ON → cartesian product.

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

DefSELF JOIN — Sara uses it for student + marks in a report.
RuleSELF JOIN → JOIN … ON s.roll = m.roll.
RememberSELF JOIN + InnoDB (a report).
UseSELF JOIN in a report (student + marks).
TrapSELF JOIN — missing ON → cartesian product.
ExSELF JOIN → student + marks.

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

SELF JOIN — sample query

Short notes

Questions

SELF JOIN

SELF JOIN — sample query

Short notes

Questions