Query

June 4, 2026 · View on GitHub

Queries are a class of Morel expressions that operate on collections. A typical query takes one or more collections as input and returns a collection, but there are also variants that return a scalar value such as a bool or a single record.

For example, the following query returns the name and job title of all employees in department 10:

from e in scott.emps
  where e.deptno = 10
  yield {e.ename, e.job};

ename  job
------ ---------
CLARK  MANAGER
KING   PRESIDENT
MILLER CLERK

val it : {ename:string, job:string} bag

(Notice how this result is printed as a table. Morel automatically uses tabular format if the value is a list of records or atomic values, provided that you have set("output", "tabular"); in the current session; see properties.)

If you know SQL, you might have noticed that this looks similar to a SQL query:

SELECT e.ename, e.job
FROM scott.emps AS e
WHERE e.deptno = 10;

There are deep similarities between Morel query expressions and SQL, which is expected, because both are based on relational algebra. Any SQL query has an equivalent in Morel, often with similar syntax.

Syntax

The formal syntax of queries is as follows.

exp → (other expressions)
    | from [ scan₁ , ... , scan_s ] step₁ ... step_t [ terminalStep ]
                                relational expression (s ≥ 0, t ≥ 0)
    | exists [ scan₁ , ... , scan_s ] step₁ ... step_t
                                existential quantification (s ≥ 0, t ≥ 0)
    | forall [ scan₁ , ... , scan_s ] step₁ ... step_t require exp
                                universal quantification (s ≥ 0, t ≥ 0)

scan → pat in exp [ on exp ]    iteration
    | pat = exp [ on exp ]      single iteration
    | val                       unbounded variable

step → distinct                 distinct step
    | except [ distinct ] exp₁ , ... , exp_e
                                except step (e ≥ 1)
    | group exp₁ [ compute exp₂ ]
                                group step
    | intersect [ distinct ] exp₁ , ... , exp_i
                                intersect step (i ≥ 1)
    | [ left | right | full ] join scan₁ , ... , scan_s
                                join step (s ≥ 1)
    | order exp                 order step
    | skip exp                  skip step
    | take exp                  take step
    | through pat in exp        through step
    | union [ distinct ] exp₁ , ... , exp_u
                                union step (u ≥ 1)
    | where exp                 filter step
    | yield exp                 yield step
    | yieldAll exp              yieldAll step

terminalStep → into exp         into step
    | compute exp               compute step

groupKey → [ id = ] exp

agg → [ id = ] exp [ of exp ]

A query is a from, exists or forall keyword followed by one or more scans, then followed by zero or more steps. (A forall query must end with a require step, and a from query may end with an into or compute terminal step.)

For example, the query

from e in scott.emps,
    d in scott.depts on e.deptno = d.deptno
  where e.deptno = 10
  yield {d.dname, e.ename, e.job};

has two scans (e in scott.emps and d in scott.depts on e.deptno = d.deptno) and two steps (where e.deptno = 10 and yield {d.dname, e.ename, e.job}).

In the following sections we will look at scans and steps in more detail. We will focus on from for now, and will cover exists and forall in quantified queries.

Finally, remember that a query is an expression. You can evaluate a query by typing it into the shell, just like any other expression. Also, you can use a query anywhere in a Morel program that an expression is valid, such as in a case expression, the body of a fn lambda, or the argument to a function call. Because Morel is strongly typed, the type of the query expression has to match where it is being used. Most queries return a collection, but quantified queries (exists and forall) and queries with a terminal step (compute or into) return a scalar value, and therefore are particularly easy to use in expressions.

Scan

A scan is a source of rows. The most common form, "id in collection", assigns each element of collection to id in turn and then invokes the later steps in the pipeline.

A scan is like a "for" loop in a language such as Java or Python.

The collection can have elements of any type. In SQL, the elements must be records, but in Morel they may be atomic values, lists, lists of lists, records that contain lists of records, or anything else.

(* Query over a list of integers. *)
from i in [1, 2, 3, 4, 5]
  where i mod 2 = 0;

2
4

val it : int list

If the collection has a structured type, you can use a pattern to deconstruct it.

