So far we haven't been able to display the authors' names in our query results, because those names are not in the Works but in the Authors table. What's worse, because there can be many authors to a title and many title associated with each author, there is a third table, Works_Authors that's taking care of this many-to-many relationship. How can these tables be joined?

The SQL command to do this is JOIN. To see how it works, let's start with the somewhat easier case of the Items table and try joining it to the Works table:

The result above was truncated for display because... the query returned a list of 800 records! In fact, JOIN creates the cross product of two tables, i.e., it joins each record of one with each record of the other to give all possible combinations. Since there are 20 records in Works and 40 in Items, the join's output has 20*40=800 records. And since Works has 8 fields and Items has 5, the output has 8+5=13 fields.

What the join hasn't done is figure out if the records being joined have anything to do with each other. It has no way of knowing whether they do or not until we tell it how. To do that, we add a clause specifying that we're only interested in combinations where Work_ID matches in both tables:

ON does the same job as WHERE: it only keeps records that pass some test. (The difference between the two is that ON filters records as they're being created, while WHERE waits until the join is done and then does the filtering.) Once we add this to our query, the database manager throws away records that combined items with unrelated works, leaving us with just the ones we want.

Notice that we used table.field to specify field names in the output of the join. We do this because tables can have fields with the same name, and we need to be specific which ones we're talking about.

We can now use the same dotted notation to select only the columns we are interested in displaying:

We can now try to tackle the case of the Authors table. To list the contributors associated with the first item on the Works table (Work_ID=1, SQL in a nutshell 3rd ed.), we write:

Or inversely, if we want to list all the works that Allen G. Taylor (Author_ID=4) has authored or contributed to, we can write:

If joining two tables is good, then joining more tables must be better. In fact, we can join any number of tables simply by adding more JOIN clauses to our query, and more ON tests to filter out combinations of records that don't make sense.

We can tell which records from Works, Authors, Items and Works_Authors correspond with each other because those tables contain primary keys and foreign keys. A primary key is a value, or combination of values, that uniquely identifies each record in a table. A foreign key is a value (or combination of values) from one table that identifies a unique record in another table. Another way of saying this is that a foreign key is the primary key of one table that appears in some other table. In our database, Works.Work_ID is the primary key in the Works table, while Items.Work_ID is a foreign key relating the Items table's entries to entries in Works. The Authors_Works table contains only foreign keys relating to entries in the Works and Authors tables.

Most database designers believe that every table should have a well-defined primary key. They also believe that this key should be separate from the data itself, so that if we ever need to change the data, we only need to make one change in one place. One easy way to do this is to create an arbitrary, unique ID for each record as we add it to the database. This is actually very common: those IDs have names like "student numbers" and "library card numbers", and they almost always turn out to have originally been a unique record identifier in some database system or other. As the query below demonstrates, SQLite actually numbers records automatically as they're added to tables, and this number could have been used instead of the ID numbers that were specified in the example tables:

Now that we have seen how joins work, we can see why the relational model is so useful and how best to use it. The first rule is that every value should be atomic, i.e., not contain parts that we might want to work with separately. We store personal and family names in separate columns instead of putting the entire name in one column so that we don't have to use substring operations to get the name's components. More importantly, we store the two parts of the name separately because splitting on spaces is unreliable: just think of a name like "Eloise St. Cyr" or "Jan Mikkel Steubart".

The second rule is that every record should have a unique primary key. This can be a serial number that has no intrinsic meaning, one of the values in the record (like the Work_ID field in the Works table), or even a combination of values.

The third rule is that there should be no redundant information. For example, we could get rid of the Works table and rewrite the Items table something like this:

In fact, we could use a single table that recorded all the information about each item in each row, just as a spreadsheet would. The problem is that it's very hard to keep data organized this way consistent: if we realize that the bibliographic information associated with a series of items is wrong, we have to change multiple records in the database. Similarly, storing authors' and contributors' names in separate columns would mean adding an extra column every time we encounter a title that has more contributors than all the previous items. If we have only one item that has twenty contributors, a spreadsheet design would require twenty separate columns to store this information, and those columns would sit empty for the majority of the items. This is one of the reasons why authority files are in separate tables.

The fourth rule is that the units for every value should be stored explicitly. In this particular database, it's not very important (although we could specify that the Pages field contains the count of pages in any given book). This can be a problem in databases that contain scientific data, for example.

Stepping back, data and the tools used to store it have a symbiotic relationship: we use tables and joins because it's efficient, provided our data is organized a certain way, but organize our data that way because we have tools to manipulate it efficiently if it's in a certain form. As anthropologists say, the tool shapes the hand that shapes the tool.

Challenges

1. Write a query that lists all works written by people whose Family name start with the letter "K".

2. Write a query that lists all authors that have written at least one book that is currently on loan from the library.

3. To which item does the barcode 722040919616 refer to, what is the title of this book and who are its authors?