Queries

We have talked about querying the database several times and never shown what a query looks like. The same SQL that Chapter 2 used to write the schema down and create the tables is used to write and run queries. As I said before, you are not expected to write SQL in this course. The examples here are for reading, and each one makes a point about designing for reads.

The simplest query returns every row of a table:

SELECT * FROM posts;

SELECT names the columns to return, and * means all of them. FROM names the table. The result is every column of every row in posts.

To get one post, add a condition:

SELECT * FROM posts WHERE post_id = 42;

WHERE keeps only the rows that satisfy the condition. post_id is the primary key, so the database can use the primary key index to go straight to the one row without scanning the table.

Here is the query for the list of published posts, newest first:

SELECT post_id, title, published_at
FROM posts
WHERE state = 'published'
ORDER BY published_at DESC;

Three things are different from the first query. SELECT names three columns instead of *. WHERE filters on state. ORDER BY sorts the result by published_at, and DESC puts the newest first.

The SELECT line is the first way to make a query cheaper. The list shows the title and the publication time. It does not show the body. body is the largest column in the table, often thousands of characters per post. If the query returned *, the database would include every body in the result and send it to the application, and the application would throw it away because the list does not show it. Naming the columns the application needs avoids sending and handling data the application does not use. Depending on the database and the query plan, it can avoid more work as well.

The WHERE and ORDER BY lines are where the index from the previous section can help. Using the index on (state, published_at), the database can find the published rows without scanning the table and read them already sorted by published_at, avoiding a separate sorting step. The database may instead choose to read every row, keep the published ones, and sort them if it expects that to be cheaper.

The N+1 query pattern

Suppose the application wants to show the author’s name with each post in the list. The posts table has author_id, but not the author’s name. The name is in users. One way to get it is to run a second query for each post:

SELECT name FROM users WHERE user_id = 7;

For a list of 20 posts, that is 21 queries: one for the posts and 20 for the authors. For 100 posts, 101. This is called the N+1 query pattern, where N is the number of rows in the first result.

Each query is a round trip: the application sends it, the database runs it, the result comes back. The database work in each author lookup is small, since user_id is the primary key. The round trip is not small, and it costs about the same whether the query returns one row or a hundred. Twenty round trips cost twenty times one round trip. In application code it looks like a loop over the posts with a lookup inside.

Joins

A relational database avoids the N+1 query pattern with a join, which combines two tables in one query. Here is the list of published posts with the author’s name:

SELECT posts.post_id, posts.title, posts.published_at, users.name
FROM posts
JOIN users ON posts.author_id = users.user_id
WHERE posts.state = 'published'
ORDER BY posts.published_at DESC;

JOIN users ON posts.author_id = users.user_id tells the database to pair each post with the user whose user_id matches the post’s author_id. The result has one row per published post, and each row carries the post’s columns and the author’s name. That is one query and one round trip. The database does the matching itself and can use the primary key index on users. This avoids the repeated round trips of asking for one author at a time.

Here is a second join, for a post’s history with the name of the person who made each change:

SELECT c.from_state, c.to_state, c.occurred_at, c.note, u.name
FROM post_state_changes c
JOIN users u ON c.actor_id = u.user_id
WHERE c.post_id = 42
ORDER BY c.occurred_at;

c and u are short names for the two tables, used so that the column names stay readable. The WHERE on c.post_id can use the index on (post_id, occurred_at) from the previous section. That index can also supply the rows in occurred_at order, avoiding a separate sorting step.

Joins are a core feature of a relational database. Chapter 2 split the course history spreadsheet into tables so that each value is stored once, and joins are how the application puts the values back together when it needs them. Many lists and pages in HopPress and CourseTracker use a join or a set of joins.

Joins also get complicated. A query can join more than two tables. It can join the same table twice, which HopPress needs to show both the author’s name and the editor’s name on one row, since both come from users. And there is more than one kind of join. Joining posts to users on editor_id, the way the first join used author_id, drops every post whose editor_id is null, because there is no user to pair it with. A different kind of join keeps those rows and fills the missing columns with null. We do not cover the kinds of joins in this course. When you write real queries, you will need to know them.