(* Query over a list of (string, int) pairs. *)
from (name, age) in [("shaggy", 17), ("scooby", 7)]
  yield {s = name ^ " is " ^ Int.toString(age) ^ "."};

shaggy is 17.
scooby is 7.

val it : {s:string} list

Multiple scans

If there are multiple scans, the query generates a cartesian product:

from i in [2, 3],
  s in ["ab", "cde"];

i s
- ---
2 ab
2 cde
3 ab
3 cde

val it : {i:int, s:string} list

If you want to add a join condition, you can append an on clause:

from e in scott.emps,
    d in scott.depts on e.deptno = d.deptno
  where e.job = "MANAGER"
  yield {e.ename, d.dname};

dname      ename
---------- -----
RESEARCH   JONES
SALES      BLAKE
ACCOUNTING CLARK

val it : {dname:string, ename:string} bag

(The on clause is not allowed on the first scan.)

If you want scans later in a query, use the join step.

from c in clients
  where c.city = "BOSTON"
  join e in scott.emps on c.contact = e.empno,
      d in scott.depts on e.deptno = d.deptno
  yield {c.cname, e.ename, d.dname};

cname  dname ename
------ ----- ------
Apple  SALES MARTIN
Disney SALES ALLEN
Ford   SALES WARD
IBM    SALES MARTIN

Lateral scans and nested data

Multiple scans are a convenient way of dealing with nested data.

(* Define the shipments data set; each shipment has one or
   more nested items. *)
val shipments =
  [{id=1, shipping=10.0, items=[{product="soda", quantity=12},
                                {product="beer", quantity=3}]},
   {id=2, shipping=7.5, items=[{product="cider",quantity=4}]}];

(* Flatten the data set by joining each shipment to its own
   items. *)
from s in shipments,
    i in s.items
  yield {s.id, i.product, i.quantity};

id product quantity
-- ------- --------
 1 soda          12
 1 beer           3
 2 cider          4

val it : {id:int, product:string, quantity:int} list

Note that the second scan uses current row from the first scan (s appears in the expression s.items). SQL calls this a lateral join (because lateral means "sideways" and one scan is looking "sideways" at the other scan). Lateral joins are only activated in SQL when you use the keywords LATERAL or UNNEST, but Morel's scans and joins are always lateral. As a result, queries over nested data are easy and concise in Morel.

Single-row scan

A scan with = syntax iterates over a single value. While pat = exp is just syntactic sugar for pat in [exp], it is nevertheless a useful way to add a column to the current row.

(* Iterate over a list of integers and compute whether
   they are odd. *)
from i in [1, 2, 3, 4, 5],
    odd = (i mod 2 = 1);

i odd
- -----
1 true
2 false
3 true
4 false
5 true

val it : {i:int, odd:bool} list

(* Equivalent using "in" and a singleton list. *)
from i in [1, 2, 3, 4, 5],
    odd in [(i mod 2 = 1)];

i odd
- -----
1 true
2 false
3 true
4 false
5 true

val it : {i:int, odd:bool} list

Empty scan

In case you are wondering, yes, a query with no scans is legal. It produces one row with zero fields.

from;

val it = [()] : unit list

You can even feed that one row into a pipeline.

from
  where true
  yield 1 + 2;

3

val it : int list

Step

A query is a pipeline of data flowing through relational operators. The scans introduce rows into the pipeline, and the steps are the relational operators that these rows flow through.

Each step has a contract with its preceding and following step: what fields does it consume, and what fields are produced. A query begins with a set of scans, and each scan defines a number of variables (usually one, unless the scan has a complex pattern).

The following query defines two fields: deptno of type int and emp with a record type.

from deptno in [10, 20],
    emp in scott.emps on emp.deptno = deptno;

(Unlike SQL, the fields of a record are not automatically unnested. If you wish to access the job field of an employee record, then you must write emp.job; the unqualified expression job is invalid.)

The deptno and emp fields can be consumed in a following yield step, which produces fields deptno, job, initial:

from deptno in [10, 20],
    emp in scott.emps on emp.deptno = deptno
  yield {deptno, emp.job, initial = String.sub(emp.ename, 1)};

