Equi-Join
The cartesian product made every possible pairing, most of them nonsense. An equi-join keeps only the pairings that are true — and it does it with a tool you already have: a where clause.
1The two tables
subjects records which teacher takes each subject, by storing the teacher's id rather than their name:
| code | sname | id |
|---|---|---|
| CS083 | Computer Science | 2 |
| MA041 | Mathematics | 3 |
| PH042 | Physics | 9 |
| EN301 | English | 4 |
| HI302 | Hindi | NULL |
| id | name | designation |
|---|---|---|
| 2 | Mohan Das | PGT |
| 3 | Tashi Bhutia | TGT |
| 4 | Reena Gupta | TGT |
| 9 | Rajesh Kumar | PGT |
The id in subjects is a foreign key pointing at the id in teachers. That shared value is the thread the join pulls on.
2The join condition
Name both tables, then add a where saying which pairings to keep: the ones where the two ids are equal.
Four rows, each of them true: Computer Science really is taught by Mohan Das. The sixty meaningless pairings from the cartesian product are gone, cut down by one condition.
An equi-join keeps the rows of a cartesian product where two columns are equal.
“Equi” because the condition uses =.
Switch between the three modes below and watch the same twenty pairings be cut down to four.
Every row of one table is paired with every row of the other first — the condition is what removes the nonsense afterwards.
| code | sname | id |
|---|---|---|
| CS083 | Computer Science | 2 |
| MA041 | Mathematics | 3 |
| PH042 | Physics | 9 |
| EN301 | English | 4 |
| HI302 | Hindi | NULL |
| id | name | designation |
|---|---|---|
| 2 | Mohan Das | PGT |
| 3 | Tashi Bhutia | TGT |
| 4 | Reena Gupta | TGT |
| 9 | Rajesh Kumar | PGT |
5 subjects × 4 teachers. Press Pair them up to see what the database actually builds.
where subjects.id = teachers.id keeps only the pairings where the two tables agree. Hindi has NULL for its teacher, and NULL equals nothing — not even NULL — so it never matches and disappears from the answer.
3Why the column names carry a table name
Both tables have a column called id. Writing where id = id would be meaningless — MySQL cannot tell which is which. So a column is written as table.column:
Columns that exist in only one of the tables — sname, designation — do not strictly need the prefix, but writing it everywhere makes the query easier to read.
4Shortening it with table aliases
Repeating subjects. and teachers. gets tiring. Give each table a short alias just after its name, and use that instead:
Same four rows, far less typing. This is the column alias idea from the SELECT chapter, applied to a table instead.
5The other way to write it: JOIN … ON
The query above joined the tables by naming them both after from and putting the condition in the where. SQL has a second spelling that says “join” out loud and gives the condition its own keyword, on:
The same four rows — not similar, identical, because it is the same join asked for in different words.
where s.id = t.id
on s.id = t.id
You may also see inner join written in full. It is the same thing again — join on its own means an inner join:
The one real advantage of this form shows up when you want to filter as well as join. The on holds the link between the tables and the where holds everything else, so the two jobs stay visibly separate:
Two of the four, because only two of those teachers are PGTs. Written the other way, the link and the filter would sit side by side in one where, joined by and — which also works, and is harder to read as the query grows.
where form is the one the CBSE syllabus means when it says equi-join, and the one the class notes use, so it is what you will most often meet in a question paper. join … on is the more modern standard form and the one you will see outside school. Pick the one you can write correctly under pressure — and stay with it, rather than mixing the two halfway through a query.6What quietly gets left out
subjects has five rows but the join returned four. Hindi is missing, because its id is NULL — no teacher has been assigned — and NULL is not equal to anything, not even to another NULL.
7Writing a join, in three steps
Put both after from, separated by a comma. Give each a short alias.
Find the shared value — nearly always a foreign key and a primary key. That is your where.
Now choose what to show, prefixing each with its table.
Extra conditions go on with and, exactly as anywhere else: where s.id = t.id and t.designation = 'PGT' gives the subjects taught by PGTs only.
8Recap
The equi-join: keep the pairings where the keys are equal.
The same join in the modern spelling — inner join means the same. Use whichever you prefer.
Needed whenever both tables share a column name.
A table alias, to keep the query short.
A NULL or unmatched key means the row does not appear at all.
What turns a cartesian product into an equi-join?
Why write subjects.id rather than just id?
subjects has 5 rows but the equi-join returns 4. Why?
How do these two compare? from subjects s, teachers t where s.id = t.id · from subjects s join teachers t on s.id = t.id
Using join … on, where does an extra filter such as designation = 'PGT' most naturally go?