SQL · Theory

JOIN

Theory

59/439

JOIN

JOIN sticks two tables side by side using a match. students has names. marks has scores. SELECT s.name, m.subject, m.score FROM students s JOIN marks m ON s.roll = m.roll;

ON is the match rule, usually PK = FK. If you forget ON (or use comma joins), you get every student with every marks row — cartesian product. That looks ‘too many rows’.

INNER JOIN (plain JOIN) keeps only matches. LEFT JOIN keeps all students even if they have no marks yet (marks columns NULL).

Draw two tiny tables on paper, three rows each, then the join result. That is the interview. Don’t recite six join names without a picture.

On the example next to this theory — JOIN combines rows using a match condition between tables.

Diagram

students          marks
  roll name         roll subject score
   12  Asha    JOIN  12  Maths    72
   13  Kabir         12  Eng      40
        │
        ▼  ON roll
  Asha | Maths | 72
  Asha | Eng   | 40

Exam tip

JOIN … ON s.roll = m.roll. Draw two small tables.

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 combines tables on a match (usually PK/FK).
RuleAlways ON. Alias s and m. INNER = matches only.
TrapMissing ON → cartesian product.

SQL · Theory

JOIN

← All stacks

Theory

59/439

JOIN

JOIN sticks two tables side by side using a match. students has names. marks has scores. SELECT s.name, m.subject, m.score FROM students s JOIN marks m ON s.roll = m.roll;

ON is the match rule, usually PK = FK. If you forget ON (or use comma joins), you get every student with every marks row — cartesian product. That looks ‘too many rows’.

INNER JOIN (plain JOIN) keeps only matches. LEFT JOIN keeps all students even if they have no marks yet (marks columns NULL).

Draw two tiny tables on paper, three rows each, then the join result. That is the interview. Don’t recite six join names without a picture.

On the example next to this theory — JOIN combines rows using a match condition between tables.

Diagram

students          marks
  roll name         roll subject score
   12  Asha    JOIN  12  Maths    72
   13  Kabir         12  Eng      40
        │
        ▼  ON roll
  Asha | Maths | 72
  Asha | Eng   | 40

Exam tip

JOIN … ON s.roll = m.roll. Draw two small tables.

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 combines tables on a match (usually PK/FK).
RuleAlways ON. Alias s and m. INNER = matches only.
TrapMissing ON → cartesian product.

JOIN

JOIN — sample query

Short notes

Questions

JOIN

JOIN — sample query

Short notes

Questions