SQL — INNER JOIN

• Recognise foreign keys — the columns that point from one table to another.
• Write SELECT … FROM A JOIN B ON A.k = B.k.
• Disambiguate columns with table aliases (a, s, c).
• Chain two joins for three-table reports.

CREATE TABLE classes (
    class_id   INTEGER PRIMARY KEY,
    name       TEXT,
    teacher    TEXT
);

CREATE TABLE pupils (
    id         INTEGER PRIMARY KEY,
    name       TEXT,
    class_id   INTEGER,                     -- foreign key → classes.class_id
    score      INTEGER
);

INSERT INTO classes VALUES
  (1, 'Form 1A', 'Cikgu Lim'),
  (2, 'Form 1B', 'Cikgu Mei'),
  (3, 'Form 1C', 'Cikgu Anand');

INSERT INTO pupils VALUES
  (1, 'Aisyah',  1, 88),
  (2, 'Wei Jie', 1, 75),
  (3, 'Suresh',  2, 92),
  (4, 'Mei',     2, 80),
  (5, 'Anita',   1, 95),
  (6, 'Razif',   2, 55);
-- (Form 1C has no pupils — yet.)

The arrow: pupils.class_id → classes.class_id. That column is the foreign key.

Basic syntax

SELECT p.name, p.score, c.name AS class_name, c.teacher
FROM pupils AS p
INNER JOIN classes AS c ON p.class_id = c.class_id;

name     score  class_name  teacher
Aisyah     88   Form 1A     Cikgu Lim
Wei Jie    75   Form 1A     Cikgu Lim
Suresh     92   Form 1B     Cikgu Mei
Mei        80   Form 1B     Cikgu Mei
Anita      95   Form 1A     Cikgu Lim
Razif      55   Form 1B     Cikgu Mei

Form 1C is missing — INNER JOIN only includes rows that match on both sides. Form 1C has no pupils, so it's dropped.

Aliases are not optional

The moment two tables both have a name column, the bare query becomes ambiguous. FROM pupils AS p gives the table a short nickname, and p.name / c.name disambiguates.

Join + filter + aggregate

-- Average score per class with teacher name
SELECT c.name AS class, c.teacher,
       ROUND(AVG(p.score), 1) AS avg_score
FROM pupils AS p
INNER JOIN classes AS c ON p.class_id = c.class_id
GROUP BY c.class_id
ORDER BY avg_score DESC;

Three tables — chained JOIN

-- Add a subjects table
CREATE TABLE subjects (subject_id INTEGER PRIMARY KEY, name TEXT);
-- And a marks bridge table
CREATE TABLE marks (
    pupil_id INTEGER, subject_id INTEGER, score INTEGER,
    PRIMARY KEY (pupil_id, subject_id)
);

-- A 3-table join
SELECT p.name AS pupil, s.name AS subject, m.score
FROM pupils  AS p
JOIN marks   AS m ON m.pupil_id = p.id
JOIN subjects AS s ON s.subject_id = m.subject_id;

INNER JOIN is the default — JOIN alone means the same thing. Use JOIN in real code; it's shorter and standard.

-- Best pupil in each class, with teacher name
SELECT class, teacher, name AS top_pupil, score FROM (
  SELECT c.name AS class, c.teacher, p.name, p.score,
         ROW_NUMBER() OVER (PARTITION BY c.class_id ORDER BY p.score DESC) AS rn
  FROM pupils  AS p
  JOIN classes AS c ON p.class_id = c.class_id
) WHERE rn = 1;

class    teacher       top_pupil  score
Form 1A  Cikgu Lim     Anita       95
Form 1B  Cikgu Mei     Suresh      92

Read the diff

Three skills combined:

• JOIN — to bring teacher into the same row as pupil.
• ROW_NUMBER() OVER (PARTITION BY c.class_id ORDER BY p.score DESC) — rank within each class.
• Outer SELECT filtering rn = 1 — keep only the winner.

This single query replaces what used to be a multi-step Python script.

A foreign key in one table points to a primary key in another. JOIN ... ON matches them. Use aliases so columns don't collide. INNER JOIN drops rows that lack a match — that's usually what you want, sometimes a footgun. Tomorrow's LEFT JOIN is the alternative.