Skip to content

More complex SELECTs

We've seen how to create simple queries from our schema. Beam supports other clauses in the SQL SELECT statement.

For these examples, we're going to use the beam-sqlite backend with the provided sample Chinook database. The Chinook database schema is modeled after a fictional record store. It provides several tables containing information on the music as well as the billing operations. Thus, it provides a good 'real-world' demonstration of beam's capabalities.

First, create a SQLite database from the included example.

$ sqlite3 chinook.db < beam-sqlite/examples/chinook.sql

Now, load the chinook database schema in GHCi.

Prelude Database.Beam.Sqlite> :load beam-sqlite/examples/Chinook/Schema.hs
Prelude Chinook.Schema> chinook <- open "chinook.db"

One more thing, before we see more complex examples, let's define a quick utility function.

Prelude Chinook.Schema> let withConnectionTutorial = withDatabaseDebug putStrLn chinook

Let's test it!

We can run all our queries like:

withConnectionTutorial $ runSelectReturningList $ select $ <query>

Let's select all the tracks.

withConnectionTutorial $ runSelectReturningList $ select $ all_ (track chinookDb)

For the rest of the guide, we will also show the generated SQL code for both sqlite and postgres.

all_ (track chinookDb)
SELECT "t0"."TrackId" AS "res0",
       "t0"."Name" AS "res1",
       "t0"."AlbumId" AS "res2",
       "t0"."MediaTypeId" AS "res3",
       "t0"."GenreId" AS "res4",
       "t0"."Composer" AS "res5",
       "t0"."Milliseconds" AS "res6",
       "t0"."Bytes" AS "res7",
       "t0"."UnitPrice" AS "res8"
FROM "Track" AS "t0";

-- With values: []
SELECT "t0"."TrackId" AS "res0",
       "t0"."Name" AS "res1",
       "t0"."AlbumId" AS "res2",
       "t0"."MediaTypeId" AS "res3",
       "t0"."GenreId" AS "res4",
       "t0"."Composer" AS "res5",
       "t0"."Milliseconds" AS "res6",
       "t0"."Bytes" AS "res7",
       "t0"."UnitPrice" AS "res8"
FROM "Track" AS "t0"
SELECT `t0`.`TrackId` AS `res0`,
       `t0`.`Name` AS `res1`,
       `t0`.`AlbumId` AS `res2`,
       `t0`.`MediaTypeId` AS `res3`,
       `t0`.`GenreId` AS `res4`,
       `t0`.`Composer` AS `res5`,
       `t0`.`Milliseconds` AS `res6`,
       `t0`.`Bytes` AS `res7`,
       `t0`.`UnitPrice` AS `res8`
FROM `Track` AS `t0`

Returning a subset of columns

Oftentimes we only care about the value of a few columns, rather than every column in the table. Beam fully supports taking projections of tables. As said before, Q is a Monad. Thus, we can use monadic do notation to only select a certain subset of columns. For example, to fetch only the name of every track:

do tracks <- all_ (track chinookDb)
   pure (trackName tracks)
SELECT "t0"."Name" AS "res0"
FROM "Track" AS "t0";

-- With values: []
SELECT "t0"."Name" AS "res0"
FROM "Track" AS "t0"
SELECT `t0`.`Name` AS `res0`
FROM `Track` AS `t0`

Notice that beam has properly written the SELECT projection to only include the Name field.

We can also return multiple fields, by returning a tuple. Perhaps we would also like to know the composer:

do tracks <- all_ (track chinookDb)
   pure (trackName tracks, trackComposer tracks)
SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1"
FROM "Track" AS "t0";

-- With values: []
SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1"
FROM "Track" AS "t0"
SELECT `t0`.`Name` AS `res0`,
       `t0`.`Composer` AS `res1`
FROM `Track` AS `t0`

You can also return arbitrary expressions in the projection. For example to return the name, composer, unit price, and length in seconds (where the database stores it in milliseconds):

do tracks <- all_ (track chinookDb)
   pure (trackName tracks, trackComposer tracks, trackMilliseconds tracks `div_` 1000)
SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1",
       ("t0"."Milliseconds") / (?) AS "res2"
FROM "Track" AS "t0";

-- With values: [SQLInteger 1000]
SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1",
       ("t0"."Milliseconds") / (1000) AS "res2"
