JOINs, with a worked two-table example

Why the data is in two tables at all

You could store marks inside the students table: columns subject1, marks1, subject2, marks2. Then a student takes a sixth subject and you have to change the table structure. That is the wrong shape.

The right shape: one table per kind of thing.

CREATE TABLE students (
    roll_no INT PRIMARY KEY,
    name    VARCHAR(60) NOT NULL,
    branch  VARCHAR(10) NOT NULL
);

CREATE TABLE marks (
    id       INT PRIMARY KEY,
    roll_no  INT NOT NULL,
    subject  VARCHAR(20) NOT NULL,
    score    INT NOT NULL,
    FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);

FOREIGN KEY (roll_no) REFERENCES students(roll_no) is the important line. It tells the database: a mark row must point at a student that exists. Insert a mark for roll 999 and the insert is rejected. Broken data is now impossible, not just discouraged.

The data

students:

marks:

Notice: Anita has two mark rows. Imran has none — he joined late and has not written an exam yet.

INNER JOIN

SELECT s.roll_no, s.name, m.subject, m.score
FROM students s
INNER JOIN marks m ON s.roll_no = m.roll_no
ORDER BY s.roll_no, m.subject;

Result — exactly four rows:

Work through what happened, because this is the whole idea.

The database looks at every possible pairing of a student row with a marks row. Four students times four mark rows is sixteen pairs. Then it keeps only the pairs where s.roll_no = m.roll_no. Twelve pairs fail that test and are dropped. Four survive.

Two consequences fall straight out of that:

Anita appears twice. She has two matching mark rows, so she is in two surviving pairs. Her name is repeated. This is normal and correct — the join result is not a list of students, it is a list of student-mark facts.

Imran vanishes. No mark row has roll_no = 104, so no pair containing Imran survives. An INNER JOIN silently deletes rows that have no match.

s and m are table aliases. They save typing and, more importantly, make s.roll_no versus m.roll_no unambiguous when both tables have a column of the same name.

LEFT JOIN

When "silently deletes rows" is wrong for your question, use LEFT JOIN. It keeps every row from the left table, matched or not, and fills the right-hand columns with NULL when there is no match.

SELECT s.roll_no, s.name, m.subject, m.score
FROM students s
LEFT JOIN marks m ON s.roll_no = m.roll_no
ORDER BY s.roll_no;

Five rows now. Imran is back, with NULL where his marks would be.

This gives you the "who has not written any exam" query for free:

SELECT s.roll_no, s.name
FROM students s
LEFT JOIN marks m ON s.roll_no = m.roll_no
WHERE m.roll_no IS NULL;

Only Imran. LEFT JOIN plus IS NULL on the right side is the standard way to find "rows in A with nothing in B". Learn this pattern — it comes up in interviews constantly.

RIGHT JOIN is the mirror image and is rarely used, because you can always swap the table order and write a LEFT JOIN instead, which is easier to read. FULL OUTER JOIN keeps unmatched rows from both sides; MySQL does not support it directly, PostgreSQL does.

Reading a join in your head

When you see a join, ask three questions in order. Which table is on the left? What is the ON condition? Can a single left row match more than one right row? Answer those three and you can predict the number of output rows before you press run.