Natural JoinOptional for Informatics Practices

The equi-join worked, but you had to say twice what MySQL could have worked out for itself: that the linking column is called id in both tables. A natural join spots that on its own — and tidies up the result while it is at it.

Who this page is for

The natural join is named in the Computer Science (083) syllabus. The Informatics Practices (065) syllabus asks only for the equi-join, so IP students can treat this as background.

1No where clause at all

The two table names are joined by the words natural join, and there is no condition to write:

MySQL command line client

mysql> select * from subjects natural join teachers;

+------+-------+------------------+--------------+-------------+-------+------------+------------+--------------+---------+

+------+-------+------------------+--------------+-------------+-------+------------+------------+--------------+---------+

| 2 | CS083 | Computer Science | Mohan Das | PGT | 58000 | 2013-08-01 | BXYPD5678L | 422233334444 | Gangtok |

| 4 | EN301 | English | Reena Gupta | TGT | 44500 | 2016-07-11 | DEFPG3456N | 444455556666 | Namchi |

| 3 | MA041 | Mathematics | Tashi Bhutia | TGT | 47000 | 2015-04-20 | CDEPB9012M | 433344445555 | Gangtok |

| 9 | PH042 | Physics | Rajesh Kumar | PGT | 60000 | 2010-05-30 | IJKPK4567T | 499900001111 | Namchi |

+------+-------+------------------+--------------+-------------+-------+------------+------------+--------------+---------+

4 rows in set (0.00 sec)

The same four subjects as the equi-join, matched on the same id — but nowhere did you have to say so.

2How it decides what to match on

A natural join matches on every column the two tables have with the same name.

subjects has code, sname and id. teachers has id, name, designation and the rest. Exactly one name appears in both lists — id — so that is what gets matched.

This is also its weakness

The join depends on how the columns happen to be named. Rename subjects.id to teacher_id and the natural join silently stops matching on it. Worse, if the tables shared a second name — say both had a city — the join would quietly require both to be equal, and return far fewer rows than you intended.

Switch to Natural join and watch the duplicated id column collapse into one.

Build the join yourself

Every row of one table is paired with every row of the other first — the condition is what removes the nonsense afterwards.

mysql> select * from subjects natural join teachers;

subjects

code	sname	id
CS083	Computer Science	2
MA041	Mathematics	3
PH042	Physics	9
EN301	English	4
HI302	Hindi	NULL

teachers (4 of the 12, so they fit)

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.

The same four rows, but MySQL worked out the condition itself from the shared column name, and merged that column into one. Putting it first is MySQL's doing, not a choice made by this widget: a natural join coalesces the shared column, then adds the rest of the left table, then the rest of the right.

3The shared column appears only once

Look at the resultset again. There is a single id column, at the front. The equi-join version of the same query with select * would have shown id twice, once from each table — the same number printed side by side, since the condition forced them to be equal.

Removing that duplication is the second thing a natural join does for you, and it is why the shared column is moved to the front.

4The two joins compared

Equi-join vs natural join

	Equi-join	Natural join
Written as	from a, b where a.id = b.id	from a natural join b
Join condition	You write it	Worked out from matching column names
The shared column	Appears twice with select *	Appears once, at the front
Columns must share a name?	No — any two columns can be compared	Yes, that is the whole mechanism
Control	Exact — you say what matches what	Automatic, and silently wrong if names change

Which to use in an exam

Both are correct answers to “join these two tables” unless the question names one. The equi-join is the safer habit: it works whatever the columns are called, and anybody reading it can see exactly what is being matched. Use the natural join when a question asks for it by name.

5Unmatched rows still vanish

Four rows again, not five. Hindi, whose teacher id is NULL, is missing here exactly as it was from the equi-join. A natural join changes how the condition is written, not what counts as a match — and NULL still matches nothing.