Video #320: SQL Builders: Joins

Episode: Video #320 Date: Apr 7, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep320-sql-builders-joins

Description

We dive into the “relational” part of relational databases by learning how tables can reference one another, the various ways queries can join these relations together, and even how to aggregate nuanced data across these relations, all without ever hopping over to Xcode.

Video

Cloudflare Stream video ID: 526ea4f930041a4b01fc0003351b8477 Local file: video_320_sql-builders-joins.mp4 *(download with --video 320)*

References

Transcript

— 0:05

We have now accomplished some amazing things. We can build SQL queries that can express complex

WHERE 0:22

And we have now tackled 3 of the major parts of a typical SQL query. We can select columns and expressions, we can order by columns and expressions, and now we can filter results by predicates. We are going to move onto the next major part of SQL queries, and this is a big one. Brandon

WHERE 0:39

SQLite is what is known as “relational” database, as are other databases such as Postgres and MySQL. The “relational” adjective refers to the database’s ability to relate the rows of one table with the rows of another. Relationships are formed so that you can join tables together along those relations. This opens up tons of power, such as fetching data from multiple tables in one single query, as well as aggregating the results in one table based on the relationship to another table. Stephen

WHERE 1:15

A prototypical example of this is if we had a “reminders lists” table that represented, well, lists of reminders. Then each reminder would belong to exactly one list. And we may find ourselves wanting to count how many reminders are in each list. We could of course perform one query to fetch all of the lists, and then perform a query for each list to count the number of reminders for each list. We have actually learned enough SQL so far to write those queries. But it is incredibly inefficient. If we have hundreds of lists we will need to execute hundreds of queries. Brandon

WHERE 1:49

Luckily SQL provides a wonderful calculating this kind of aggregate data in one single, efficient query. And it really can be efficient. It is possible to execute such queries with thousands of lists and tens of thousands of reminders.

WHERE 2:09

This tool is called

JOIN 2:25

In order to explore joins we need to have another table to join to . It is technically possible to join tables to themselves, and such queries can be powerful, but that is a little too advanced for us at this stage.

JOIN 2:37

Let’s create a table that represents lists of reminders. It will only have an id and title column, and since we are familiar with CREATE TABLE statements we can simply paste it in: CREATE TABLE "remindersLists" ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "title" TEXT NOT NULL );

JOIN 3:03

That creates our new table, and we can insert some data into it: INSERT INTO "remindersLists" ("title") VALUES ('Personal'), ('Work'), ('Family');

JOIN 3:26

And we can query for all of our lists: sqlite> SELECT * FROM "remindersLists"; ┌────┬───────────┐ │ id │ title │ ├────┼───────────┤ │ 1 │ Personal │ │ 2 │ Work │ │ 3 │ Family │ └────┴───────────┘

JOIN 3:36

So we now have a remindersLists table, and we have created some lists.

JOIN 3:40

Next we need to set up a relationship between our reminders table and our remindersLists table. There are many kinds of relationships one can model. There’s 1-to-1, 1-to-many and many-to-many. The relationship we want to model right now is that of a reminder belonging to exactly one list. It will not be possible for a reminder to belong to no list or many lists. And a list can have any number of reminders, even potentially zero reminders.

JOIN 4:11

To model such a 1-to-many relationship, you add a column to the table that can belong to the other table, and the column added will be the ID of the list the reminder belongs to. This extra field acts as a kind of “pointer”. It points to the row in another table it is associated with. And in SQL terminology, this is called a “foreign key”.

JOIN 4:37

