Databases & SQL · Class 12 · Working with Two Tables

MySQLjoins⏱️ 8 min read

Cartesian ProductOptional for Informatics Practices

Every query so far named one table. Name two and something surprising happens: you do not get the tables side by side, you get every possible pairing of their rows. Understanding that is what makes a real join make sense.

Who this page is for

The Computer Science (083) syllabus names the cartesian product explicitly. The Informatics Practices (065) syllabus asks only for the equi-join, which is the next page — but it is much easier to understand once you have seen this, so IP students should read it anyway.

1Two small tables

A school has two lab rooms and three teaching periods in the afternoon:

MySQL command line client

mysql> select * from rooms;

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

| rno | rname |

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

| 1 | Lab A |

| 2 | Lab B |

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

2 rows in set (0.00 sec)

mysql> select * from slots;

+--------+

| period |

+--------+

| 1 |

| 2 |

| 3 |

+--------+

3 rows in set (0.00 sec)

2Naming both tables

Put both names after from, separated by a comma, and no condition at all:

MySQL command line client

mysql> select * from rooms, slots;

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

| rno | rname | period |

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

| 2 | Lab B | 1 |

| 1 | Lab A | 1 |

| 2 | Lab B | 2 |

| 1 | Lab A | 2 |

| 2 | Lab B | 3 |

| 1 | Lab A | 3 |

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

6 rows in set (0.00 sec)

Two rooms and three periods gave six rows. Every room has been paired with every period: Lab A at period 1, Lab B at period 1, Lab A at period 2, and so on. Nothing has been added to either table — this is every combination that could be made from them.

The cartesian product pairs every row of the first table with every row of the second.

2 rows × 3 rows = 6 rows

Degree adds, cardinality multiplies

The result has the columns of both tables together — 2 + 1 = 3, so its degree is the sum. But its cardinality is 2 × 3 = 6, the product. That is exactly the sort of one-mark question this topic attracts, and the two words behave differently, so read carefully.

Press the button and watch all six — then all twenty — pairings appear at once.

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, 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.

No condition, so nothing is thrown away: 5 × 4 = 20 rows, most of them untrue. On the real twelve-row table that is 60. Multiply two thousand-row tables and it is a million.

3When this is what you want

Rarely — but not never. The six rows above are a genuine timetable grid: every slot that could be booked. If you were drawing a blank timetable to fill in, that is precisely the list you need.

4Why it is usually a mistake

Now try it with the real tables. Twelve teachers and five subjects:

MySQL command line client

mysql> select count(*) from teachers, subjects;

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

| count(*) |

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

| 60 |

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

1 row in set (0.00 sec)

Sixty rows — every teacher paired with every subject, including all the pairings that are simply untrue. Mohan Das appears alongside Hindi, which he does not teach. Only 5 of those 60 rows say anything correct.

The runaway row count

A cartesian product of two tables of 1,000 rows each is a million rows. If a query is unexpectedly slow or returns an absurd number of rows, the first thing to check is whether you named two tables and forgot the condition that links them. That condition is the whole of the next lesson.

5Other names for it

The same operation is called a cross join or an unrestricted join. In algebra it is written A × B, which is where “product” comes from. Your syllabus calls it the cartesian product; the others may appear in reference books.