What is a SQL join

The information is split across two tables

In the last lesson we used SQL to pull the rows we wanted out of a single table. In a real database, though, the information you need is rarely all in one table. A shop keeps its orders in an orders table, and its customers' names and contact details in a separate customers table.
The split is deliberate. If the customer's name and phone number were written again on every order, the same details would pile up over and over, and a new phone number would mean hunting down and fixing every one of those rows. First, let's lay the two tables side by side.

Tap each table header to open it. You will see that the orders table and the customers table hold different columns.

Opened up, the two tables clearly do different jobs. The orders table has an order number, a customer number, and an item; the customers table has a customer number and a name. From the orders table alone, all you know about whoever bought the pencil is a number.
A question like "What is the name of the person who bought the pencil?" crosses tables: the item is in one table and the name in the other, so you only get an answer by linking them. First, let's find the clue that connects them.

Find the column both tables share

Put the column names of the two tables side by side, and one of them appears in both: the customer number. In the customers table, each customer gets a number of their own that no one else has, so knowing the number pins down exactly which customer you mean.
The customer number in the orders table is simply that number copied over. That is why the customer number in the customers table is called the primary key, and the one in the orders table that points to it is called a foreign key. This shared column is the bridge between the two tables.

Tap the column names one at a time. Pick a column that is in both tables and it lights up; pick one that is in only one table and you are told which one.

The item was only in the orders table, and the name only in the customers table. The customer number is the one column found in both.
When the values in this column match, the two rows refer to the same customer: customer number 7 in the orders table and customer number 7 in the customers table are the same person, Hajun. So with this column as a guide, you can pair rows with each other.

Pair the rows that share a number

Pairing is simple. Pick a row in the orders table, read its customer number, and look in the customers table for the row with the same customer number. If the numbers match, the two rows are a pair.
One customer can place several orders, and then several rows in the orders table point to the same row in the customers table. Tap the pencil order and then the eraser order below, and both lead to the same customer. One customer with many orders: this kind of relationship is called one-to-many.

Tap a row in the orders table. The customers row with the same customer number lights up, and the line below tells you whose order it is.

The orders that only showed a number now come with a name. The pencil and eraser orders both lead to Hajun, and the notebook order leads to Minseo.
A database repeats this for every row. A primary key usually gets an index automatically, so even when the tables are large, the database finds each partner in the customers table quickly. So what kind of table do you get when you put each pair together on one line?

Put each pair together in one table

Place two paired rows side by side and you get a new, wider row: the order's cells, then that customer's cells next to them, with the customer number, which was in both tables, kept only once. Gather those rows and you have a result table where what was bought and who bought it sit on the same line.
In SQL, you write JOIN between the two table names and state with ON which columns must have equal values. The SELECT and WHERE from the last lesson can be used alongside it just as before.

Tap the combine button. The two tables are joined row by row on the customer number, bringing what was bought and who bought it into one table. Tap again to split them back apart.

Hajun's name appears twice in the result table. Even so, the customers table still stores Hajun in only one row. The repetition exists only in a result table built for a moment to answer a question; the stored tables stay as they were.
Also keep in mind that rows without a partner are left out. If the customers table has someone who has never placed an order, there is no order to pair with, so that customer does not appear in the result of a basic join. There is a different kind of join for when you want to keep the unpaired rows too.

Summary

To sum up: a database spreads information across several tables so that it never writes the same details more than once. To answer a question that crosses tables, you find a column both tables share, pair the rows whose values in that column match, and put each pair side by side.
That gives you a result table you can read like a single table. Store it split up, link it when you ask: that is a join.

Tap the key points in order to review. (two tables → find the common column → pair rows by the common column → put them into one table)

Now you can ask questions across scattered tables by linking them through a shared column, which means that splitting data into tables does not stand in the way of getting answers.
So by what rule should tables be split? What goes wrong if a customer's name is written on every order, and how do you trim away the repeated information? The next lesson, on normalization, takes this up.