We can use the special .schema command in the SQLite console to see the current shape of our reminders table: sqlite> .schema reminders CREATE TABLE IF NOT EXISTS "reminders" ( "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "title" text NOT NULL DEFAULT '', "isCompleted" boolean NOT NULL DEFAULT false, "priority" integer);

JOIN 4:55

We want to add another column to this table. This brings up to a whole new type of statement that we have yet to come across, called an ALTER TABLE statement: ALTER TABLE

JOIN 5:06

And like always, let’s refer to the SQLite documentation to see how such a statement can be constructed. We will find the following flow chart on the website.

JOIN 5:22

This shows that right after specifying ALTER TABLE we name the table we want to alter: ALTER TABLE "reminders"

JOIN 5:32

And then we can either rename the table, rename a column, or add a column, or drop a column. We want to add a column: ALTER TABLE "reminders" ADD COLUMN

JOIN 5:45

And after that we can to specify the column in the exact same syntax as we would when creating the table. In this case we want to add a column named remindersListID and make it an integer: ALTER TABLE "reminders" ADD COLUMN "remindersListID" INTEGER

JOIN 6:05

And further, we want to make the column NOT NULL : ALTER TABLE "reminders" ADD COLUMN "remindersListID" INTEGER NOT NULL This will force that each reminder always belongs to a list.

JOIN 6:20

However, we can go the extra mile. We can explicitly specify that this column references the column of another table. The way one does this is by utilizing column constraints when creating the table. To see our options we can go back to the ALTER TABLE flow chart and expand the sub-flow chart named “ column-constraint .”

JOIN 7:10

In this flow chart we will see a further sub-flow chart, “ foreign-key-clause ”.

JOIN 7:15

And now we see how we can tell SQLite that our new columns references the column of another table. We start with the

REFERENCES 7:28

Then we say which table we are referencing: ALTER TABLE "reminders" ADD COLUMN "remindersListID" INTEGER NOT NULL REFERENCES "remindersLists"

REFERENCES 7:37

Followed by an open parentheses with the column name that we are referencing and a closed parentheses: ALTER TABLE "reminders" ADD COLUMN "remindersListID" INTEGER NOT NULL REFERENCES "remindersLists"("id")

REFERENCES 7:53

We can further, optionally, provide more customizations to this constraint. We won’t describe all of these, but the one that is most interesting is the ON DELETE clause. It allows us to decide what SQLite should do with reminders when we delete a list.

REFERENCES 8:25

We have a few options:

REFERENCES 8:26

We can tell SQLite to

NULL 9:06

We can also tell SQLite to revert the foreign key back to its default value, but again that is not applicable here because we can’t specify a default for remindersListID .

NULL 9:18

Next we can tell SQLite to cascade the deletion. That is, it will delete every reminder that is associated with the list we are deleting. This option is applicable to us, and is probably what we want.

NULL 9:33

Similar to cascade there is the restrict option. This tells SQLite to not allow us to delete a list if there are reminders associated with it. This is also a perfectly valid stance to take on this constraint.

NULL 9:47

And finally there is the NO ACTION option, which tells SQLite to leave the reminder’s foreign key pointing at a non-existing list row.

NULL 10:00

Of all these options we will go with

CASCADE 10:09

We could further provide options for what to do when the foreign table is updated. This would happen if we updated the id of a list and we wanted to make sure to update the foreign key in the reminders table to point to the new ID. This can be handy for certain kinds of foreign key relationships, but it is not useful for us right now because one should never change the primary key of a row. Once a row is created its primary key should forever remain constant. And so we will not go any further into the ON UPDATE branch of this flow chart, nor we will we discuss

DEFERRABLE 10:45

OK, this now tells SQLite that the integer held in the remindersListID must be equal to a value in the id column in the remindersLists table, and if a list is deleted, then all associated reminders will be deleted. And ideally, if you ever try to store an ID that is not a valid remindersLists ID, you should get an error.

DEFERRABLE 11:10

However, trying to execute this does lead to a SQL error: Runtime error: Cannot add a NOT NULL column with default value NULL

DEFERRABLE 11:13

Since we already have reminders in our table we cannot add a NOT NULL column unless we also specify a default. Typically the reminders and remindersLists tables would have been created at the same time, and so we wouldn’t have to worry about this.

DEFERRABLE 11:35

But because we are doing this incrementally we have to worry about such problems. We will simplify things by just saying that all reminders already in our table will default be put into our “Personal” list: ALTER TABLE "reminders" ADD COLUMN "remindersListID" INTEGER NOT NULL DEFAULT 1 REFERENCES "remindersLists"("id") ON DELETE CASCADE

DEFERRABLE 11:54

Now this executes successfully, and we can select all reminders from our database: sqlite> SELECT * FROM "reminders"; ┌────┬─────────────────────┬─────────────┬──────────┬─────────────────┐ │ id │ name │ isCompleted │ priority │ remindersListID │ ├────┼─────────────────────┼─────────────┼──────────┼─────────────────┤ │ 1 │ Groceries │ 0 │ 1 │ 1 │ │ 2 │ Get haircut │ 0 │ │ 1 │ │ 3 │ Take a walk │ 1 │ 1 │ 1 │ │ 4 │ Buy concert tickets │ 1 │ 2 │ 1 │ │ 5 │ Call accountant │ 0 │ 3 │ 1 │ └────┴─────────────────────┴─────────────┴──────────┴─────────────────┘ …to see that indeed each reminder has remindersListID of 1.

DEFERRABLE 12:04

To make things a little more interesting let’s assign each reminder a more appropriate list. We will put the “Groceries” and “Buy concert tickets” reminders in the “Family” list, and the “Call accountant” reminder in the “Work” list using the

UPDATE 13:41

Now when we select all of the reminders we see something a little more realistic: sqlite> SELECT * FROM "reminders"; ┌────┬─────────────────────┬─────────────┬──────────┬─────────────────┐ │ id │ name │ isCompleted │ priority │ remindersListID │ ├────┼─────────────────────┼─────────────┼──────────┼─────────────────┤ │ 1 │ Groceries │ 0 │ 1 │ 3 │ │ 2 │ Get haircut │ 0 │ │ 1 │ │ 3 │ Take a walk │ 1 │ 1 │ 1 │ │ 4 │ Buy concert tickets │ 1 │ 2 │ 3 │ │ 5 │ Call accountant │ 0 │ 3 │ 2 │ └────┴─────────────────────┴─────────────┴──────────┴─────────────────┘

UPDATE 13:49

And if we were to try to set the remindersListID of “Groceries” to a non-existent list ID, like 999: UPDATE "reminders" SET "remindersListID" = 999 WHERE "id" = 5; …we will see that this unfortunately executes successfully and updated the data in the table: sqlite> SELECT * FROM "reminders"; ┌────┬─────────────────────┬─────────────┬──────────┬─────────────────┐ │ id │ name │ isCompleted │ priority │ remindersListID │ ├────┼─────────────────────┼─────────────┼──────────┼─────────────────┤ │ 1 │ Groceries │ 0 │ 1 │ 3 │ │ 2 │ Get haircut │ 0 │ │ 1 │ │ 3 │ Take a walk │ 1 │ 1 │ 1 │ │ 4 │ Buy concert tickets │ 1 │ 2 │ 3 │ │ 5 │ Call accountant │ 0 │ 3 │ 999 │ └────┴─────────────────────┴─────────────┴──────────┴─────────────────┘

UPDATE 14:11

This is unfortunately very bad for data integrity. We have told SQLite that the remindersListID columns references the id column of the remindersLists table, and further that our foreign key is non-

NULL 14:45

Foreign key constraints are to databases as type systems are to programming languages. We use types in Swift because it provides a baseline of confidence and hygiene in our applications so that we don’t make silly mistakes like multiply an integer with an array of strings. Foreign keys are the baseline of hygiene we can employ in our databases. They give us confidence that data split across many tables is connected together securely as we would hope.

NULL 15:16

The reason this is happening is because SQLite didn’t have strict foreign keys from the beginning and instead later added it. And in order to maintain backwards compatibility, foreign key enforcement is off by default. It will allow storing invalid IDs. But it is easy to enable a stricter mode of SQLite to have it enforce foreign key constraints: PRAGMA foreign_keys = ON;

NULL 16:44

Let’s quickly update our reminder to a valid list: UPDATE "reminders" SET "remindersListID" = 3 WHERE "id" = 5;

NULL 16:56

That was successful, but when we try to set it back to 999 we get an error: sqlite> UPDATE "reminders" SET "remindersListID" = 999 WHERE "id" = 5; Runtime error: FOREIGN KEY constraint failed (19)

NULL 17:13

So this is really great. SQLite now has our back when editing reminders and lists to make sure that a reminder always belongs to at least one list. It will be impossible for a reminder to be orphaned and left without a list. And this should be reminiscent of how Swift has our back so that when we have a variable that holds a string, it can only ever hold a string and nothing else. SQL joins

NULL 17:36

We have now spent a good amount of time learning new syntax in SQL for altering tables and update rows in a table. And we’ve even gone deep into the concept of foreign key constraints and strictness of foreign keys in SQLite. But we still haven’t discussed joins, which is what this whole episode is supposed to be about. Stephen

NULL 17:53

That’s true, but now that we have a new table in our schema it is now possible to discuss joins. We want the ability to join the reminders and lists tables together so that in just one single query we can get the data for our reminders, along with the list it is associated with.

NULL 18:09

And once we know the basics of that we will show how it’s even possible to aggregate data across joins, such as if we wanted to select all lists along with a count of the number of reminders in each list.

NULL 18:19

It’s incredibly powerful stuff, so let’s get started.

NULL 18:24

Joins can be used in

SELECT 18:34

In the

FROM 18:44

…which shows that a join is performed by specifying the table you want to join from, then a join operator, then the table you want to join to, and finally a join constraint.

FROM 18:57

The “join-operator” has a flow chart of its own showing the various flavors of join. There are left, right, full, inner, and cross joins, and the left, right and full joins can also be “outer”. You are even allowed to not specify the word

JOIN 19:24

All of these flavors of join allow you to be very precise with what you require when you join the tables together. We are going to focus on just the plain

FULL 19:42

So, to start let’s just do a plain

JOIN 19:56

We get a whole bunch of rows. 15 to be precise. And we also get a lot of columns. 7 of them.

JOIN 20:05

What does this data set mean?

JOIN 20:07

This data set has a row for each combination of rows in each table. So we see 5 rows of “Personal,” where each row corresponds to one of the reminders we have. And we see the exact same thing play out for “Work” and “Family”. And there are 15 rows because we have 5 reminders and 3 lists, and 5 times 3 is 15. Further, we have a column in this data set for each column in reminders and remindersLists , which means there are 7 columns since reminders has 5 columns and remindersLists has 2 columns.

JOIN 20:42

This means that if you formed a join such as this: -- SELECT * FROM table1 JOIN table2

JOIN 20:50

…the resulting data set would have the following number of rows: -- count(table1) * count(table2)

JOIN 21:01

…and the following number of columns: -- columns(table1) + columns(table2)

JOIN 21:10

This kind of resulting data set is also known as a cartesian product . Cartesian product is a math name stemming from geometry, but it can be generalized to its modern meaning of an unconditional and unfiltered product of two data sets by taking all ordered combinations of values from the data sets.

JOIN 21:38

However, it is quite rare to literally want all combinations of values from two or more data sets. Right now we aren’t taking into account that these reminders belong to exactly one list, and instead for each reminder we are getting a row for every list.

JOIN 21:40

This is where

JOIN 21:45

You can either specify an

ON 21:55

We start by specifying

ON 22:02

And then we specify a condition that determines when to take a row from the full cartesian product of the two data sets.

ON 22:09

In our situation, we want to take only the rows where the remindersListID from the reminders table matches the id from the remindersLists table: SELECT * FROM remindersLists JOIN reminders ON reminders.remindersListID = remindersLists.id

ON 22:40

Running this query we now see we select all of the reminders along with the list they are associated with: sqlite> SELECT * ...> FROM remindersLists ...> JOIN reminders ...> ON reminders.remindersListID = remindersLists.id; ┌────┬──────────┬────┬─────────────────────┬─────────────┬──────────┬─────────────────┐ │ id │ name │ id │ name │ isCompleted │ priority │ remindersListID │ ├────┼──────────┼────┼─────────────────────┼─────────────┼──────────┼─────────────────┤ │ 3 │ Family │ 1 │ Groceries │ 0 │ │ 3 │ │ 3 │ Personal │ 2 │ Get haircut │ 0 │ │ 1 │ │ 2 │ Personal │ 3 │ Take a walk │ 1 │ 1 │ 1 │ │ 1 │ Family │ 4 │ Buy concert tickets │ 1 │ 2 │ 3 │ │ 2 │ Work │ 5 │ Call accountant │ 0 │ 3 │ 2 │ └────┴──────────┴────┴─────────────────────┴─────────────┴──────────┴─────────────────┘

ON 22:51

We have successfully discarded all of the pairs of reminders and lists that do not belong together, and it’s all thanks to the

ON 22:58

Now you may wonder why do we even need this special syntax for an

WHERE 23:11

And this does indeed select the exact same rows. However, there are a few reasons why we do not write joins this way.

WHERE 23:15

First of all, if we have a complex

WHERE 24:18

Second, while

WHERE 24:34

And finally, and probably least importantly, it may be a little more efficient putting join constraints in the

ON 24:47

It’s also worth noting that you can go to the other extreme when defining constraints. Instead of putting any filtering logic in a

ON 25:07

We of course would never want to do this, but it does show that there are liberties we can take when deciding how to filter the rows of a join. In general, we feel that the best practice is to try to localize join-specific logic in the

WHERE 25:23

Now that we know how to join two tables we can start to do more fun things with it. Everything we have learned so far about

SELECT 25:32

For example, we can select just a subset of the columns, like the name of the reminder and the name of the list: sqlite> SELECT remindersLists.title, reminders.title ...> FROM remindersLists ...> JOIN reminders ON remindersLists.id = reminders.remindersListID; ┌──────────┬────────────────────┐ │ title │ title │ ├──────────┼────────────────────┤ │ Work │ Take a walk │ │ Personal │ Buy concert tickets│ │ Work │ Call accountant │ │ Family │ Groceries │ │ Family │ Get haircut │ └──────────┴────────────────────┘

SELECT 25:46

That has successfully gotten rid of any columns that we are not interested in.

SELECT 25:49

We can also specify order clauses, and we can order by any column from either table, and in any direction. So we could first order by the list’s name, and then the reminder’s title: sqlite> SELECT remindersLists.title, reminders.title ...> FROM remindersLists ...> JOIN reminders ON remindersLists.id = reminders.remindersListID ...> ORDER BY remindersLists.title, reminders.title; ┌──────────┬─────────────────────┐ │ title │ title │ ├──────────┼─────────────────────┤ │ Family │ Get haircut │ │ Family │ Groceries │ │ Personal │ Buy concert tickets │ │ Work │ Call accountant │ │ Work │ Take a walk │ └──────────┴─────────────────────┘

SELECT 26:05

Now we see we get all of the “Family” reminders, then “Personal” reminders, and finally the “Work” reminders.

SELECT 26:12

And then further, for any two reminders in the same list, we can choose to sort them by their title in a descending fashion if we want: sqlite> SELECT remindersLists.title, reminders.title ...> FROM remindersLists ...> JOIN reminders ON remindersLists.id = reminders.remindersListID ...> ORDER BY remindersLists.title, reminders.title DESC; ┌──────────┬─────────────────────┐ │ name │ title │ ├──────────┼─────────────────────┤ │ Family │ Groceries │ │ Family │ Get haircut │ │ Personal │ Buy concert tickets │ │ Work │ Take a walk │ │ Work │ Call accountant │ └──────────┴─────────────────────┘

SELECT 26:20

Or we could sort by their priority in a descending fashion: sqlite> SELECT remindersLists.title, reminders.title ...> FROM remindersLists ...> JOIN reminders ON remindersLists.id = reminders.remindersListID ...> ORDER BY remindersLists.title, reminders.priority DESC; ┌──────────┬─────────────────────┐ │ name │ title │ ├──────────┼─────────────────────┤ │ Family │ Groceries │ │ Family │ Get haircut │ │ Personal │ Buy concert tickets │ │ Work │ Call accountant │ │ Work │ Take a walk │ └──────────┴─────────────────────┘

SELECT 26:27

And now we see that amongst family reminders, “Groceries” is highest priority, and amongst work reminders, “Call accountant” is higher priority. Aggregates across joins

SELECT 26:37

We finally are starting to see what joins in SQL are, and they really do give a lot of power to relational databases, such as SQLite. And although we have only shown joining a single table, you can join more tables. This becomes very useful when you need to fetch related data from multiple tables, or if you have a many-to-many relationship between tables, such as tags associated with reminders. But we won’t look at multi-joins right now. Brandon

SELECT 27:02

Things get a lot more interesting when we start mixing in some aggregate functions, such as the count , max , avg and group_concat functions. Previously these functions allowed us to calculate an aggregate across an entire table, such as the count of rows, average of priorities across reminders, or even the ability to concatenate all of the titles of reminders into a single string, all happening directly in SQLite.

SELECT 27:26

But aggregates with joins allow us to further perform these computations on a per-row basis. For some examples, we can select all lists along with a count of how many reminders are in each list. Or we can select all lists along with an average of the priority of all reminders in each list. Or we select all lists along with a comma-separated string of the titles of all reminders in each list.

SELECT 27:52

These are all powerful queries, but they also barely scratch the surface of what is possible. So, let’s dig in.

SELECT 28:00

Suppose we want to select all the lists from our database, and further get a comma separated list of all the reminders in each list. It will act as a kind of summary of each list.

SELECT 28:14

We could of course start by selecting all of the lists: sqlite> SELECT * FROM remindersLists; ┌────┬──────────┐ │ id │ name │ ├────┼──────────┤ │ 1 │ Personal │ │ 2 │ Work │ │ 3 │ Family │ └────┴──────────┘

SELECT 28:22

And then for each ID in this dataset we could execute an additional query to concatenate all of the reminder titles for each list: sqlite> SELECT group_concat(title) FROM reminders ...> WHERE remindersListID = 1; ┌─────────────────────────┐ │ group_concat(title) │ ├─────────────────────────┤ │ Get haircut,Take a walk │ └─────────────────────────┘ sqlite> SELECT group_concat(title) FROM reminders ...> WHERE remindersListID = 2; ┌─────────────────────┐ │ group_concat(title) │ ├─────────────────────┤ │ Call accountant │ └─────────────────────┘ sqlite> SELECT group_concat(title) FROM reminders ...> WHERE remindersListID = 3; ┌───────────────────────────────┐ │ group_concat(title) │ ├───────────────────────────────┤ │ Groceries,Buy concert tickets │ └───────────────────────────────┘

SELECT 28:57

This technically works, but has quite a few drawbacks. First, and foremost, we are executing 4 queries. And if we had 100 lists, we would need to execute 101 queries. SQLite can handle lots of queries quite well, but this is unnecessary thrashing of the database can cause slowdown of it is not done carefully.

SELECT 29:26

Second, and probably more annoying, is that we have to perform extra work in our application code to merge all of these datasets together. We would ideally like to package up all of this information into an array of data types like this: struct RemindersListsSummary { let remindersList: RemindersList let reminderTitles: String } let summaries: [RemindersListsSummary] But we would be responsible for doing a bunch of data munging in order to turn these 4 SQL queries into a single array.

SELECT 30:17

And luckily SQL comes with the tools that allow us to aggregate all of this information with just a single query. We can select the title of reach reminder list, along with a comma-separated string of all the reminders in the list: sqlite> SELECT remindersLists.title, group_concat(reminders.title) ...> FROM remindersLists ...> JOIN reminders ON remindersLists.id = reminders.remindersListID; ┌────────┬──────────────────────────────────────────────────────────────┐ │ title │ group_concat(reminders.title) │ ├────────┼──────────────────────────────────────────────────────────────┤ │ Family │ Groceries,Get haircut,Take a walk,Buy concert tickets,Call a │ │ │ ccountant │ └────────┴──────────────────────────────────────────────────────────────┘

SELECT 31:13

Although, that does not produce a result set that I would expect. We just seem to be getting one list, and the concatenation of all reminder titles. Not the titles of reminders on a per-list basis.

SELECT 31:29

Well, there is one more thing to specify. Right now SQLite doesn’t know that we want to concatenate the reminder titles on a per-list basis. There are many ways we can aggregate the reminders titles. We could aggregate for the completed and uncompleted state of reminders. Or we could aggregate the titles for each priority.

SELECT 31:58

In order to distinguish all of these ways of aggregating we must specify what is known as a GROUP BY clause. Heading back to the fantastic SQLite documentation for

WHERE 32:20

The expression you provide for the GROUP BY clause will be computed for each row of the data set, and when two rows have an equal group, their values will be aggregated with the specified aggregate function.

WHERE 32:36

And we can first explore this with a simple

SELECT 33:07

Here we see the incomplete reminders and complete reminders all concatenated on their own rows.

SELECT 33:16

We can also group by priority: sqlite> SELECT priority, group_concat(title) FROM reminders GROUP BY priority; ┌──────────┬───────────────────────┐ │ priority │ group_concat(title) │ ├──────────┼───────────────────────┤ │ │ Groceries,Get haircut │ │ 1 │ Take a walk │ │ 2 │ Buy concert tickets │ │ 3 │ Call accountant │ └──────────┴───────────────────────┘

SELECT 33:25

We see each reminder grouped in each row by priority.

SELECT 33:34

And this works for any aggregate function, like counting: sqlite> SELECT priority, count(title) FROM reminders GROUP BY priority; ┌──────────┬──────────────┐ │ priority │ count(title) │ ├──────────┼──────────────┤ │ │ 2 │ │ 1 │ 1 │ │ 2 │ 1 │ │ 3 │ 1 │ └──────────┴──────────────┘

SELECT 33:51

And then going back to our join query, if we want to concatenate the reminders’ titles on a per list basis, we should group by the ID of the remindersLists table: sqlite> SELECT remindersLists.title, group_concat(reminders.title) ...> FROM reminders ...> JOIN remindersLists ...> ON reminders.remindersListID = remindersLists.id ...> GROUP BY remindersLists.id; ┌──────────┬──────────────────────────────────────┐ │ title │ group_concat(reminders.title) │ ├──────────┼──────────────────────────────────────┤ │ Personal │ Get haircut,Take a walk,Walk the dog │ │ Work │ Call accountant │ │ Family │ Groceries,Buy concert tickets │ └──────────┴──────────────────────────────────────┘

SELECT 34:15

And now we get some results that we expect. We can clearly see the name of each list, along with the count of reminders in those lists.

SELECT 34:27

We can also specify a

JOIN 34:58

However, things do get tricky if you want to aggregate a subset of rows from the reminders table. For example, suppose we only wanted to aggregate the uncompleted reminders’ titles. You might hope you could just use a

WHERE 35:34

That does seem to actually work. We only have one incomplete reminder in each list, so the result set doesn’t look particularly interesting. Let’s add another row just so that we can make sure the concatenate is working: sqlite> INSERT INTO reminders (title, remindersListID) ...> VALUES ('Walk the dog', 1);

WHERE 36:01

And now re-running the query we see that the personally list does indeed have two reminders in it: sqlite> SELECT remindersLists.title, group_concat(reminders.title) ...> FROM remindersLists ...> JOIN reminders ...> ON remindersLists.id = reminders.remindersListID ...> WHERE NOT reminders.isCompleted ...> GROUP BY remindersLists.id; ┌──────────┬───────────────────────────────┐ │ title │ group_concat(reminders.title) │ ├──────────┼───────────────────────────────┤ │ Personal │ Get haircut,Walk the dog │ │ Work │ Call accountant │ │ Family │ Groceries │ └──────────┴───────────────────────────────┘

WHERE 36:09

This all behaves how I would hope, but unfortunately there is a problem lurking in the shadows. Suppose we want to run a very similar query, but this time we want the concatenated titles of completed reminders: sqlite> SELECT remindersLists.title, ...> group_concat(reminders.title) ...> FROM remindersLists JOIN reminders ...> ON remindersLists.id = reminders.remindersListID ...> AND reminders.isCompleted ...> GROUP BY remindersLists.id; ┌──────────┬───────────────────────────────┐ │ title │ group_concat(reminders.title) │ ├──────────┼───────────────────────────────┤ │ Personal │ Take a walk │ │ Family │ Buy concert tickets │ └──────────┴───────────────────────────────┘

WHERE 36:30

Now suddenly we only get two lists. What happened to the “Work” list?

WHERE 36:43

Well, the “Work” lists does not have any completed reminders in it, and so all of its rows were filtered out by the

WHERE 37:07

And this is why we say that

WHERE 37:22

SQLite does provide tools for this kind of logic, for example, the

CASE 38:41

But this is quite verbose. SQLite provides a helper function called iif that works more like a ternary expression, and allows you to perform a conditional test and then provide one of two values based on the condition.

CASE 39:08

It’s a function that takes 3 arguments. The first is the condition you are testing, the second is the value to use when the condition is true, and the third is the value to use when the condition is false: sqlite> SELECT iif(1, 'true', 'false'); ┌─────────────────────────┐ │ iif(1, 'true', 'false') │ ├─────────────────────────┤ │ true │ └─────────────────────────┘ sqlite> SELECT iif(0, 'true', 'false'); ┌─────────────────────────┐ │ iif(0, 'true', 'false') │ ├─────────────────────────┤ │ false │ └─────────────────────────┘

CASE 39:20

And we can use this function directly inline our group_concat expression. We can say that if the reminder is completed, we will use its title, otherwise we will use NULL: sqlite> SELECT remindersLists.title, ...> group_concat( (x1...> iif(reminders.isCompleted, NULL, reminders.title) (x1...> ) ...> FROM remindersLists ...> JOIN reminders ...> ON remindersLists.id = reminders.remindersListID ...> GROUP BY remindersLists.id; ┌──────────┬────────────────────────────────────────────────────────────┐ │ title │ group_concat(iif(reminders.isCompleted, NULL, reminders.ti │ ├──────────┼────────────────────────────────────────────────────────────┤ │ Personal │ Take a walk │ │ Work │ │ │ Family │ Buy concert tickets │ └──────────┴────────────────────────────────────────────────────────────┘

CASE 40:09

And now we get the “Work” list showing even though it does not have any completed reminders.

CASE 40:20

However, there is still a small problem with this. But to see this we need a new list added to the table that does not have any reminders assigned to it, whether they are completed or uncompleted.

CASE 40:32

So, let’s add a new list: sqlite> INSERT INTO remindersLists (title) ...> VALUES ('Secret Project');

CASE 40:42

And then run our query again: sqlite> SELECT remindersLists.title, ...> group_concat( (x1...> iif(reminders.isCompleted, NULL, reminders.title) (x1...> ) ...> FROM remindersLists ...> JOIN reminders ...> ON remindersLists.id = reminders.remindersListID ...> GROUP BY remindersLists.id; ┌──────────┬────────────────────────────────────────────────────────────┐ │ title │ group_concat(iif(reminders.isCompleted, reminders.title, N │ ├──────────┼────────────────────────────────────────────────────────────┤ │ Personal │ Take a walk │ │ Work │ │ │ Family │ Buy concert tickets │ └──────────┴────────────────────────────────────────────────────────────┘

CASE 40:46

Sadly we do not see the new “Secret Project” list. This is happening because the

ON 41:14

To see this concretely, let’s for a moment remove all aggregation from the query, as well as the

ON 41:43

This is the full cartesian product of these data sets, showing an unfiltered pairing of every list with every reminder, even if they don’t belong together.

ON 41:54

And in particular, the “Secret Project” rows do not belong with any of the reminders rows: │ Secret Project │ Groceries │ │ Secret Project │ Get haircut │ │ Secret Project │ Take a walk │ │ Secret Project │ Buy concert tickets │ │ Secret Project │ Call accountant │ │ Secret Project │ Walk the dog │

ON 42:03

And so once you supply an

ON 42:18

But, there is another kind of join we can perform that allows us to have every single row from the left side appear, even if there are no associated rows on the right side. It is called a LEFT JOIN : sqlite> SELECT remindersLists.title, ...> group_concat( (x1...> iif(reminders.isCompleted, NULL, reminders.title) (x1...> ) ...> FROM remindersLists ...> LEFT JOIN reminders ...> ON remindersLists.id = reminders.remindersListID ...> GROUP BY remindersLists.id; ┌────────────────┬──────────────────────────────────────────────────────┐ │ title │ group_concat(iif(reminders.isCompleted, NULL, remind │ ├────────────────┼──────────────────────────────────────────────────────┤ │ Personal │ Take a walk │ │ Work │ │ │ Family │ Buy concert tickets │ │ Secret Project │ │ └────────────────┴──────────────────────────────────────────────────────┘

ON 42:46

That simple change has fixed the query. We are now getting all lists back, regardless if they have have completed reminders, or even if they have no reminders at all.

ON 43:10

These tricks work for other kinds of aggregations too. For example, we can count the number of incomplete reminders per list by essentially swapping group_concat out for count : sqlite> SELECT remindersLists.title, ...> count( (x1...> iif(reminders.isCompleted, NULL, reminders.title) (x1...> ) ...> FROM remindersLists ...> LEFT JOIN reminders ...> ON remindersLists.id = reminders.remindersListID ...> GROUP BY remindersLists.id; ┌────────────────┬───────────────────────────────────────────────────────┐ │ title │ count(iif(reminders.isCompleted, NULL, reminders.id)) │ ├────────────────┼───────────────────────────────────────────────────────┤ │ Personal │ 2 │ │ Work │ 1 │ │ Family │ 1 │ │ Secret Project │ 0 │ └────────────────┴───────────────────────────────────────────────────────┘

ON 44:04

It’s amazing to see how easy SQL can perform complex queries and aggregates like this with a simple and succinct syntax.

ON 44:13

And there’s one last additional piece of SQL syntax we want to show off that is closely related to aggregates. After the aggregate is performed you will often want to filter the results based on the aggregated data. For example, suppose we wanted to select only the lists that have at least one incomplete reminder. In particular, we do not want to select the “Secret Project” list.

ON 45:01

We may think we can use a

WHERE 45:18

This does not work because it is not possible to refer to results of aggregate functions in a

WHERE 45:55

If we go back to the main

WHERE 46:14

So, if we wanted to select only the lists that have more than one incomplete reminder, we can do so like this: sqlite> SELECT remindersLists.title, ...> count( (x1...> iif(reminders.isCompleted, NULL, reminders.id) (x1...> ) ...> FROM remindersLists ...> LEFT JOIN reminders ...> ON remindersLists.id = reminders.remindersListID ...> GROUP BY remindersLists.id ...> HAVING count(iif(reminders.isCompleted, NULL, reminders.id)) > 1; ┌────────────────┬───────────────────────────────────────────────────────┐ │ title │ count(iif(reminders.isCompleted, NULL, reminders.id)) │ ├────────────────┼───────────────────────────────────────────────────────┤ │ Personal │ 2 │ └────────────────┴───────────────────────────────────────────────────────┘

WHERE 46:50

And it’s a little verbose to have to specify the full count and iif function all over again. To help with this we can introduce an alias for our computed column, and then use that alias in the

HAVING 47:37

And just like that we are execute an incredibly complex query. It counts the number of incomplete reminders in each list, and further filters out any lists that have less than 2 incomplete reminders. And even if we had hundreds of lists and tens of thousands of reminders, this query could be made to run in just a few milliseconds. SQL is just an incredibly powerful language. Next time: Joins in Swift

HAVING 48:05

We have now completed a deep dive into SQL joins. We didn’t cover absolutely everything there is to know about them, but we did cover a lot:

HAVING 48:12

We now know how to join two tables together in a query, such as selecting all reminders along with the title of its associated list. Stephen

HAVING 48:20

We also know how to aggregate across joins of tables. For example, we were able to select the count of all reminders in each list as just a single query. SQL is great at aggregating data like this across many tables and rows. Brandon

HAVING 48:34

And finally, we dipped our toes in some more advanced queries, such as counting the number of incomplete reminders for each list, which required us to aggregate across a conditional expression and to use a left join instead of a regular join.

HAVING 48:49

This is all really powerful stuff, but we also just had an entire episode without writing a single line of Swift code. I think that’s a first for Point-Free! Stephen

HAVING 48:57

So now it is time to start updating our query builder to support joins. We want a simple API for joining two tables together along some constraint, and then some ability to select columns from either table, or perform predicate logic with the columns of each table, as well as ordering and aggregating across the two tables.

HAVING 49:14

Let’s dig in…next time! References SQLite The SQLite home page https://www.sqlite.org Downloads Sample code 0320-sql-building-pt7 Point-Free A hub for advanced Swift programming. Brought to you by Brandon Williams and Stephen Celis . Content Become a member The Point-Free Way Beta previews Gifts Videos Collections Free clips Blog More About Us Community Slack Mastodon Twitter BlueSky GitHub Contact Us Privacy Policy © 2026 Point-Free, Inc. All rights are reserved for the videos and transcripts on this site. All other content is licensed under CC BY-NC-SA 4.0 , and the underlying source code to run this site is licensed under the MIT License .