Join tables together

Up to now you have dealt with one table at a time. But look inside the database of a real app and there are tables everywhere. Why not gather them into one?

Think about keeping a library's loan records in a single table: who, which book, when. You would be writing the member's name, address and phone number onto every single loan. Borrow ten times and the same name and address are written ten times over. And that is where the trouble starts.

• when the address changes you have to fix all ten places
• miss one and there is no telling which is right
• the same information is stored over and over, eating space for nothing

So you split the tables. The member details go in the members table once, and the loans table holds only a mark saying which member it was. That mark is the id, and it is the thread between the tables. Splitting things this way is the most basic idea in database design.

Then, when you want to look, you join what you split. That is the join you learn in this chapter. Write "join the loans table to the members table where the member id matches" and out comes one table of loan records with the member's name attached.

There is more than one kind of join, and the difference between them is the steepest part of this chapter.

• the way that joins only what is present on both sides
• the way that keeps one side whole and leaves a gap where there is no partner

To "count the loans of every member, including the ones who have never borrowed", you need the second. Write it the first way and the people who never borrowed vanish from the result. "The zeros disappear" is a very common mistake in aggregation, so choose between the two deliberately.

A join stretches to three tables and more. Join loans, members and books together and you have "who borrowed which book, and when" on one sheet. Queries like that are running behind the screens of a real app.

The nuisance when writing joins is the same column name existing in both tables. You lose track of which id you are talking about. So you learn to give a table a short alias and say "this table's id" explicitly. It is a small extra step, but doing it means you can still read a long query later on.

The easy thing to trip on is forgetting to write the joining condition. Join with no condition and every row on one side is paired with every row on the other, giving you a result of unbelievable size. A hundred rows and a hundred rows makes ten thousand. When the number of rows is obviously wrong, this is the first place to look.

One knack for making sense of it: think of a join as making one big table first. Picture the joined table, then narrow from it and aggregate on it. That is quicker than chasing the order of operations through your head — imagine "what shape is the table after joining?".

You can join more than three, and you can even join a table to itself. That is the shape you use to follow a relationship inside one table, as in "who is this employee's manager?". It bends the mind slightly, but the thinking is the same as ever.

What is in this chapter feeds straight into designing an app of your own. Deciding how to split the tables is the work of the very first stage of building. Understand "why split" here and your hands will move when you come to design a database yourself.

By this point SQL turns from "a tool for taking things out of a table" into "a tool for assembling scattered data into an answer". Put it together with the aggregating from the last chapter and the questions you can answer widen all at once.