The syntax of SELECT clauses $⟨$sqlSelectClause$\mathrm{⟩}$ is the following:

A SELECT clause consists of one or more fields of the form $⟨$sqlSelectField$\mathrm{⟩}$. Each field has a label $⟨$lab$\mathrm{⟩}$. If the as $\mathrm{⟨}$lab${}_i$$\mathrm{⟩}$ of the $i$-th field (the first field is 1st) is omitted, it is complemented by adding as $i$. No two fields may have the same label.

For $⟨$sqlSelectClause$\mathrm{⟩}$ with $n$ fields, if the $⟨$sqlexp${}_i$$\mathrm{⟩}$ of its $i$-th field ($1\le i\le n$) $\mathrm{⟨}$sqlexp${}_i$$\mathrm{⟩}$ as $\mathrm{⟨}$lab${}_i$$\mathrm{⟩}$ has the $(\tau ,w)▷\tau {}_i$ type, the type of entire $⟨$sqlSelectClause$\mathrm{⟩}$ is of the type

($\tau$, {$\mathrm{⟨}$lab${}_{\mathrm{1}}$$\mathrm{⟩}$ : $\tau {}_{\mathrm{1}}$, $\mathrm{\dots }$, $\mathrm{⟨}$lab${}_n$$\mathrm{⟩}$ : $\tau {}_n$} list, $w$) SQL.query.

The syntax of the From clauses $⟨$sqlFromClause$\mathrm{⟩}$ is the following:

The first syntax constructs a FROM clause. The second form embeds the value of $⟨$exp$\mathrm{⟩}$ in the ontext as a FROM clause. $⟨$exp$\mathrm{⟩}$ must be of the SQL.from type.

The commas in the first form sometimes conflicts with those of SML#’s tuple expressions. For example,