FROM "Track" AS "t0"
SELECT `t0`.`Name` AS `res0`,
       `t0`.`Composer` AS `res1`,
       (`t0`.`Milliseconds`) / (1000) AS `res2`
FROM `Track` AS `t0`

Beam includes instances to support returning up to 6-tuples. To return more, feel free to nest tuples. As an example, we can write the above query as

do tracks <- all_ (track chinookDb)
   pure ((trackName tracks, trackComposer tracks), trackMilliseconds tracks `div_` 1000)
SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1",
       ("t0"."Milliseconds") / (?) AS "res2"
FROM "Track" AS "t0";

-- With values: [SQLInteger 1000]
SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1",
       ("t0"."Milliseconds") / (1000) AS "res2"
FROM "Track" AS "t0"
SELECT `t0`.`Name` AS `res0`,
       `t0`.`Composer` AS `res1`,
       (`t0`.`Milliseconds`) / (1000) AS `res2`
FROM `Track` AS `t0`

Notice that the nesting of tuples does not affect the generated SQL projection. The tuple structure is only used when reading back the row from the database.

The Q monad is perfectly rule-abiding, which means it also implements a valid Functor instance. Thus the above could more easily be written.

fmap (\tracks -> (trackName tracks, trackComposer tracks, trackMilliseconds tracks `div_` 1000)) $
all_ (track chinookDb)
SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1",
       ("t0"."Milliseconds") / (?) AS "res2"
FROM "Track" AS "t0";

-- With values: [SQLInteger 1000]
SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1",
       ("t0"."Milliseconds") / (1000) AS "res2"
FROM "Track" AS "t0"
SELECT `t0`.`Name` AS `res0`,
       `t0`.`Composer` AS `res1`,
       (`t0`.`Milliseconds`) / (1000) AS `res2`
FROM `Track` AS `t0`

WHERE clause

We've seen how to use all_ to select all rows of a table. Sometimes, you would like to filter results based on the result of some condition. For example, perhaps you would like to fetch all customers whose names start with "Jo". We can filter over results using the filter_ function.

filter_ (\customer -> customerFirstName customer `like_` "Jo%") $
all_ (customer chinookDb)
SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ("t0"."FirstName") LIKE (?);

-- With values: [SQLText "Jo%"]
SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ("t0"."FirstName") LIKE ('Jo%')
SELECT `t0`.`CustomerId` AS `res0`,
       `t0`.`FirstName` AS `res1`,
       `t0`.`LastName` AS `res2`,
       `t0`.`Company` AS `res3`,
       `t0`.`Address` AS `res4`,
       `t0`.`City` AS `res5`,
       `t0`.`State` AS `res6`,
       `t0`.`Country` AS `res7`,
       `t0`.`PostalCode` AS `res8`,
       `t0`.`Phone` AS `res9`,
       `t0`.`Fax` AS `res10`,
       `t0`.`Email` AS `res11`,
       `t0`.`SupportRepId` AS `res12`
FROM `Customer` AS `t0`
WHERE (`t0`.`FirstName`) LIKE ('Jo%')

You can use (&&.) and (||.) to combine boolean expressions, as you'd expect. For example, to select all customers whose first name begins with "Jo", last name begins with "S", and who live in either California or Washington:

filter_ (\customer -> ((customerFirstName customer `like_` "Jo%") &&. (customerLastName customer `like_` "S%")) &&.
                      (addressState (customerAddress customer) ==. just_ "CA" ||. addressState (customerAddress customer) ==. just_ "WA")) $
        all_ (customer chinookDb)
SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE (?))
       AND (("t0"."LastName") LIKE (?)))
  AND ((CASE
            WHEN (("t0"."State") IS NULL)
                 AND ((?) IS NULL) THEN ?
            WHEN (("t0"."State") IS NULL)
                 OR ((?) IS NULL) THEN ?
            ELSE ("t0"."State")=(?)
        END)
       OR (CASE
               WHEN (("t0"."State") IS NULL)
                    AND ((?) IS NULL) THEN ?
               WHEN (("t0"."State") IS NULL)
                    OR ((?) IS NULL) THEN ?
               ELSE ("t0"."State")=(?)
           END));

