We have now accomplished some amazing things. We can build SQL queries that can express complex WHERE clauses, and do so in a fashion that is similar to how one would write Swift predicate, and there is a layer of type-safety too. And once the query is formed we can render it out to a SQL string that could then be executed with a database library.

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.

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.

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.

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.

This tool is called JOIN and it is one of the most advanced tools in SQL. Before trying to recreate it in our Swift query builder library we must understand it deeply. So, let’s begin with a deep dive into SQL JOIN clauses.