(1, _sql from #db.t1, #db.t2, #db.t3)

is ambiguous because there are two possible interpretations: this denotes a pair of an integer and FROM clause, or a 4-tuple whose second element is a FROM clause. The FROM clause must be parenthesized as either

(1, _sql (from #db.t1, #db.t2, #db.t3))

if the expression means the former, or

(1, _sql (from #db.t1), #db.t2, #db.t3)

if it means the latter. This is why the syntactic restriction described in Section 22.2 is introduced. In contrast to the above example,

(_sql from #db.t1, #db.t2, #db.t3)

is a single FROM clause expression with no ambiguity because it begins with _sql.

A table expression $⟨$sqlTable$\mathrm{⟩}$ denotes a table. Its syntax is as follows:

The labeling expressions must has the following rules:

• •

  The labeling expressions has the strongest associativity among the table expressions.

• •

  If a table reference #$\mathrm{⟨}$vid$\mathrm{⟩}$.$\mathrm{⟨}$lab$\mathrm{⟩}$ is not labeled with a labeling expression, it is labeled by the same name as the table as if as $\mathrm{⟨}$lab$\mathrm{⟩}$ is specified.

The syntax of $⟨$sqlTable$\mathrm{⟩}$ is restricted by the following rules:

• •

  All as $\mathrm{⟨}$lab$\mathrm{⟩}$s occurring in a $⟨$sqlFromClauses$\mathrm{⟩}$ must be distinct. Thus, if a table is referenced twice or more times in a FROM clause, the references of the table must be labeled differently by as.

• •

  Each of the $⟨$sqlTable${}_{\mathrm{1}}$$\mathrm{⟩}$ and $⟨$sqlTable${}_{\mathrm{2}}$$\mathrm{⟩}$ in a $\mathrm{⟨}$sqlTable${}_{\mathrm{1}}$$\mathrm{⟩}$ natural join $\mathrm{⟨}$sqlTable${}_{\mathrm{2}}$$\mathrm{⟩}$ must be neither an inner or cross join.

• •

  The $⟨$sqlTable$\mathrm{⟩}$ of a $\mathrm{⟨}$sqlTable$\mathrm{⟩}$ as $\mathrm{⟨}$lab$\mathrm{⟩}$ must be neither an inner or cross join.

For a a FROM clause with $n$ labels of $\mathrm{⟨}$sqlTable${}_i$$\mathrm{⟩}$ as $\mathrm{⟨}$lab${}_i$$\mathrm{⟩}$ ($1\le i\le n$), if the type of each $⟨$sqlTable${}_i$$\mathrm{⟩}$ is $\tau {}_i$, the type of entire FROM clause is the following:

({$\mathrm{⟨}$lab${}_{\mathrm{1}}$$\mathrm{⟩}$ : $\tau {}_{\mathrm{1}}$, $\mathrm{\dots }$, $\mathrm{⟨}$lab${}_n$$\mathrm{⟩}$ : $\tau {}_n$} list, $w$) SQL.from

The type of $⟨$sqlTable${}_i$$\mathrm{⟩}$ is decided as follows:

• •

  The type of #$\mathrm{⟨}$vid$\mathrm{⟩}$.$\mathrm{⟨}$lab$\mathrm{⟩}$ is $\tau {}^{\prime }$ where the type of $⟨$vid$\mathrm{⟩}$ is ($\tau$, $w$) SQL.db and $\tau$ is a record type including at least a $⟨$lab$\mathrm{⟩}$:$\tau {}^{\prime }$ field.

• •

  The type of $\mathrm{⟨}$sqlTable$\mathrm{⟩}$ as $\mathrm{⟨}$lab$\mathrm{⟩}$ is identical to that of $⟨$sqlTable$\mathrm{⟩}$.

• •

  The type of ($\mathrm{⟨}$sqlselect$\mathrm{⟩}$) is $\tau$ where $⟨$sqlselect$\mathrm{⟩}$ has type ($\tau$,$w$) SQL.query.

• •

  The type of $\mathrm{⟨}$sqlTable${}_{\mathrm{1}}$$\mathrm{⟩}$ natural join $\mathrm{⟨}$sqlTable${}_{\mathrm{2}}$$\mathrm{⟩}$ is the record type obtained by performing the natural join operation on the record types of the two tables.

SML# does not compute the type of inner and cross joins. Therefore, inner and cross joins cannot be labeled by as directly (syntactically restricted). The type of these joins are represented by the record type of the entire FROM clause.

A FROM clause computes a table obtained by joining the tables it refers to. The record type {$\mathrm{⟨}$lab${}_{\mathrm{1}}$$\mathrm{⟩}$ : $\tau {}_{\mathrm{1}}$, $\mathrm{\dots }$, $\mathrm{⟨}$lab${}_n$$\mathrm{⟩}$ : $\tau {}_n$} of a FROM clause represents a row of the computed table. Each field of the record type corresponds to a labeled subset of columns. The SQL column reference expressions occurring in other clauses refer to these labels.

Here is the syntax of the WHERE clauses $⟨$sqlWhereClause$\mathrm{⟩}$:

The first form construct a WHERE clause. Its type is ($\tau$ -> $\tau$, $w$) SQL.whr if $⟨$sqlexp$\mathrm{⟩}$ has type $(\tau ,w)▷\mathtt{\text{SQL.bool3}}$.

The second form embeds the result of $⟨$exp$\mathrm{⟩}$ in the current context. $⟨$exp$\mathrm{⟩}$ must be of the SQL.whr type.

The syntax of the GROUP BY clauses $⟨$sqlGroupClause$\mathrm{⟩}$ is the following:

Different from other clauses, the ...($\mathrm{⟨}$exp$\mathrm{⟩}$) notation is not allowed for the GROUP BY clauses and therefore they cannot be separated from the SELECT queries. Syntactically, a GROUP BY clause must occur along with a SELECT and FROM clauses.

Each $⟨$sqlexp${}_i$$\mathrm{⟩}$ comma-separated in a GROUP BY clause must be of type $(\tau ,w)▷\tau {}_i$ where the type of the associated FROM clause is ($\tau$, $w$) SQL.from. The GROUP BY clause splits the table computed by the FROM clause to the groups of rows by using keys specified in $⟨$sqlexp$\mathrm{⟩}$s, as described later.

A GROUP BY clause may have just one HAVING clause. The value expression of a HAVING clause is a condition to filter the row groups; therefore, its type must be $(\tau {}^{\prime },w)▷\mathtt{\text{SQL.bool3}}$.

The second form group by () is one of the standard SQL syntax that makes a single group of all rows. In a conventional SQL, if an aggregate function is used without a GROUP BY clause, the query aggregates the entire table implicitly. For example, the SQL query

SELECT avg(e.age) FROM employee AS e

is a correct SQL query that computes the average of the e.age column. In contrast, SML# forces such an aggregating query to have group by (). The above query must be written in SML# as follows:

fn db => _sql select avg(#e.age) from #db.employee as e group by ()

This SML# expression is evaluated to the SQL query

SELECT avg(e.age) FROM employee AS e GROUP BY ()

as in the SML# expression.

The type $\tau {}^{\prime }$ of the row groups computed by a GROUP BY clause is computed roughly in the following steps:

1. 1.

   Let the type of rows before grouping be

   $$\tau =\mathtt{\text{{}}k{}_1\mathtt{\text{:}}\tau {}_1\mathtt{\text{, }}\mathrm{\dots }\mathtt{\text{, }}k{}_n\mathtt{\text{:}}\tau {}_n\mathtt{\text{}}}$$

   where the type of the FROM clause is $\tau$ list.

2. 2.

   Group the rows in lists. Hence, the group type is naturally $\tau$ list list.

3. 3.

   Transpose the row group type {$k{}_{\mathrm{1}}$:$\tau {}_{\mathrm{1}}$, $\mathrm{\dots }$, $k{}_n$:$\tau {}_n$} list to $\tau {}^t=\mathtt{\text{{}}k{}_1\mathtt{\text{:}}\tau {}_1\mathtt{\text{ list, }}\mathrm{\dots }\mathtt{\text{, }}k{}_n\mathtt{\text{:}}\tau {}_n\mathtt{\text{ list}}}$.

For the transposition in the step 3, the set of columns $k{}_1,\mathrm{\dots },k{}_n$ must be determined at compile time, as opposed to the record-polymorphic property of queries. SML# obtains the set of columns from column reference expressions ocurring in the entire query with a GROUP BY clause. For each column reference #$\mathrm{⟨}$lab${}_{\mathrm{1}}$$\mathrm{⟩}$.$\mathrm{⟨}$lab${}_{\mathrm{2}}$$\mathrm{⟩}$ occurring as either a key specified in the GROUP BY clause or column reference expression referring to a grouped value, if the column reference is identical to one of the group keys, the column refers to the single key value and therefore the type of the column is not changed in the result of the GROUP BY clause. Otherwise, the type of the column is the list type of its original type. Any other columns, which are never refered to in the query, are ignored and omitted in the result type of the GROUP BY clause.

Note that this type computation is performed based on the syntactic context and hence does not consider variable references. For example,

val q = fn db => _sql select #e.department, avg(#e.salary)
                      from #db.employee as e
                      group by #e.department

is a typechecked query with a GROUP BY clause. However,

val s = _sql select #e.department, avg(#e.salary)
val q = fn db => _sql select...(s)
                      from #db.employee as e
                      group by #e.department

causes a type error because the second example does not have any SELECT clause construction but an embedding, in contrast to the first example that have both a SELECT and GROUP BY clauses in the same syntax of a query. In the first example, the type of the row groups has #e.department and #e.salary columns as expected, but in the second example, the row groups is regarded to have no column since no column reference occur in the same query syntax. As a good manner, when you write a query with group by, you should not use the ...($\mathrm{⟨}$exp$\mathrm{⟩}$) notation for its SELECT clause.

Notes for the observant readers: even when the SELECT clause is sepearated from a query with GROUP BY, if the embedded SELECT clause only refers to the group keys, the query does not cause type errors. For example, edit the above example by removing avg(#e.salary) from the definition of s as follows:

val s = _sql select #e.department
val q = fn db => _sql select...(s)
                      from #db.employee as e
                      group by #e.department

This program does not cause type errors because #e.department is a group key and therefore it is included in the result type of GROUP BY.

The syntax of the ORDER BY clauses $⟨$sqlOrderclause$\mathrm{⟩}$ is the following:

The first form constructs an ORDER BY clause. The second form embeds the result of $⟨$exp$\mathrm{⟩}$ in the current context as an ORDER BY clause. $⟨$exp$\mathrm{⟩}$ must be of the SQL.orderby type.

An ORDER BY clause rearranges the rows computed by the SELECT clause. For the SELECT query computing the rows of type $\tau$, Each $⟨$sqlexp${}_i$$\mathrm{⟩}$ in the ORDER BY clause must be $(\tau ,w)▷\tau {}_i$, and the entire ORDER BY clause must be of type ($\tau$ -> $\tau$, $w$) SQL.orderby.

There are several variations of ORDER BY clause in SQL since it has been extended by the database vendors and standard trucks without backward compatibility. The convention SML# adopts is that an ORDER BY clause may refer to only the result of the SELECT clause. The column reference expression with anonymous table #.$\mathrm{⟨}$lab$\mathrm{⟩}$ is used to refer to a column of the SELECT clause from the ORDER BY clause. The following shows an example:

fn db => _sql select #e.name as name, #.age as age
              from #db.employee as e
              order by #.age

While many database engines allows for ORDER BY to refer to columns other than those of SELECT, SML# prohibits such references. The following program causes type errors, for example:

fn db => _sql select #e.name as name, #.age as age
              from #db.employee as e
              order by #e.department

The OFFSET and LIMIT clauses have same meaning; they returns the rows only in the given range. The major difference of these clauses is who specify them: the OFFSET clause is specified in the standard SQL, and the LIMIT clause is a widely-accepted vender extention. SML# supports both.

The syntax of them $⟨$sqlOffsetOrLimitClause$\mathrm{⟩}$ is as follows:

Note that the keyword offset is used in two ways in the above syntax: offset is either the subclause for a LIMIT clause or the main clause of a OFFSET clause that may have a FETCH subclause. These subclauses are not interchangable. Note also that if an OFFSET clause or its FETCH subclause has a non-constant expression, the expression must be parenthesized.

The type of $⟨$sqlexp$\mathrm{⟩}$ and $⟨$sqlatexp$\mathrm{⟩}$ must be $(\mathtt{\text{{}}},w)▷\text{𝚒𝚗𝚝}$. Therefore, no column reference may appear in these clauses.

Several database engines allows these clauses and subclauses to appear more than one times in a qeury in any order. SML# follows the standard SQL and hence a main clause must be followed by its subclause. A query may contain only one of these clauses.

If more than one SELECT queries are nested, the inner query is a subquery of the outer query. A subquery is referred to as a corelated subquery if it refers to columns introduced by the FROM clause of its outer query. The following is an example of a corelated subquery in the standard SQL:

SELECT e.department AS department, e.name AS name
FROM empoloyee AS e
WHERE e.salary > (SELECT avg(#t.salary)
                  FROM employee as t
                  WHERE t.department = e.department
                  GROUP BY ())

In SML#, a subquery may appear as an value expression $⟨$sqlexp$\mathrm{⟩}$ (see also Section 22.4.8) and in a FROM clause (Section 22.5.2). A subquery may be a corelated subquery if the following conditions are met:

1. 1.

   If a subquery itself and all of its outer queries have FROM clauses not of the form from...($\mathrm{⟨}$exp$\mathrm{⟩}$), the subquery may be a corelated subquery.

This is a translation of the above SQL query into SML#.

fn db => _sql select #e.department as department, #e.name as name
              from #db.empoloyee as e
              where #e.salary > (select avg(#t.salary)
                                 from #db.employee as t
                                 where #t.department = #e.department
                                 group by ())

If one of the nested query has from...($\mathrm{⟨}$exp$\mathrm{⟩}$), subqueries are not to be corelated. For example, if the above example is editted as follows by replacing its FROM clause with from...($\mathrm{⟨}$exp$\mathrm{⟩}$)

let
  val f = fn db => _sql from #db.employee as t
in
  fn db => _sql select #e.department as department, #e.name as name
                from #db.empoloyee as e
                where #e.salary > (select avg(#t.salary)
                                   from...(f db)
                                   where #t.department = #e.department
                                   group by ())
end

the e of #e.department in the subquery is not interpreted as the e of from #db.employee as e in the outer query, but e to be introduced by from ...(x db). Therefore, x db must have e. Concequently, this example causes a type error. Also in the case when the outer query has from...($\mathrm{⟨}$exp$\mathrm{⟩}$), such as

let
  val f = fn db => _sql from #db.empoloyee as e
in
  fn db => _sql select #e.department as department, #e.name as name
                from...(f db)
                where #e.salary > (select avg(#t.salary)
                                   from #db.employee as t
                                   where #t.department = #e.department
                                   group by ())

the subquery is not regarded as corelated one and therefore the e of #e.department is undefined.

Corelated subqueries and GROUP BY clauses can be combined as expected. For example, the following queries the youngest person who have incomes greater than the average saraly of his/her department:

fn db => _sql
  select #e.department, (select min(#t.age)
                         from #db.employee as t
                         where (#t.department = #e.department
                                and (Some) #t.salary > min(#e.salary))
                         group by ())
  from #db.employee as e
  group by #e.department

The inner query aggregates the grouped column #e.salary of the outer query by the min aggregate function. As described in Section #e.salary, the type of GROUP BY is computed from the syntactic context. This is also true even if a query has corelated subqueries.

Notes for the observant readers: A corelated subquery must occur in a outer query, but the subquery is not tightly corelated to the outer query similarly to the static scoping of ML variables. The syntactic restriction of corelated subqueries are introduced just to make the type computation of subqueries possible in a static manner. As long as any SQL value expressions including corelated subqueries are first-class citizens, it is possible to detach a corelated subquery $A$ from a nested query $B$ and embed $A$ to another nested query $C$ by exploiting SML# features. In the resulting query, $A$ embedded in $C$ refers to $C$’s tables, not to $B$. Regardless of the query construction operations, if the type of those operations are consistent, then the resulting SQL query is correct.