Working with Two Tables: Cartesian Product, Equi-join and Natural Join

Hello students, welcome to Kwickprep. Riya's name is in one table and her house name is in another. How do we show them together in one result? Today we will learn why data is kept in separate tables. Then we will join them using the Cartesian product, the equi-join and the natural join.

Imagine a school keeps everything in one big table. Riya is in house number one, which is Green. Aman is in house two, which is Blue. Neha is also in house one, so Green is typed again. Kabir is in house one too, so Green is typed a third time.

This repetition causes problems, so we split the data into two tables. First, each house name is stored only once. Second, there are fewer typing mistakes, because nobody types Green again and again. Third, if a house is renamed, we change it in one row only. Fourth, the database uses less storage space.

Here is our first table, called student. It has a roll number, a name and a house number. Riya has roll number one and is in house one. Aman has roll number two and is in house two. Neha has roll number three and is in house one.

The second table is called house. House number one is Green. House number two is Blue. The column H No is present in both tables, so it is called the common column.

Before we join, learn four words. Degree means the number of columns, so student has a degree of three. Cardinality means the number of rows, so student has a cardinality of three. A common column holds the same kind of data in both tables and links them. A join combines rows from two tables into one result.

The simplest way to use two tables is to list both after from, separated by a comma. There is no condition at all. This gives the Cartesian product, also called a cross join. Every row of the first table is paired with every row of the second table.

Here is part of that result, showing the name and both house number columns. Riya is paired with house one. Riya is also paired with house two, which is wrong for her. Aman is paired with house one, also wrong. Aman is paired with house two, which is correct. Neha is paired with house one, which is correct. And Neha is paired with house two, which is wrong.

Exams often ask you to calculate the size of this result. The number of rows is the rows of the first table multiplied by the rows of the second. The number of columns is the columns of the first table plus the columns of the second. So here we get three times two, six rows, and three plus two, five columns. With a thousand students and forty clubs, you get forty thousand rows, and most of them are meaningless.

To keep only the correct pairs, we add a condition. The where clause says student dot H No equals house dot H No. Only rows where the two house numbers are equal are kept. A join that matches columns using the equals sign is called an equi-join. Now Riya is Green, Aman is Blue and Neha is Green.

Remember four rules for the equi-join. The condition uses the equals sign on the common column. When both tables have a column with the same name, write the table name, a dot, then the column name. If you write select star, the common column appears two times in the output. Many books also write it as join, with the condition after the word on.

Typing full table names again and again is slow. So we give each table a short name, called an alias. Here S stands for student and H stands for house. We can also add more conditions with and. Pause and predict. Which students print if we ask for house Green? Riya and Neha, because both are in house one.

A natural join does the equi-join for us. We write natural join between the two table names, with no condition. SQL finds the column with the same name in both tables and matches on it. In MySQL, the common column H No comes first, and it appears only once. So the result has four columns, not five.

Let us compare the three ways side by side. A Cartesian product has no condition, and it shows every pair. In an equi-join, you write the equals condition, and select star shows the common column twice. In a natural join, SQL matches the same-named column automatically and shows it only once.

The natural join has some traps. The common columns must have exactly the same name in both tables. If two pairs of columns share names, it matches on both of them. And if no column name matches, it quietly gives you the full Cartesian product.

Let us revise what we learned today. We split data into tables to avoid repetition and mistakes. A Cartesian product pairs every row with every row, so rows multiply and columns add. An equi-join uses a where condition with equals on the common column. A natural join matches same-named columns by itself and shows the common column once. Try these queries on two small tables of your own.