deptno initial job
------ ------- ---------
10     L       MANAGER
10     I       PRESIDENT
10     I       CLERK
20     M       CLERK
20     O       MANAGER
20     C       ANALYST
20     D       CLERK
20     O       ANALYST

val it : {deptno:int, initial:char, job:string} bag

In the following sections, we define each of Morel's step types and how they map input fields to output fields.

Step expressions

Expressions within query steps use the same syntax as expressions elsewhere in Morel, but they execute within an enhanced environment that provides additional operations.

Query step expressions have access to three special operations not available in other contexts:

current - references the current row being processed
elements - references the collection of elements in the current group
ordinal - provides the position/index of the current row

Most query steps evaluate once per row and run within a specialized environment. This applies to all steps except:

compute
except
intersect
into
skip
take
through
union

An expression in a per-row step has access to:

Field definitions from the preceding step in the query pipeline
Special operations: ordinal and current expressions for row-specific processing

The `current` expression

The current expression refers to the current row. If there are multiple fields, its type is a record with the fields defined by the preceding step. If there is only one field, its type is the type of that field.

(* Multiple fields. Current is a record. *)
from i in [1, 2],
    j in ["a", "b"]
  yield current;

i j
- -
1 a
1 b
2 a
2 b

val it : {i:int, j:string} list

(* Single anonymous field. Current is an atom. *)
from i in [1, 2, 3]
  yield i mod 2
  yield current;
val it = [1,0,1] : int list

(* Single field named "dname". Current is an atom, but you
   can also refer to the field by name. *)
from d in scott.depts
  yield d.dname
  yield current;
val it = ["ACCOUNTING","RESEARCH","SALES","OPERATIONS"]
  : string bag

from d in scott.depts
  yield d.dname
  yield dname;
val it = ["ACCOUNTING","RESEARCH","SALES","OPERATIONS"]
  : string bag

(* Yield is a record expression with a single field.
   Current is a record. *)
from i in [1, 2, 3]
  yield {j = i + 1}
  yield current;
val it = [{j=2}, {j=3}, {j=4}] : {j:int} list

The `elements` expression

The elements expression returns the collection of elements in the current group. It can only be used in the compute clause of a group step, or in a compute step.

(* For each department, compute the total salary of the clerks. *)
from e in scott.emps
  group e.deptno
    compute {s = (from x in elements
                    where x.job = "CLERK"
                    compute sum over x.sal)};

deptno s
------ ------
10     1300.0
20     1900.0
30     950.0

val it : {deptno:int, s:real} list

(* Gather the departments in each region. *)
let
  fun region d =
    case d.loc of
        "CHICAGO" => "MIDWEST"
      | "DALLAS" => "SOUTH"
      | _ => "NORTHEAST"
in
  from d in scott.depts
    group {r = region d} compute elements
    order r
end;

val it =
  [{r="MIDWEST",
    elements=[{deptno=30,dname="SALES",loc="CHICAGO"}]},
   {r="NORTHEAST",
    elements=
    [{deptno=10,dname="ACCOUNTING",loc="NEW YORK"},
     {deptno=40,dname="OPERATIONS",loc="BOSTON"}]},
   {r="SOUTH",
    elements=[{deptno=20,dname="RESEARCH",loc="DALLAS"}]}]
  : {elements:{deptno:int, dname:string, loc:string} bag, r:string} list

(* How many employees in each department? *)
from e in scott.emps
  group e.deptno compute {c = length elements}
  order deptno;

c deptno
- ------
3 10
5 20
6 30

val it : {c:int, deptno:int} list

elements is only available inside a compute step, or the compute clause of a group step.

from e in scott.emps
  yield length elements;

stdIn:2.16-2.24 Error: 'elements' is only valid in a 'compute' clause
  raised at: stdIn:2.16-2.24

The `ordinal` expression

The ordinal expression refers to the ordinal number of the current row, starting at 0.

(* Print the top 5 employees by salary, and their rank. *)
from e in scott.emps
  order DESC e.sal
  take 5
  yield {e.ename, e.sal, rank = ordinal + 1};