-- With values: [SQLText "Jo%",SQLText "S%",SQLText "CA",SQLInteger 1,SQLText "CA",SQLInteger 0,SQLText "CA",SQLText "WA",SQLInteger 1,SQLText "WA",SQLInteger 0,SQLText "WA"]
SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE ('Jo%'))
       AND (("t0"."LastName") LIKE ('S%')))
  AND ((("t0"."State") IS NOT DISTINCT
        FROM ('CA'))
       OR (("t0"."State") IS NOT DISTINCT
           FROM ('WA')))
SELECT `t0`.`CustomerId` AS `res0`,
       `t0`.`FirstName` AS `res1`,
       `t0`.`LastName` AS `res2`,
       `t0`.`Company` AS `res3`,
       `t0`.`Address` AS `res4`,
       `t0`.`City` AS `res5`,
       `t0`.`State` AS `res6`,
       `t0`.`Country` AS `res7`,
       `t0`.`PostalCode` AS `res8`,
       `t0`.`Phone` AS `res9`,
       `t0`.`Fax` AS `res10`,
       `t0`.`Email` AS `res11`,
       `t0`.`SupportRepId` AS `res12`
FROM `Customer` AS `t0`
WHERE (((`t0`.`FirstName`) LIKE ('Jo%'))
       AND ((`t0`.`LastName`) LIKE ('S%')))
  AND ((CASE
            WHEN ((`t0`.`State`) IS NULL)
                 AND (('CA') IS NULL) THEN TRUE
            WHEN ((`t0`.`State`) IS NULL)
                 OR (('CA') IS NULL) THEN FALSE
            ELSE (`t0`.`State`) = ('CA')
        END)
       OR (CASE
               WHEN ((`t0`.`State`) IS NULL)
                    AND (('WA') IS NULL) THEN TRUE
               WHEN ((`t0`.`State`) IS NULL)
                    OR (('WA') IS NULL) THEN FALSE
               ELSE (`t0`.`State`) = ('WA')
           END))

Note

We had to use the just_ function above to compare addressState (customerAddress customer). This is because addressState (customerAddress customer) represents a nullable column which beam types as Maybe Text. Just as in Haskell, we need to explicitly unwrap the Maybe type. This is an example of beam offering stronger typing than SQL itself.

LIMIT/OFFSET support

The limit_ and offset_ functions can be used to truncate the result set at a certain length and fetch different portions of the result. They correspond to the LIMIT and OFFSET SQL constructs.

limit_ 10 $ offset_ 100 $
filter_ (\customer -> ((customerFirstName customer `like_` "Jo%") &&. (customerLastName customer `like_` "S%")) &&.
                      (addressState (customerAddress customer) ==. just_ "CA" ||. addressState (customerAddress customer) ==. just_ "WA")) $
        all_ (customer chinookDb)
SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE (?))
       AND (("t0"."LastName") LIKE (?)))
  AND ((CASE
            WHEN (("t0"."State") IS NULL)
                 AND ((?) IS NULL) THEN ?
            WHEN (("t0"."State") IS NULL)
                 OR ((?) IS NULL) THEN ?
            ELSE ("t0"."State")=(?)
        END)
       OR (CASE
               WHEN (("t0"."State") IS NULL)
                    AND ((?) IS NULL) THEN ?
               WHEN (("t0"."State") IS NULL)
                    OR ((?) IS NULL) THEN ?
               ELSE ("t0"."State")=(?)
           END))
LIMIT 10
OFFSET 100;

-- With values: [SQLText "Jo%",SQLText "S%",SQLText "CA",SQLInteger 1,SQLText "CA",SQLInteger 0,SQLText "CA",SQLText "WA",SQLInteger 1,SQLText "WA",SQLInteger 0,SQLText "WA"]
SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE ('Jo%'))
       AND (("t0"."LastName") LIKE ('S%')))
  AND ((("t0"."State") IS NOT DISTINCT
        FROM ('CA'))
       OR (("t0"."State") IS NOT DISTINCT
           FROM ('WA')))
LIMIT 10
OFFSET 100
SELECT `t0`.`CustomerId` AS `res0`,
       `t0`.`FirstName` AS `res1`,
       `t0`.`LastName` AS `res2`,
       `t0`.`Company` AS `res3`,
       `t0`.`Address` AS `res4`,
       `t0`.`City` AS `res5`,
       `t0`.`State` AS `res6`,
       `t0`.`Country` AS `res7`,
       `t0`.`PostalCode` AS `res8`,
       `t0`.`Phone` AS `res9`,
       `t0`.`Fax` AS `res10`,
       `t0`.`Email` AS `res11`,
       `t0`.`SupportRepId` AS `res12`
