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 uses the primary key index to go straight to the one row. It does not scan 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 read every body from disk 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 that wasted work.

The WHERE and ORDER BY lines are where the index from the previous section is used. The index on (state, published_at) lets the database find the published rows without scanning the table and read them already sorted by published_at, so there is no sorting step. Without the index, the database reads every row, keeps the published ones, and sorts them.

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, using the primary key index on users, and it does that faster than the application can by 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 uses the index on (post_id, occurred_at) from the previous section, and the same index already returns the rows in occurred_at order, so there is no 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. Every list and every page in HopPress and CourseTracker is 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.