Let’s say we want to know the track title and album name for the shortest tracks that belong to an album in our database.

printResults "select t.name, a.title from tracks t inner join albums a on a.albumid = t.albumid order by t.milliseconds limit 5;" $
  select $ do
    (t :& a) <- from $
      innerJoin (table @Track) . on (table @Album) $ \(t :& a) ->
        t^.TrackAlbumId ==. just (a^.AlbumId)
    limit 5
    orderBy [asc (t^.TrackMilliseconds)]
    pure (t^.TrackName, a^.AlbumTitle)

# select t.name, a.title from tracks t inner join albums a on a.albumid = t.albumid order by t.milliseconds limit 5;
       1. (Value "\201 Uma Partida De Futebol", Value "O Samba Pocon\233")
       2. (Value "Now Sports", Value "Body Count")
       3. (Value "A Statistic", Value "Body Count")
       4. (Value "Oprah", Value "Body Count")
       5. (Value "Commercial 1", Value "House of Pain")

At this point, we finally see why we have been giving our tables short identifiers (t, c, i, a etc.) in both sql and Haskell code. When we are juggling multiple tables at once in a join, we can access fields from either table by choosing the right prefix, and it’s clear where the data is coming from.

The new part in this query is the join. I’ve written it this way:

(t :& a) <- from $
  innerJoin (table @Track) . on (table @Album) $ \(t :& a) ->
    t^.TrackAlbumId ==. just (a^.AlbumId)

The way you’ll find it written by other people is using infix backticks.

(t :& a) <- from $
  table @Track
  `innerJoin` table @Album
  `on` (\(t :& a) -> t^.TrackAlbumId ==. just (a^.AlbumId))

However such a from clause is written, its innermost expression is going to be a function that produces a sql conditional expression indicating how to join rows from both tables with each other. In our case, the function is

let
  joinRowsOn (t :& a) =
    t^.TrackAlbumId ==. just (a^.AlbumId)
in
  -- ...

The :& data constructor serves practically the same purpuse as a tuple – it binds together two table objects that are joined in a query, and by deconstructing that pair we can access each table object individually, as we do here when asserting equivalence between album ids.¹¹ Note also the just function, required because TrackAlbumId is nullable.

This function is paired up with the second table we are joining using the on function, as in

let
  joinRowsOn (t :& a) =
    t^.TrackAlbumId ==. just (a^.AlbumId)
  hasOnClause =
    on (table @Album) joinRowsOn
in
  -- ...

Then the innerJoin function takes the first table and the result of the on function and produces an object that can be passed to the Esqueleto from function.

let
  joinRowsOn (t :& a) =
    t^.TrackAlbumId ==. just (a^.AlbumId)
  hasOnClause =
    on (table @Album) joinRowsOn
  completeJoinExpression =
    innerJoin (table @Track) hasOnClause
in
  select $ do
    joinedTables <- from completeJoinExpression
    -- ...

This will get translated to an inner join sql query.

To get the identifiers of the individual tables out of the joinedTables variable, we can add a pattern match on it, since it is one of those :& pairs that we can deconstruct.

let
  joinRowsOn (t :& a) =
    t^.TrackAlbumId ==. just (a^.AlbumId)
  hasOnClause =
    on (table @Album) joinRowsOn
  completeJoinExpression =
    innerJoin (table @Track) hasOnClause
in
  select $ do
    joinedTables <- from completeJoinExpression
    case joinedTables of
      t :& a -> do
        -- ...

Going backwards again, we can now inline the pattern match:

let
  joinRowsOn (t :& a) =
    t^.TrackAlbumId ==. just (a^.AlbumId)
  hasOnClause =
    on (table @Album) joinRowsOn
  completeJoinExpression =
    innerJoin (table @Track) hasOnClause
in
  select $ do
    (t :& a) <- from completeJoinExpression

and then inline the completeJoinExpression.

let
  joinRowsOn (t :& a) =
    t^.TrackAlbumId ==. just (a^.AlbumId)
  hasOnClause =
    on (table @Album) joinRowsOn
in
  select $ do
    (t :& a) <- from $
      innerJoin (table @Track) hasOnClause

From here, we continue to inline the hasOnClause variable.

let
  joinRowsOn (t :& a) =
    t^.TrackAlbumId ==. just (a^.AlbumId)
in
  select $ do
    (t :& a) <- from $
      innerJoin (table @Track)
      . on (table @Album)
      $ joinRowsOn

We inline the last variable too.

select $ do
  (t :& a) <- from $
    innerJoin (table @Track)
    . on (table @Album)
    $ \(t :& a) -> t^.TrackAlbumId ==. just (a^.AlbumId)

Now we’re back at the query we started with. Hopefully that gives a sense of how these joins are constructed.