S-SQL Examples L

Lateral

Basically, the lateral keyword allows access to columns after the from statement and use them earlier in the query. Here is a sample vanilla sql query using lateral, borrowed from https://popsql.com/learn-sql/postgresql/how-to-use-lateral-joins-in-postgresql#data-set

(query
 "select
    pledged_usd,
    avg_pledge_usd,
    amt_from_goal,
    duration,
    (usd_from_goal / duration) as usd_needed_daily
 from kickstarter_data,
    lateral (select pledged / fx_rate as pledged_usd) pu
    lateral (select pledged_usd / backers_count as avg_pledge_usd) apu
    lateral (select goal / fx_rate as goal_usd) gu
    lateral (select goal_usd - pledged_usd as usd_from_goal) ufg
    lateral (select (deadline - launched_at)/86400.00 as duration) dr;")

And now how it would be written in s-sql

(query
 (:select 'pledged-usd 'avg-pledge-usd 'amt-from-goal 'duration
          (:as (:/ 'usd-from-goal 'duration) 'usd-needed-daily)
          :from 'kickstarter-data
          :lateral (:as (:select (:as (:/ 'pledged 'fx-rate)
                                      'pledged-usd))
                        'pu)
          :lateral (:as (:select (:as (:/ 'pledged-usd 'backers-count)
                                      'avg-pledge-usd))
                        'apu)
          :lateral (:as (:select (:as (:/ 'goal 'fx-rate)
                                      'goal-usd))
                        'gu)
          :lateral (:as (:select (:as (:- 'goal-usd 'pledged-usd)
                                      'usd-from-goal))
                        'ufg)
          :lateral (:as (:select (:as (:/ (:- 'deadline 'launched-at) 86400.00)
                                      'duration))
                        'dr)))

Here is an example with :join-lateral

(query
   (:select '*
    :from (:as 'tags 't1)
    :join-lateral (:as
                   (:fetch
                    (:order-by
                     (:select 'm.*
                      :from (:as 'movies 'm)
                      :where (:= 'm.tag-id 't1.id))
                     (:desc 'm.created-at))
                    2)
                   'e1)
    :on (:= 1 1)))

This one has an :inner-join-lateral

(query
      (:select '*
       :from (:as 'tags 't1)
       :inner-join-lateral (:as
                            (:fetch
                             (:order-by
                              (:select 'm.*
                               :from (:as 'movies 'm)
                               :where (:= 'm.tag-id 't1.id))
                              (:desc 'm.created-at))
                             2)
                            'e1)
       :on 't))

A :cross-join-lateral

(query
   (:select '*
    :from (:as 'tags 't1)
    :cross-join-lateral (:as
                         (:fetch
                          (:order-by
                           (:select 'm.*
                            :from (:as 'movies 'm)
                            :where (:= 'm.tag-id 't1.id))
                           (:desc 'm.created-at))
                          2)
                         'e1)))

  (query
   (:select 'geo.zipcode 'geo.state 'movie.name
    :from 'geo
    :cross-join-lateral
    (:as
     (:limit
      (:order-by
       (:select 'movie-name
        :from 'streams
        :where (:= 'geo.zipcode 'streams.zipcode))
       (:desc 'streams.country))
      5)
     (:movie 'name))))

And a :left-join-lateral

(query
   (:select 'p.* (:as 'dads.id 'dad-id) (:as 'moms.id 'mom-id)
    :from (:as 'people 'p)
    :left-join-lateral (:as (:select '*
                             :from 'people
                             :where (:and (:= 'gender "m")
                                          (:= 'surname-1 'p.surname-1)
                                          (:<> 'pack-id 'p.pack-id)))
                            'dads)
    :on 't
    :left-join-lateral (:as (:select '*
                             :from 'people
                             :where (:and (:= 'gender "f")
                                          (:= 'surname-1 'p.surname-2)
                                          (:<> 'pack-id 'p.pack-id)
                                          (:<> 'pack-id 'dads.pack-id)))
                            'moms)
    :on 't))

Like, ilike Example

The sql like operator provides a little bit of fuzzy string matching in a search. The following is a simple example using the sql like operator in s-sql.

(query (:select 'id 'name
                :from 'countries
                :where (:like 'name "%New%")))

((103 "New Caledonia") (58 "New Zealand") (108 "Papua New Guinea"))

The sql ilike operator provides the same thing, but on a case insensitive basis. The following is a simple example using the sql ilike operator in s-sql.

(query (:select 'id 'name
                :from 'countries
                :where (:like 'name "%NEW%")))

((103 "New Caledonia") (58 "New Zealand") (108 "Papua New Guinea"))

Limit and offset

Note that :limit has 2 possible parameters, the limit and the offset. Note that the :order-by and :limit forms are wrapped around the :select form. The only difference between the two queries is the offset parameter.

(let ((list-limit 2)
      (offset 0))
  (query
   (:limit
    (:order-by
     (:select 'countries.id 'countries.name
              :from 'countries)
     'name)
    '$1 '$2)
   list-limit offset))

((82 "Afghanistan") (130 "Albania"))

(let ((list-limit 2) (offset 2))
  (query
   (:limit
    (:order-by
     (:select 'countries.id 'countries.name
              :from 'countries)
     'name)
    '$1 '$2)
   list-limit offset))

((140 "Algeria") (34 "All"))

S-SQL Examples Home Page

Lateral

Like, ilike Example

Limit and offset