ename rank sal
----- ---- ------
KING  1    5000.0
SCOTT 2    3000.0
FORD  3    3000.0
JONES 4    2975.0
BLAKE 5    2850.0

val it : {ename:string, rank:int, sal:real} list

ordinal is only available in steps whose input is ordered.

from e in scott.emps
  yield {e.ename, e.sal, rank = ordinal + 1};
> stdIn:2.33-2.40 Error: cannot use 'ordinal' in unordered query
>   raised at: stdIn:2.33-2.40

Step list

Name	Summary
`distinct`	Removes duplicate rows from the current collection.
`except`	Returns the set (or multiset) difference between the current collection and one or more argument collections.
`group`	Performs aggregation across groups of rows.
`intersect`	Returns the set (or multiset) intersection between the current collection and one or more argument collections.
`join`	Joins one or more scans to the current collection.
`order`	Sorts the current collection by an expression.
`skip`	Skips a given number of rows from the current collection.
`take`	Limits the number of rows to return from the current collection.
`through`	Calls a table function, with the current collection as an argument, and starts a scan over the collection it returns.
`union`	Returns the set (or multiset) union between the current collection and one or more argument collections.
`unorder`	Makes the current collection unordered.
`where`	Emits rows of the current collection for which a given predicate evaluates to `true`.
`yield`	For each row in the current collection, evaluates an expression and emits it as a row.

The following steps produce a single scalar or record value. Because the output is not a collection, no further steps are possible, and therefore they are called terminal steps.

It can be unwieldy to use a query in an expression such as if or case if the query returns a collection. Queries with a terminal step, and forall and exists queries, are easy to embed in an expression.

Name	Summary
`compute`	Applies aggregate functions to the current collection.
`into`	Applies a function to the current collection.
`require`	Evaluates the predicate of a `forall` query.

Distinct step

distinct

Description

Removes duplicate rows from the current collection.

The output fields are the same as the input fields.

SQL	Morel	Remarks
`SELECT`	`yield`	While `SELECT` must be the first keyword of a SQL query, you may use `yield` at any point in a Morel pipeline. It often occurs last, and you can omit it if the output record already has the right shape.
`FROM`	`from`	Unlike SQL `FROM`, `from` is the first keyword in a Morel query.
`JOIN`	`join`	SQL `JOIN` is part of the `FROM` clause, but Morel `join` is a step.
`WHERE`	`where`	Morel `where` is equivalent to SQL `WHERE`.
`HAVING`		Use a `where` after a `group`.
`DISTINCT`	`distinct`	SQL `DISTINCT` is part of the `SELECT` clause, but Morel `distinct` is a step, shorthand for `group`
`ORDER BY`	`order`	Morel `order` is equivalent to SQL `ORDER BY`.
`LIMIT`	`take`	Morel `take` is equivalent to SQL `LIMIT`.
`OFFSET`	`skip`	Morel `skip` is equivalent to SQL `OFFSET`.
`UNION`	`union`	Morel `union` is equivalent to SQL `UNION ALL`; `union distinct` is equivalent to `UNION`.
`INTERSECT`	`intersect`	Morel `intersect` is equivalent to SQL `INTERSECT ALL`; `intersect distinct` is equivalent to `INTERSECT`.
`EXCEPT`	`except`	Morel `except` is equivalent to SQL `EXCEPT ALL`; `except distinct` is equivalent to `EXCEPT`. (Some dialects use `MINUS` rather than `EXCEPT`.)
`EXISTS`	`exists`	SQL `EXISTS` is a unary operator whose operand is a query, but Morel `exists` is a query that returns `true` if the query has at least one row.
-	`forall`	Morel `forall` is a query that returns `true` if a predicate is true for all rows.
`IN`	`elem`	SQL `IN` is a binary operator whose right operand is either a query or a list (but not an array or multiset); Morel `elem` is the equivalent operator, and its right operand can be any collection, including a query.
`NOT IN`	`notelem`	Morel `notelem` is equivalent to SQL `NOT IN`, but without SQL's confusing NULL-value semantics.
-	`yieldall`	Morel `yieldall` evaluates a collection expression and outputs one row for each element of that collection.