We now have a statically checked, type-safe way to select columns from a table in Swift. We simply use the select static method on our table type, then provide a variadic list of key paths of the columns we want to select, and we will magically get a valid SQL statement written for us that can query the database.

However, we can make things so, so much better. While the form of select we have built so far looks nice, and it’s how most SQL libraries out there handle things, but it unfortunately makes a lot of SQL functionality impossible to express. It turns out that SELECT statements in SQL have a lot more power than we have demonstrated so far.

Let’s see just how complex SELECTs can be, then see why our key path syntax is not capable of expressing these more complex queries, and then see what we can do to support them.