SQL · Theory

JOIN

← All stacks

Theory

59/439

JOIN

JOIN sits in a report. Riya’s job is student + marks. Write that first.

JOIN on the board — JOIN … ON s.roll = m.roll. Then say what student + marks looks like after.

Skip JOIN and missing ON → cartesian product shows up in a report.

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

If student + marks breaks under JOIN, check missing ON → cartesian product first.

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

Viva for JOIN — what it is → JOIN … ON s.roll = m.roll → the mistake (missing ON → cartesian product).

Diagram

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

Exam tip

Board: student + marks. Dry-run JOIN. Name the trap: missing ON → cartesian product.

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;

JOIN combines rows using a match condition between tables.

Short notes

DefJOIN — Riya uses it for student + marks in a report.
RuleJOIN → JOIN … ON s.roll = m.roll.
RememberJOIN + an index (a report).
UseJOIN in a report (student + marks).
TrapJOIN — missing ON → cartesian product.
ExJOIN → 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;

JOIN

JOIN — sample query

Short notes

Questions

JOIN

JOIN — sample query

Short notes

Questions