FROM `Customer` AS `t0`
WHERE (((`t0`.`FirstName`) LIKE ('Jo%'))
       AND ((`t0`.`LastName`) LIKE ('S%')))
  AND ((CASE
            WHEN ((`t0`.`State`) IS NULL)
                 AND (('CA') IS NULL) THEN TRUE
            WHEN ((`t0`.`State`) IS NULL)
                 OR (('CA') IS NULL) THEN FALSE
            ELSE (`t0`.`State`) = ('CA')
        END)
       OR (CASE
               WHEN ((`t0`.`State`) IS NULL)
                    AND (('WA') IS NULL) THEN TRUE
               WHEN ((`t0`.`State`) IS NULL)
                    OR (('WA') IS NULL) THEN FALSE
               ELSE (`t0`.`State`) = ('WA')
           END))
LIMIT 100,
      10

Note

Nested limit_s and offset_s compose in the way you'd expect without generating extraneous subqueries.

Warning

Note that the order of the limit_ and offset_ functions matter. Offseting an already limited result is not the same as limiting an offseted result. For example, if you offset three rows into a limited set of five results, you will get at most two rows. On the other hand, if you offset three rows and then limit the result to the next five, you may get up to five. Beam will generate exactly the query you specify. Notice the difference below, where the order of the clauses made beam generate a query that returns no results.

offset_ 100 $ limit_ 10 $
filter_ (\customer -> ((customerFirstName customer `like_` "Jo%") &&. (customerLastName customer `like_` "S%")) &&.
                      (addressState (customerAddress customer) ==. just_ "CA" ||. addressState (customerAddress customer) ==. just_ "WA")) $
        all_ (customer chinookDb)
SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE (?))
       AND (("t0"."LastName") LIKE (?)))
  AND ((CASE
            WHEN (("t0"."State") IS NULL)
                 AND ((?) IS NULL) THEN ?
            WHEN (("t0"."State") IS NULL)
                 OR ((?) IS NULL) THEN ?
            ELSE ("t0"."State")=(?)
        END)
       OR (CASE
               WHEN (("t0"."State") IS NULL)
                    AND ((?) IS NULL) THEN ?
               WHEN (("t0"."State") IS NULL)
                    OR ((?) IS NULL) THEN ?
               ELSE ("t0"."State")=(?)
           END))
LIMIT 0
OFFSET 100;

-- With values: [SQLText "Jo%",SQLText "S%",SQLText "CA",SQLInteger 1,SQLText "CA",SQLInteger 0,SQLText "CA",SQLText "WA",SQLInteger 1,SQLText "WA",SQLInteger 0,SQLText "WA"]
SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE ('Jo%'))
       AND (("t0"."LastName") LIKE ('S%')))
  AND ((("t0"."State") IS NOT DISTINCT
        FROM ('CA'))
       OR (("t0"."State") IS NOT DISTINCT
           FROM ('WA')))
LIMIT 0
OFFSET 100
SELECT `t0`.`CustomerId` AS `res0`,
       `t0`.`FirstName` AS `res1`,
       `t0`.`LastName` AS `res2`,
       `t0`.`Company` AS `res3`,
       `t0`.`Address` AS `res4`,
       `t0`.`City` AS `res5`,
       `t0`.`State` AS `res6`,
       `t0`.`Country` AS `res7`,
       `t0`.`PostalCode` AS `res8`,
       `t0`.`Phone` AS `res9`,
       `t0`.`Fax` AS `res10`,
       `t0`.`Email` AS `res11`,
       `t0`.`SupportRepId` AS `res12`
FROM `Customer` AS `t0`
WHERE (((`t0`.`FirstName`) LIKE ('Jo%'))
       AND ((`t0`.`LastName`) LIKE ('S%')))
  AND ((CASE
            WHEN ((`t0`.`State`) IS NULL)
                 AND (('CA') IS NULL) THEN TRUE
            WHEN ((`t0`.`State`) IS NULL)
                 OR (('CA') IS NULL) THEN FALSE
            ELSE (`t0`.`State`) = ('CA')
        END)
       OR (CASE
               WHEN ((`t0`.`State`) IS NULL)
                    AND (('WA') IS NULL) THEN TRUE
               WHEN ((`t0`.`State`) IS NULL)
                    OR (('WA') IS NULL) THEN FALSE
               ELSE (`t0`.`State`) = ('WA')
           END))
