Basic

Stitching two tables

I kept all my data in one giant table and it worked for a week, then every query started repeating the same name down a hundred rows. Splitting into two tables and joining them is what fixed it.

What you will see

name|city Ada|Rome Grace|Paris sqlite>

About 2 min of reading

Try It

Build a second table, link it to the first, and read a name next to its city.

Draw the second table with its own id and a city column.

CREATE TABLE cities (id INTEGER, city TEXT);

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

Fill it with cities keyed to the same ids as the users table.

INSERT INTO cities (id, city) VALUES (1, "Rome"); INSERT INTO cities (id, city) VALUES (2, "Paris");

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

Join the two tables on the shared id. Ada lines up with Rome, Grace with Paris. Alan has no city row, so he does not appear.

SELECT name, city FROM users JOIN cities ON users.id = cities.id;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

name|city Ada|Rome Grace|Paris sqlite>

The same join with table nicknames. The answer is identical, the query is shorter to read.

SELECT u.name, c.city FROM users JOIN cities ON u.id = c.id;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

name|city Ada|Rome Grace|Paris sqlite>

Data Flow

JOIN pulls a second table in, ON matches rows on a shared column, and matching rows are glued into one wider row.

1 FROM names the first table

↓

2 JOIN brings the second

↓

3 ON matches on a column

↓

4 Matched rows glue together

Why this works

Why split at all

One table per idea. Names in one table, cities in another, and a number that links them. You repeat the link number many times, but you write each fact once. When a fact changes, you change it in one place, not down every row. This is what a database buys you over a single flat file.

JOIN needs ON

A JOIN without ON would match every row of one table against every row of the other, which is a square of the sizes and almost never what you want. ON is the rule that says which rows belong together. Forgetting it is not an error, it is a huge wrong answer, and that is why it is worth pausing on.

Examples

Two tables, one link

SELECT name, city FROM users;

Right now city is not in users, so this errors. The name lives in one table and the city lives in another. To see them side by side you have to tell SQL how the two tables connect.

Join on a shared id

SELECT name, city FROM users JOIN cities ON users.id = cities.id;

JOIN brings a second table in. ON says which column lines up. Where the ids match, the name and the city are glued onto one row.

Shorten with a name

SELECT u.name, c.city FROM users JOIN cities ON u.id = c.id;

u and c are nicknames for the tables. When two tables share a column name, the prefix says which one you mean. Without it SQL cannot tell which id you want.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Only the rows that match

Previous Lesson

Only the rows that match

Next Lesson

Adding rows safely

Adding rows safely →