Joins: linking tables that sit apart

The data sits in two tables

Earlier we learned to pick
the rows we want from one table.
But data usually does not
all fit in a single table.
Orders go in an orders table,
person info in a separate customers table.
We split tables on purpose
so the same info is not copied all over.
Shall we lay the two tables side by side first?

Tap a table to open it. You can see the orders table and the customers table sit apart.

The two tables look separate.
The orders table has what was bought,
the customers table has the person's name.
But from the orders table alone
we cannot learn the name behind an order.
The name is over in the customers table.
To answer a question that crosses tables
we must link the two somehow.
Where is the clue to link them?

Find the column both tables share

Look closely at the two tables
and there is one column with the same name.
It is the customer number.
The orders table has a customer number,
and the customers table has one too.
A column that both tables share like this
is called a common column.
This common column is the bridge between them.
Shall we find which column is shared?

Tap a column name. Pick the common column both tables share and it lights up, otherwise it tells you.

We found the common column.
The customer number is in both tables.
If the value in this column is equal,
those rows in the two tables point to the same person.
Customer number 7 in the orders table
and customer number 7 in the customers table
mean the same person.
So using this column as a guide,
we can match rows to each other.

Match rows by the common column

Now let's actually match them.
Pick one row from the orders table.
Look at its customer number.
Then in the customers table
find the row with the same customer number.
If the two customer numbers are equal,
the two are a pair.
One order row and one person row
clasp hands like this.

Tap a row in the orders table. The customers row with the same customer number links up, showing the pair.

The order and the person got paired.
Rows with the same customer number
clasped hands neatly.
Now we can read straight off
the name behind this order.
The order that showed only a number
now has a person's name beside it.
With the pairs all matched,
shall we merge the two into one line?

The two tables merge into one

Stick the two paired rows side by side
and you get a new table with a longer line.
Next to the order's cells
come that person's cells, lined up.
The common column need appear only once,
so the number stays one tidy cell.
A table merged this way
can be treated like one table.
What was bought and who bought it
now sit together on one line.

Tap to combine. The two tables join line by line on the common column, gathering what and who into one table.

It merged cleanly into one table.
Now we can see the order and the person
together at a glance.
We keep the tables apart
yet link them by the common column when needed,
so there is no need to copy the same info.
The thrift of keeping them split
and the ease of viewing them linked,
a join brings us both.
Now let's wrap the whole thing up.

Let's wrap up

Gathered on one line, it is this.
Data is split across several tables.
To answer a question that crosses tables
we find a common column both tables share.
Matching rows whose value in that column is equal,
we link the two tables into one line.
Then they can be treated like one table,
so we learn even the name behind an order.
Linking by matching on a common column, that is a join.

Tap the key points in order to review. (two tables -> find the common column -> match by the common column -> merge into one table)

Now you know how to link
scattered tables by a common column.
A question once trapped in one table
cleared up with a single bridge.
Splitting yet linking when needed,
this knack is the power of handling data.
Next, with what other way
can we handle many tables even better?
Let's keep exploring together.