The process of retrieving or the command to retrieve data from a database is called a Query. Let's see some examples of queries.

simple query:

>>> :{
let
  query :: Query
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]
    '[]
    '["col" ::: 'Null 'PGint4]
  query = selectStar (from (table #tab))
in printSQL query
:}
SELECT * FROM "tab" AS "tab"

restricted query:

>>> :{
let
  query :: Query
    '[ "tab" ::: 'Table ('[] :=>
       '[ "col1" ::: 'NoDef :=> 'NotNull 'PGint4
        , "col2" ::: 'NoDef :=> 'NotNull 'PGint4 ])]
    '[]
    '[ "sum" ::: 'NotNull 'PGint4
     , "col1" ::: 'NotNull 'PGint4 ]
  query =
    select
      ((#col1 + #col2) `as` #sum :* #col1)
      ( from (table #tab)
        & where_ (#col1 .> #col2)
        & where_ (#col2 .> 0) )
in printSQL query
:}
SELECT ("col1" + "col2") AS "sum", "col1" AS "col1" FROM "tab" AS "tab" WHERE (("col1" > "col2") AND ("col2" > 0))

subquery:

>>> :{
let
  query :: Query
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]
    '[]
    '["col" ::: 'Null 'PGint4]
  query =
    selectStar
      (from (subquery (selectStar (from (table #tab)) `as` #sub)))
in printSQL query
:}
SELECT * FROM (SELECT * FROM "tab" AS "tab") AS "sub"

limits and offsets:

>>> :{
let
  query :: Query
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]
    '[]
    '["col" ::: 'Null 'PGint4]
  query = selectStar
    (from (table #tab) & limit 100 & offset 2 & limit 50 & offset 2)
in printSQL query
:}
SELECT * FROM "tab" AS "tab" LIMIT 50 OFFSET 4

parameterized query:

>>> :{
let
  query :: Query
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGfloat8])]
    '[ 'NotNull 'PGfloat8]
    '["col" ::: 'NotNull 'PGfloat8]
  query = selectStar
    (from (table #tab) & where_ (#col .> param @1))
in printSQL query
:}
SELECT * FROM "tab" AS "tab" WHERE ("col" > ($1 :: float8))

aggregation query:

>>> :{
let
  query :: Query
    '[ "tab" ::: 'Table ('[] :=>
       '[ "col1" ::: 'NoDef :=> 'NotNull 'PGint4
        , "col2" ::: 'NoDef :=> 'NotNull 'PGint4 ])]
    '[]
    '[ "sum" ::: 'NotNull 'PGint4
     , "col1" ::: 'NotNull 'PGint4 ]
  query =
    select (sum_ #col2 `as` #sum :* #col1)
    ( from (table (#tab `as` #table1))
      & groupBy #col1
      & having (#col1 + sum_ #col2 .> 1) )
in printSQL query
:}
SELECT sum("col2") AS "sum", "col1" AS "col1" FROM "tab" AS "table1" GROUP BY "col1" HAVING (("col1" + sum("col2")) > 1)

sorted query:

>>> :{
let
  query :: Query
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]
    '[]
    '["col" ::: 'Null 'PGint4]
  query = selectStar
    (from (table #tab) & orderBy [#col & AscNullsFirst])
in printSQL query
:}
SELECT * FROM "tab" AS "tab" ORDER BY "col" ASC NULLS FIRST

joins:

>>> :set -XFlexibleContexts
>>> :{
let
  query :: Query
    '[ "orders" ::: 'Table (
         '["pk_orders" ::: PrimaryKey '["id"]
          ,"fk_customers" ::: ForeignKey '["customer_id"] "customers" '["id"]
          ,"fk_shippers" ::: ForeignKey '["shipper_id"] "shippers" '["id"]] :=>
         '[ "id"    ::: 'NoDef :=> 'NotNull 'PGint4
          , "price"   ::: 'NoDef :=> 'NotNull 'PGfloat4
          , "customer_id" ::: 'NoDef :=> 'NotNull 'PGint4
          , "shipper_id"  ::: 'NoDef :=> 'NotNull 'PGint4
          ])
     , "customers" ::: 'Table (
         '["pk_customers" ::: PrimaryKey '["id"]] :=>
         '[ "id" ::: 'NoDef :=> 'NotNull 'PGint4
          , "name" ::: 'NoDef :=> 'NotNull 'PGtext
          ])
     , "shippers" ::: 'Table (
         '["pk_shippers" ::: PrimaryKey '["id"]] :=>
         '[ "id" ::: 'NoDef :=> 'NotNull 'PGint4
          , "name" ::: 'NoDef :=> 'NotNull 'PGtext
          ])
     ]
    '[]
    '[ "order_price" ::: 'NotNull 'PGfloat4
     , "customer_name" ::: 'NotNull 'PGtext
     , "shipper_name" ::: 'NotNull 'PGtext
     ]
  query = select
    ( #o ! #price `as` #order_price :*
      #c ! #name `as` #customer_name :*
      #s ! #name `as` #shipper_name )
    ( from (table (#orders `as` #o)
      & innerJoin (table (#customers `as` #c))
        (#o ! #customer_id .== #c ! #id)
      & innerJoin (table (#shippers `as` #s))
        (#o ! #shipper_id .== #s ! #id)) )
in printSQL query
:}
SELECT "o"."price" AS "order_price", "c"."name" AS "customer_name", "s"."name" AS "shipper_name" FROM "orders" AS "o" INNER JOIN "customers" AS "c" ON ("o"."customer_id" = "c"."id") INNER JOIN "shippers" AS "s" ON ("o"."shipper_id" = "s"."id")

self-join:

>>> :{
let
  query :: Query
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]
    '[]
    '["col" ::: 'Null 'PGint4]
  query = selectDotStar #t1
    (from (table (#tab `as` #t1) & crossJoin (table (#tab `as` #t2))))
in printSQL query
:}
SELECT "t1".* FROM "tab" AS "t1" CROSS JOIN "tab" AS "t2"

value queries:

>>> :{
let
  query :: Query '[] '[] '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
  query = values (1 `as` #foo :* true `as` #bar) [2 `as` #foo :* false `as` #bar]
in printSQL query
:}
SELECT * FROM (VALUES (1, TRUE), (2, FALSE)) AS t ("foo", "bar")

set operations:

>>> :{
let
  query :: Query
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]
    '[]
    '["col" ::: 'Null 'PGint4]
  query =
    selectStar (from (table #tab))
    `unionAll`
    selectStar (from (table #tab))
in printSQL query
:}
(SELECT * FROM "tab" AS "tab") UNION ALL (SELECT * FROM "tab" AS "tab")

with queries:

>>> :{
let
  query :: Query
    '[ "t1" ::: 'View
       '[ "c1" ::: 'NotNull 'PGtext
        , "c2" ::: 'NotNull 'PGtext] ]
    '[]
    '[ "c1" ::: 'NotNull 'PGtext
     , "c2" ::: 'NotNull 'PGtext ]
  query = with (
    selectStar (from (view #t1)) `as` #t2 :>>
    selectStar (from (view #t2)) `as` #t3
    ) (selectStar (from (view #t3)))
in printSQL query
:}
WITH "t2" AS (SELECT * FROM "t1" AS "t1"), "t3" AS (SELECT * FROM "t2" AS "t2") SELECT * FROM "t3" AS "t3"

the TableExpression in the select command constructs an intermediate virtual table by possibly combining tables, views, eliminating rows, grouping, etc. This table is finally passed on to processing by the select list. The select list determines which columns of the intermediate table are actually output.

After the select list has been processed, the result table can be subject to the elimination of duplicate rows using selectDistinct.

The simplest kind of query is selectStar which emits all columns that the table expression produces.

A selectDistinctStar emits all columns that the table expression produces and eliminates duplicate rows.

When working with multiple tables, it can also be useful to ask for all the columns of a particular table, using selectDotStar.

A selectDistinctDotStar asks for all the columns of a particular table, and eliminates duplicate rows.

values computes a row value or set of row values specified by value expressions. It is most commonly used to generate a “constant table” within a larger command, but it can be used on its own.

>>> type Row = '["a" ::: 'NotNull 'PGint4, "b" ::: 'NotNull 'PGtext]
>>> let query = values (1 `as` #a :* "one" `as` #b) [] :: Query '[] '[] Row
>>> printSQL query
SELECT * FROM (VALUES (1, E'one')) AS t ("a", "b")

values_ computes a row value or set of row values specified by value expressions.

The results of two queries can be combined using the set operation union. Duplicate rows are eliminated.

The results of two queries can be combined using the set operation unionAll, the disjoint union. Duplicate rows are retained.

The results of two queries can be combined using the set operation intersect, the intersection. Duplicate rows are eliminated.

The results of two queries can be combined using the set operation intersectAll, the intersection. Duplicate rows are retained.

The results of two queries can be combined using the set operation except, the set difference. Duplicate rows are eliminated.

The results of two queries can be combined using the set operation exceptAll, the set difference. Duplicate rows are retained.

with provides a way to write auxiliary statements for use in a larger query. These statements, referred to as CommonTableExpressions, can be thought of as defining temporary tables that exist just for one query.

A CommonTableExpression is an auxiliary statement in a with clause.

render a CommonTableExpression.

render a non-empty AlignedList of CommonTableExpressions.

Expands the outermost JSON object into a set of key/value pairs.

Expands the outermost binary JSON object into a set of key/value pairs.

Expands the outermost JSON object into a set of key/value pairs.

Expands the outermost binary JSON object into a set of key/value pairs.

Returns set of keys in the outermost JSON object.

Returns set of keys in the outermost JSON object.

Expands the JSON expression to a row whose columns match the record type defined by the given table.

Expands the binary JSON expression to a row whose columns match the record type defined by the given table.

Expands the outermost array of objects in the given JSON expression to a set of rows whose columns match the record type defined by the given table.

Expands the outermost array of objects in the given binary JSON expression to a set of rows whose columns match the record type defined by the given table.

Builds an arbitrary record from a JSON object.

Builds an arbitrary record from a binary JSON object.

Builds an arbitrary set of records from a JSON array of objects.

Builds an arbitrary set of records from a binary JSON array of objects.

A TableExpression computes a table. The table expression contains a fromClause that is optionally followed by a whereClause, groupByClause, havingClause, orderByClause, limitClause and offsetClauses. Trivial table expressions simply refer to a table on disk, a so-called base table, but more complex expressions can be used to modify or combine base tables in various ways.

Render a TableExpression

A from generates a TableExpression from a table reference that can be a table name, or a derived table such as a subquery, a JOIN construct, or complex combinations of these. A from may be transformed by where_, group, having, orderBy, limit and offset, using the & operator to match the left-to-right sequencing of their placement in SQL.

A where_ is an endomorphism of TableExpressions which adds a search condition to the whereClause.

A groupBy is a transformation of TableExpressions which switches its Grouping from Ungrouped to Grouped. Use group Nil to perform a "grand total" aggregation query.

A having is an endomorphism of TableExpressions which adds a search condition to the havingClause.

An orderBy is an endomorphism of TableExpressions which appends an ordering to the right of the orderByClause.

A limit is an endomorphism of TableExpressions which adds to the limitClause.

An offset is an endomorphism of TableExpressions which adds to the offsetClause.

A FromClause can be a table name, or a derived table such as a subquery, a JOIN construct, or complex combinations of these.

A real table is a table from the schema.

subquery derives a table from a Query.

view derives a table from a View.

left & crossJoin right. For every possible combination of rows from left and right (i.e., a Cartesian product), the joined table will contain a row consisting of all columns in left followed by all columns in right. If the tables have n and m rows respectively, the joined table will have n * m rows.

left & innerJoin right on. The joined table is filtered by the on condition.

left & leftOuterJoin right on. First, an inner join is performed. Then, for each row in left that does not satisfy the on condition with any row in right, a joined row is added with null values in columns of right. Thus, the joined table always has at least one row for each row in left.

left & rightOuterJoin right on. First, an inner join is performed. Then, for each row in right that does not satisfy the on condition with any row in left, a joined row is added with null values in columns of left. This is the converse of a left join: the result table will always have a row for each row in right.

left & fullOuterJoin right on. First, an inner join is performed. Then, for each row in left that does not satisfy the on condition with any row in right, a joined row is added with null values in columns of right. Also, for each row of right that does not satisfy the join condition with any row in left, a joined row with null values in the columns of left is added.

Bys are used in group to reference a list of columns which are then used to group together those rows in a table that have the same values in all the columns listed. By #col will reference an unambiguous column col; otherwise By2 (#tab ! #col) will reference a table qualified column tab.col.

Renders a By.

A GroupByClause indicates the Grouping of a TableExpression. A NoGroups indicates Ungrouped while a Group indicates Grouped. NoGroups is distinguised from Group Nil since no aggregation can be done on NoGroups while all output Expressions must be aggregated in Group Nil. In general, all output Expressions in the complement of bys must be aggregated in Group bys.

Renders a GroupByClause.

A HavingClause is used to eliminate groups that are not of interest. An Ungrouped TableExpression may only use NoHaving while a Grouped TableExpression must use Having whose conditions are combined with .&&.

Render a HavingClause.

SortExpressions are used by sortBy to optionally sort the results of a Query. Asc or Desc set the sort direction of a NotNull result column to ascending or descending. Ascending order puts smaller values first, where "smaller" is defined in terms of the .< operator. Similarly, descending order is determined with the .> operator. AscNullsFirst, AscNullsLast, DescNullsFirst and DescNullsLast options are used to determine whether nulls appear before or after non-null values in the sort ordering of a Null result column.

Render a SortExpression.

The right-hand side is a subQuery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subQuery result. The result of in_ is true if any equal subquery row is found. The result is false if no equal row is found (including the case where the subquery returns no rows).

>>> printSQL $ true `in_` values_ (true `as` #foo)
TRUE IN (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))

The left-hand side of this form of rowIn is a row constructor. The right-hand side is a subQuery, which must return exactly as many columns as there are expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result. The result of rowIn is true if any equal subquery row is found. The result is false if no equal row is found (including the case where the subquery returns no rows).

>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression '[] '[] 'Ungrouped '[])) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
>>> printSQL $ myRow `rowIn` values_ myRow
ROW(1, FALSE) IN (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))

>>> printSQL $ true `eqAll` values_ (true `as` #foo)
TRUE = ALL (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))

>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression '[] '[] 'Ungrouped '[])) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
>>> printSQL $ myRow `rowEqAll` values_ myRow
ROW(1, FALSE) = ALL (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))

>>> printSQL $ true `eqAny` values_ (true `as` #foo)
TRUE = ANY (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))

>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression '[] '[] 'Ungrouped '[])) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
>>> printSQL $ myRow `rowEqAny` values_ myRow
ROW(1, FALSE) = ANY (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))

>>> printSQL $ true `neqAll` values_ (true `as` #foo)
TRUE <> ALL (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))

>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression '[] '[] 'Ungrouped '[])) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
>>> printSQL $ myRow `rowNeqAll` values_ myRow
ROW(1, FALSE) <> ALL (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))

>>> printSQL $ true `neqAny` values_ (true `as` #foo)
TRUE <> ANY (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))

>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression '[] '[] 'Ungrouped '[])) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
>>> printSQL $ myRow `rowNeqAny` values_ myRow
ROW(1, FALSE) <> ANY (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))

>>> printSQL $ true `allLt` values_ (true `as` #foo)
TRUE ALL < (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))

>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression '[] '[] 'Ungrouped '[])) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
>>> printSQL $ myRow `rowLtAll` values_ myRow
ROW(1, FALSE) ALL < (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))

>>> printSQL $ true `ltAny` values_ (true `as` #foo)
TRUE ANY < (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))

>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression '[] '[] 'Ungrouped '[])) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
>>> printSQL $ myRow `rowLtAll` values_ myRow
ROW(1, FALSE) ALL < (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))

>>> printSQL $ true `lteAll` values_ (true `as` #foo)
TRUE <= ALL (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))

>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression '[] '[] 'Ungrouped '[])) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
>>> printSQL $ myRow `rowLteAll` values_ myRow
ROW(1, FALSE) <= ALL (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))

>>> printSQL $ true `lteAny` values_ (true `as` #foo)
TRUE <= ANY (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))

>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression '[] '[] 'Ungrouped '[])) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
>>> printSQL $ myRow `rowLteAny` values_ myRow
ROW(1, FALSE) <= ANY (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))

>>> printSQL $ true `gtAll` values_ (true `as` #foo)
TRUE > ALL (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))

>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression '[] '[] 'Ungrouped '[])) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
>>> printSQL $ myRow `rowGtAll` values_ myRow
ROW(1, FALSE) > ALL (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))

>>> printSQL $ true `gtAny` values_ (true `as` #foo)
TRUE > ANY (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))

>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression '[] '[] 'Ungrouped '[])) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
>>> printSQL $ myRow `rowGtAny` values_ myRow
ROW(1, FALSE) > ANY (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))

>>> printSQL $ true `gteAll` values_ (true `as` #foo)
TRUE >= ALL (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))

>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression '[] '[] 'Ungrouped '[])) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
>>> printSQL $ myRow `rowGteAll` values_ myRow
ROW(1, FALSE) >= ALL (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))

>>> printSQL $ true `gteAny` values_ (true `as` #foo)
TRUE >= ANY (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))

>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression '[] '[] 'Ungrouped '[])) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
>>> printSQL $ myRow `rowGteAny` values_ myRow
ROW(1, FALSE) >= ANY (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))