LIMIT 100,
      0

Backends often differ as to how they implement LIMIT/OFFSET. For example, SQLite requires that LIMIT always be given if an OFFSET is provided. Beam correctly handles this behavior.

offset_ 100 $
filter_ (\customer -> ((customerFirstName customer `like_` "Jo%") &&. (customerLastName customer `like_` "S%")) &&.
                      (addressState (customerAddress customer) ==. just_ "CA" ||. addressState (customerAddress customer) ==. just_ "WA")) $
        all_ (customer chinookDb)
SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE (?))
       AND (("t0"."LastName") LIKE (?)))
  AND ((CASE
            WHEN (("t0"."State") IS NULL)
                 AND ((?) IS NULL) THEN ?
            WHEN (("t0"."State") IS NULL)
                 OR ((?) IS NULL) THEN ?
            ELSE ("t0"."State")=(?)
        END)
       OR (CASE
               WHEN (("t0"."State") IS NULL)
                    AND ((?) IS NULL) THEN ?
               WHEN (("t0"."State") IS NULL)
                    OR ((?) IS NULL) THEN ?
               ELSE ("t0"."State")=(?)
           END))
LIMIT -1
OFFSET 100;

-- With values: [SQLText "Jo%",SQLText "S%",SQLText "CA",SQLInteger 1,SQLText "CA",SQLInteger 0,SQLText "CA",SQLText "WA",SQLInteger 1,SQLText "WA",SQLInteger 0,SQLText "WA"]
SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE ('Jo%'))
       AND (("t0"."LastName") LIKE ('S%')))
  AND ((("t0"."State") IS NOT DISTINCT
        FROM ('CA'))
       OR (("t0"."State") IS NOT DISTINCT
           FROM ('WA')))
  OFFSET 100
SELECT `t0`.`CustomerId` AS `res0`,
       `t0`.`FirstName` AS `res1`,
       `t0`.`LastName` AS `res2`,
       `t0`.`Company` AS `res3`,
       `t0`.`Address` AS `res4`,
       `t0`.`City` AS `res5`,
       `t0`.`State` AS `res6`,
       `t0`.`Country` AS `res7`,
       `t0`.`PostalCode` AS `res8`,
       `t0`.`Phone` AS `res9`,
       `t0`.`Fax` AS `res10`,
       `t0`.`Email` AS `res11`,
       `t0`.`SupportRepId` AS `res12`
FROM `Customer` AS `t0`
WHERE (((`t0`.`FirstName`) LIKE ('Jo%'))
       AND ((`t0`.`LastName`) LIKE ('S%')))
  AND ((CASE
            WHEN ((`t0`.`State`) IS NULL)
                 AND (('CA') IS NULL) THEN TRUE
            WHEN ((`t0`.`State`) IS NULL)
                 OR (('CA') IS NULL) THEN FALSE
            ELSE (`t0`.`State`) = ('CA')
        END)
       OR (CASE
               WHEN ((`t0`.`State`) IS NULL)
                    AND (('WA') IS NULL) THEN TRUE
               WHEN ((`t0`.`State`) IS NULL)
                    OR (('WA') IS NULL) THEN FALSE
               ELSE (`t0`.`State`) = ('WA')
           END))
LIMIT 1000000000
OFFSET 100

Notice that the SQLite query output has provided a dummy LIMIT -1 clause, while the Postgres query has not.

DISTINCT support

SQL can only return unique results from a query through the SELECT DISTINCT statement. Beam supports this using the nub_ command. For example, to get all the unique postal codes where our customers live.

nub_ $ fmap (addressPostalCode . customerAddress) $
  all_ (customer chinookDb)
SELECT DISTINCT "t0"."PostalCode" AS "res0"
FROM "Customer" AS "t0";

-- With values: []
SELECT DISTINCT "t0"."PostalCode" AS "res0"
FROM "Customer" AS "t0"
SELECT DISTINCT `t0`.`PostalCode` AS `res0`
FROM `Customer` AS `t0`