Data Query Language (DQL)¶

The heart of both delightql and SQL is the query expression, also called a table expression. A query expression is a unit of code that returns exactly one table. Here, table is synonymous with predicate or relation, regardless of whether the data is persisted via CREATE TABLE. More typically, these results are anonymous and ephemeral – the output of execution within the REPL.

The majority of delightql lives in this section; mastering it is prerequisite to understanding DDL and DML.

A query’s meaning is identical to the table it produces. This substitutability is key to composability: through subqueries and CTEs, query expressions become recursively inductive to any depth. Delightql encourages a particular style of composition – pipelining a relation through transformations, left to right, with consistent associativity and scoping.

Basics: Comments, Strings, and Semicolons¶

Unlike SQL, delightql uses the double forward slash // for comments. The double hyphen -- is not a valid comment in delightql and will fail to parse.

// I am a valid comment
_(one@1)

Delightql only uses double quotes " to delimit a string in code. The single quote is not an optional syntax for strings. The backtick is used for stropping identifiers.

`employee`(*), last_name = "King"

The semicolon ; is so pervasive in other programming languages that this reference must make this the first syntactic clarification: semicolons in delightql are NOT statement separators. Instead they are the special operator UNION CORRESPONDING and are covered in the section on set operators.

employee_2024(*) as x ;
  employee_2025(*) as y
// the above UNIONs the two tables
// with null padding for unshared
// columns

All reserved words¶

Delightql has very few keywords. They are

• as
• not
• and
• or
• in
• true
• false
• null
• of – only for pivots
• groups – only for window functions
• rows – only for window functions
• has – only for tree group destructuring
• asc and ascending – only in order by
• desc and descending – only in order by

Case insensitivity¶

Delightql is case-insensitive. In contrast to Prolog, where capitalization distinguishes variables from atoms. The following all refer to the same identifier:

• employee_id
• Employee_Id
• EMPLOYEE_ID

Stropping¶

Names containing characters outside the bare identifier shape (spaces, for instance) are delimited with backticks: `employee Id`. Keyword spellings do not require stropping in identifier positions: from(*) may name a table, and as:(x) may name a function. Keywords still perform their grammatical roles where an operator or literal is expected.

From¶

Every delightql query begins with a source: one or more tables from which to draw data. SQL calls this selection In contrast to Codd’s original paper, where selection (σ) denoted row filtering–what SQL now calls WHERE., but we will call this access or sourcing.

All Columns¶

employee(*)

* The glob * in argument position requests all columns from a table – equivalent to SELECT * FROM employee. This is the most common way to source data in delightql.

Argumentative Access¶

Arguments within the functor bring columns into scope by position.

employee(employee_id  , last_name ,
         first_name   , title    , reports_to,
         birth_date   , hire_date , address, city,
         state       , country  , postal_code,
         phone       , fax      , email )

This binds each identifier to the column at that ordinal position in the table definition. The arity must match exactly – if employee has 15 columns, the functor must have 15 arguments.

The identifiers you choose become the column names in scope of the position having the identifier.

employee(a, b,  c,  d,
        e,   f, g,   h, i,
        j,  k,   l, m,   n,  o )

This makes argumentative access error-prone for wide tables. When arity exceeds a handful of columns, prefer the glob employee(*) and use the columns by name (see All Columns).

Aliasing a Table¶

employee(*) as e

The as keyword assigns an alias to a table. Once aliased, columns must be accessed through the alias – the original table name leaves scope.

select * from employee as e;

Aliases are often a convenience, but become necessary in contexts like self-joins.

Table Namespacing¶

mount!("etl.sqlite", "etl")(*)
etl.partner_sale_2025_07_31(*) as s

A dot-prefixed identifier namespaces the table. Here, partner_sale_2025_07_31 lives within the namespace etl. What this namespace represents – schema in some databases, database in others – is implementation-dependent.

select * from etl.partner_sale_2025_07_31 as s;

The namespace is the entire syntax before the dot and may include nesting using ::. Namespaces are nested like file-system folders.

mount!("etl.sqlite", "client1::production::etl")(*)
client1::production::etl.partner_sale_2025_07_31(*) as s

In the above example, client1::production::etl is the namespace where client1 contains production which contains etl.

Namespaces are elements of the delightql runtime. The delightql programmer chooses the hierarchy and maps these to source structures. For more information, see the namespacing section of DDL.

Anonymous Table¶

_(1,2,3)

The underscore functor _( ) declares a table inline, with literal values. Commas separate columns. This is equivalent to:

select 1,2,3;

select * from (values (1,2,3));

The underscore is the FULL sigil – a name for “no name.” The name FULL plays against NULL: where NULL matches nothing and has null potency, FULL matches everything and is full of potential.

Note the two anonymous tables in play: the inline table declared with _( ) and the result table returned by the query itself. The term anonymous table in this reference denotes the former – tables whose values are defined inline and whose names are discarded. An anonymous table is a query-mode construct, despite its syntactic resemblance to assertion-mode fact instantiation.

Stacked Notation and Multiple Rows¶

Multiple rows are expressed with the SEMI-OR sigil ;, a disjunction operator:

_(1,2;10,20)

select 1,2
  UNION ALL
select 10,20;

Semicolon binds looser than comma, so rows stack naturally without parentheses. This stacked notation appears throughout delightql – anywhere multiple clauses or rows would otherwise require repeating functor syntax. Stacked notation is essentially syntactic sugar: it reduces redundant functor notation in both assertion mode and anonymous tables. See the glossary for examples.

Naming the Columns of Anonymous Tables¶

The columns in the examples so far have been positional – they have ordinal positions but no names. Delightql allows positional-only access, but also provides syntax for naming columns.

  _( first, second @ 1,2;10,20;100,200)

select 1 as first ,2 as second
  UNION ALL
select 10,20
  UNION ALL
select 100,200;

The ATOP sigil @ separates column names (comma-delimited) from the stacked data that follows. This three-row, two-column table now has columns named first and second.

ATOP has an alternate form: three or more dashes. The following are all equivalent:

• @
• ---
• --------
• ----------------------

The dashed form enables formatted table literals:

_(
 id,first_name   , last_name , email
 ----------------------------------------------------
 0,"Gusti"       , "Parlor"  , "[email protected]"  ;
 1,"Diane-marie" , "McHenry" , "[email protected]"  ;
 2,"Ced"         , "Mainds"  , "[email protected]" ;
 3,"Bren"        , "Berndsen", "[email protected]"
)

Sparse Anonymous Tables¶

When most columns in a wide anonymous table are NULL, data rows become verbose and error-prone. Sparse columns solve this: mark optional columns with ? in the header, then fill only the ones you need per row.

_(column, type, nullable?, default?, check?, primary_key?, unique?
  ---------------------------------------------------------------
  "id",     "INT",    _(primary_key @ "true") ;
  "name",   "TEXT" ;
  "email",  "TEXT",   _(unique @ "true") ;
  "salary", "DECIMAL",_(check @ "salary>0"))

Columns without ? are positional – every row must supply them, in order. Columns with ? are sparse – unfilled sparse columns default to NULL. If sparse columns are used, they must come after the required columns.

Sparse fills¶

A sparse fill uses anonymous table syntax _(col @ val) to assign a value to a named sparse column. Fills appear after the positional values in a data row:

_(column, type, nullable?, primary_key?
  -------------------------------------
  // Single fill
  "id", "INT", _(primary_key @ "true") ;

  // Multiple separate fills
  "id", "INT", _(primary_key @ "true"), _(nullable @ "false") ;

  // Combined fill: multiple sparse columns in one expression
  "id", "INT", _(primary_key, nullable @ "true", "false"))

In a combined fill, column names and values are matched positionally: primary_key gets "true", nullable gets "false".

No fills¶

When a row supplies no fills, all sparse columns are NULL:

_(a, b?, c?
  --------
  1 ;
  2 ;
  3)

This is equivalent to _(a, b, c @ 1, null, null; 2, null, null; 3, null, null).

All sparse¶

A table may have no positional columns at all:

_(x?, y?
  ------
  _(x @ 1) ;
  _(y @ 2) ;
  _(x, y @ 3, 4))

Higher-Order Predicates¶

Delightql supports higher-order predicates–predicates that accept tables (or scalars) as parameters and return a table. SQL calls these table-valued functions. Defining higher-order rules is covered in the DDL section. Given this definition:

clean_contacts(T(*))( * ) :-
  T(*)
    |> $(trim:())( last_name, first_name)
    |> $(lower:())( email)
    |> -( fax)

a query passes it a table:

clean_contacts(employee(*))(*)

Here, clean_contacts is a higher-order predicate that takes employee(*) as its parameter.

The transpiled SQL depends on how the predicate was defined. With the definition given, the query clean_contacts(employee(*))(*) produces:

select
    employee_id,
    trim(last_name)    as last_name,
    trim(first_name)   as first_name,
    title,
    reports_to,
    birth_date,
    hire_date,
    address,
    city,
    state,
    country,
    postal_code,
    phone,
    lower(email)      as email
from employee;

Higher-order predicates are structurally typed by the columns they reference. This resembles duck typing: delightql has no formal type layer, but detects which columns the predicate body requires. Any table providing those columns is a valid argument. In this example, any table with last_name, first_name, email, and fax qualifies:

clean_contacts(customer(*))(*)

The pipeline form, explained in Higher-Order Pipes, is equivalent:

customer(*)
    |> clean_contacts(*)

Order By¶

Delightql, like SQL, has order by. The ORDER-BY operator is an octothorpe-prefixed functor #() applied after a pipe:

employee(*)
    |>  #(first_name,last_name)

select
  *
from employee order by first_name, last_name;

Columns appear in the SQL in the order given. Collation modifiers work as in SQL–with keywords:

employee(*)
    |>  #(hire_date descending,last_name ascending)

select
  *
from employee order by hire_date desc, last_name asc;

Order By has no meaning in pure relational algebra, where relations are unordered sets. SQL has never been true to theory; delightql is equally cavalier. To admit order-by as a first-class relational operator, delightql takes two positions:

• Relations are ordered sequences of tuples, not sets. A sequence captures this notion.
• A parametric mechanism maps domain orderings onto tuple orderings. We can imagine every tuple contains a hidden column #. Absent an explicit ordering, this column holds arbitrary values. Given order by my_column, the tuples reorder and # recalculates accordingly.

Limit¶

Limit the number of tuples returned using the TUPLE-ORDINAL sigil # in a predicate position:

customer(*) , # < 20

select * from customer limit 20;

Read this as: “all columns of customer where the implicit row ordinal is less than 20.”

Limit affects only cardinality, not schema.

Order of operations matters. Delightql evaluates left to right, so these two queries differ:

customer(*), invoice(*.(customer_id)), #<20

select
  *
from customer join invoice using(customer_id)
  limit 20;

customer(*), #<20, invoice(*.(customer_id))

select
  *
from (select * from customer limit 20)
  join invoice using(customer_id);

Projection¶

Projection retains a subset of columns from a relation. In SQL, this is the list between SELECT and FROM.

Project Retain¶

employee(*)
  |>  (first_name , last_name)

select
  first_name,
  last_name
from employee;

The R-PIPE |> passes a relation to the PROJECT operator ( ). Columns listed inside are retained; all others are discarded.

The pipe creates a scope barrier for the relation being projected: only its projected columns continue forward. Inside an interior relation, bindings from the enclosing scope remain accessible; they are not part of the local interface this projection replaces.

Projections can be chained:

employee(*)
  |>  (first_name , last_name)
  |>  (first_name )

select first_name from employee;
-- -- optimized from:
-- select first_name
--   from (
--     select
--       first_name,
--       last_name
--     from Employee);

Delightql (and SQL optimizers) will simplify redundant intermediate projections. But scope is enforced at each step–this will not work:

// Error: first_name not in scope
employee(*)
  |>  (last_name)
  |>  (first_name)

After line 2, only last_name exists in the piped relation.

Rename¶

Rename a column during projection with as:

employee(*)
  |>  (first_name as f, last_name)

select
  first_name as f,
  last_name
from employee;

Generalized Projection¶

Columns can be transformed during projection using domain functions:

track(*)
    |>  ( upper:(name) as n,
          upper:(composer) as composer,
          milliseconds / 1000 as seconds)

select
  upper(name) as n,
  upper(composer) as composer,
  milliseconds / 1000 as seconds
from track;

Note the colon in upper:(name). This distinguishes functions from relations – foo(A,B) is a relation; foo:(A) is a function.

Aggregate functions are not permitted in projection. See the sections on distinct and group by for aggregate usage. Delightql will reject known aggregates, but cannot detect user-defined aggregates–these will transpile as if scalar.

Other functions – ‘case’, ‘case select’, concatenation, windowing/analytic functions, and operators – are covered in the function chapter of this reference.

Argumentative Projection¶

Columns can be projected by position using argumentative access. The FULL sigil _ discards unwanted positions:

employee(employee_id , last_name , _ , _ ,
        _ , _ , _ , _ , _ ,
        _ , _ , _ , _ , _ , _ )

select
  employee_id,
  last_name
from employee;

Only the first two columns are retained; the rest are projected away. The identifiers employee_id and last_name name the columns in the result – matching the underlying column names here, though positional access can also rename by using a different identifier.

As with argumentative positional access, this notation is brittle for wide tables – prefer named projection when arity exceeds a handful of columns (see Argumentative Positional Access).

Project Out¶

The PROJECT-OUT operator -(◌) subtracts columns from a relation:

employee(*)
  |> -(birth_date, email)

select
    employee_id,
    last_name,
    first_name,
    title,
    reports_to,
    --  birth_date, -- column projected out
    hire_date,
    address,
    city,
    state,
    country,
    postal_code,
    phone,
    fax
    -- Email -- column projected out
from employee;

All columns except birth_date and email are retained. This is particularly useful for wide tables where listing retained columns would be tedious.

Map Covering¶

The MAP-COVER operator $( · )( · ) applies a function across specified columns while preserving all others:

employee(*)
  |> $(upper:())( last_name, first_name, title, city)

select
    employee_id,
    upper(last_name)  as  last_name,
    upper(first_name) as  first_name,
    upper(title)     as  title,
    reports_to,
    birth_date,
    hire_date,
    address,
    upper(city)      as  city,
    state,
    country,
    postal_code,
    phone,
    fax,
    email
from employee;

The first parentheses contain the function; the second lists the target columns. The function upper:() is written with its argument row open – the column value lands in the row’s final argument, the same landing a function pipe takes. For a unary function the final argument is the only one; for max:(0) the column value lands after the written 0, as max(0, value). Write @ where the value must land elsewhere.

Map covering:

1. Applies the function to each listed column
2. Renames results to their original column names
3. Passes through unlisted columns unchanged
4. Preserves column ordinality

Because unlisted columns pass through, transformations can be chained:

employee(*)
  |> $(upper:())( last_name, first_name, title, city)
  |> $(date:())( birth_date, hire_date)

Composing functions. When multiple functions apply to the same columns, three options exist:

Chained covers (repetitive but clear):

employee(*)
  |>  $(upper:())(first_name,last_name)
  |>  $(trim:())(first_name,last_name)

Containment composition using F-PARAM @ as a placeholder:

employee(*)
  |>  $(trim:(upper:(@)) )(first_name,last_name)

Both produce:

select
    employee_id,
    trim(upper(last_name))  as  last_name,
    trim(upper(first_name)) as  first_name,
    title,
    reports_to,
    birth_date,
    hire_date,
    address,
    city,
    state,
    country,
    postal_code,
    phone,
    fax,
    email
from employee;

Basic Dimension Covering¶

The BASIC-COVER operator $$( ) transforms individual columns without the curried function syntax:

employee(*)
  |> $$( "--------" as phone, upper:(state) as state)

select
    employee_id,
    last_name,
    first_name,
    title,
    reports_to,
    birth_date,
    hire_date,
    address,
    city,
    upper(state) as state,
    country,
    postal_code,
    '--------' as Phone,
    fax,
    email
from employee;

Each transformed column requires an as modifier – this identifies which columns are being replaced. Unlisted columns pass through in their original ordinality. Referencing a nonexistent column is an error.

Conditional Covering (If-Only)¶

The IF-ONLY sigil | constrains which rows a cover applies to. Rows not matching the predicate pass through unchanged.

Map-cover with if-only:

employee(*)
  |> $(upper:())(last_name, first_name | title = "IT Staff")

SELECT
  employee_id,
  CASE
    WHEN title = 'IT Staff' THEN upper(
      last_name
    )
    ELSE last_name
  END AS last_name,
  CASE
    WHEN title = 'IT Staff' THEN upper(
      first_name
    )
    ELSE first_name
  END AS first_name,
  title,
  reports_to,
  birth_date,
  hire_date,
  address,
  city,
  state,
  country,
  postal_code,
  phone,
  fax,
  email
FROM employee;

The predicate follows the column list, separated by |. This mirrors the aggregate if-only count:(col | pred) – the | always sits between the operands and the condition.

Without if-only, the function applies to all rows. With if-only, the function applies only to matching rows; non-matching rows retain their original values.

Basic-cover with if-only:

employee(*)
  |> $$("REDACTED" as phone, "---" as fax | title = "IT Staff")

select
    employee_id,
    last_name,
    first_name,
    title,
    reports_to,
    birth_date,
    hire_date,
    address,
    city,
    state,
    country,
    postal_code,
    case when title = 'IT Staff'
         then 'REDACTED' else phone end as phone,
    case when title = 'IT Staff'
         then '---' else Fax end        as Fax,
    email
from employee;

The predicate goes at the end of the item list, after the last as target.

Composability. If-only composes with callable nesting and chaining:

customer(*)
  |> $(trim:(upper:(@)))(first_name, last_name | country = "USA")

If-only is syntactic sugar over CASE expressions. The equivalent without if-only:

employee(*)
  |> $$( _:(title = "IT Staff" -> upper:(last_name);  _ -> last_name)  as last_name,
         _:(title = "IT Staff" -> upper:(first_name); _ -> first_name) as first_name)

Regular Expression Column Addressing¶

A regular expression can select columns by name pattern:

employee(*)
  |> ( /date/ )

select
    birth_date,   -- matches /date/
    hire_date     -- matches /date/
from employee;

The pattern matches anywhere in a column’s name, and it ignores case, as a column reference does: (Birth_Date) and /BIRTH/ both find birth_date. The regex applies only to column names, not namespaces or indexes.

To match case exactly, add the flag c after the closing slash:

employee(*)
  |> ( /date/c )

Every column in the example database is lowercase, so /date/c still finds birth_date and hire_date, while /Date/c finds nothing and refuses.

c is the only flag. /date/i refuses, because ignoring case is already the default. Ignoring case applies inside character classes too, so /^[A-Z]/ matches birth_date, while /^[A-Z]/c matches nothing here.

The pattern language is a small subset of POSIX basic regular expressions: ^ and $ anchor, . matches any character, * repeats, [...] is a character class, and \+ and \? repeat one-or-more and zero-or-one. There are no groups and no alternation.

Positions. A regex stands wherever a list of columns is enumerated: PROJECT-IN ( ), PROJECT-OUT -( ), the second parentheses of MAP-COVER, the source of a RENAME-COVER *(/date/ as :"d_{@}"), GROUP-MODULO keys, function arguments, and record members. A project-in that matches no column refuses; a project-out may match none.

It cannot:

• Be followed by as; rename the matched columns with a name template
• Stand as a single value: an operand, a function-pipe step, or a slot
• Add columns in EMBED +( ), since it names only columns that already exist

Rename Cover¶

The RENAME-COVER operator *( ) renames specified columns while passing all others through:

track(*)
  |> *( name as track_name)

select
    track_id,
    name as track_name,
    album_id,
    media_type_id,
    genre_id,
    composer,
    milliseconds,
    bytes,
    unit_price
from track;

Rename cover preserves column count and column ordinality – only the names change.

Embedding¶

The EMBED operator +( ) adds a new column to a relation, placed after existing columns:

employee(*)
  |> +(  strftime:("%Y",birth_date) - 2 as two_years_before_birth )

select
    *,
    strftime('%Y',birth_date) - 2 as two_years_before_birth
from employee;

This is equivalent to |> (*, expr as name). The embed syntax makes the intent explicit: the relation is unchanged except for the added column.

Map Embedding¶

The EMBED-MAP operator +$( )( ) applies a function across columns and creates new columns from the results (rather than replacing the originals):

tidy:(s) :- trim:(coalesce:(s, ""))
label:(s) :- upper:(tidy:(s))

invoice(*)
  |> +$(tidy:() as :"tidy_{@}")( /^billing_/ )
  |> +$(label:() as :"label_{@}")( /^billing_/ )

SELECT
  invoice_id,
  customer_id,
  invoice_date,
  billing_address,
  billing_city,
  billing_state,
  billing_country,
  billing_postal_code,
  total,
  trim(coalesce(billing_address, '')) AS tidy_billing_address,
  trim(coalesce(billing_city, '')) AS tidy_billing_city,
  trim(coalesce(billing_state, '')) AS tidy_billing_state,
  trim(coalesce(billing_country, '')) AS tidy_billing_country,
  trim(coalesce(billing_postal_code, '')) AS tidy_billing_postal_code,
  upper(trim(coalesce(billing_address, ''))) AS label_billing_address,
  upper(trim(coalesce(billing_city, ''))) AS label_billing_city,
  upper(trim(coalesce(billing_state, ''))) AS label_billing_state,
  upper(trim(coalesce(billing_country, ''))) AS label_billing_country,
  upper(trim(coalesce(billing_postal_code, ''))) AS label_billing_postal_code
FROM invoice;

The first parentheses contain the function and an as qualifier with an F-STRING. The F-PARAM sigil @ stands in for the column name, generating tidy_billing_address, tidy_billing_city, etc. The second parentheses specify the target columns–here, all columns matching /^billing_/.

Unlike MAP-COVER, which replaces columns in place, EMBED-MAP preserves the originals and appends the transformed columns.

Projection Summary¶

The operators introduced so far are all unary – they transform a single relation. The following qualities characterize their behavior:

• Column Cardinality Preserved: The number of columns remains unchanged.
• Column Ordinality Preserved: Columns retain their relative order, with no intercalation of new columns among existing ones.
• Column Names Preserved: Existing columns keep their names (new columns do not affect this).
• Table Cardinality Preserved: The number of rows remains unchanged.
• Table Ordinality Preserved: Rows retain their order.

A quality is marked Y only if it holds under all circumstances; N if any case violates it.

Preservation properties of Project, ProjectOut, MapCover, and BasicMapCover

Preservation properties of Rename, OrderBy, and GroupModulo

Preservation properties of Embed and MapEmbed

One operator stands apart: GROUP-MODULO %( ) preserves none of these qualities unconditionally. It captures both DISTINCT and GROUP BY, and is the subject of the next chapter.

Distinct and Group By¶

Distinct and group by are congruent operations. Their similarity is often a source of confusion:

select
  title
from employee
  group by title;

-- Produces the same result as:

select
  distinct title
from employee;

Delightql reflects this congruence with unified syntax.

Distinct¶

The GROUP-MODULO operator %( ) returns distinct combinations of the specified columns:

customer(*)
  |> %(country)

select
  distinct country
from customer;

Multiple columns return distinct combinations:

customer(*)
  |> %(country, state)
  |> #(country,state descending)

select
  distinct country, state
from customer
    order by country asc, state desc;

To deduplicate all columns – converting a multiset (bag) into a set:

genre_2025(*)
  |> %(*)  //returns unique rows and removes duplicates

Group By¶

Group by extends distinct with aggregation. The AGG-AND sigil ~> separates grouping columns (left) from reduced columns (right):

invoice(*)
  |> %(billing_country ~>  count:(*) , sum:(total) )

select
  billing_country,  -- grouping column
  count(*),        -- reduced column
  sum(total)       -- reduced column
from invoice
  group by billing_country;

Grouping columns may be expressions:

invoice(*)
    |> %( total > 10  as high_low,
          upper:(billing_country) ~>
            count:(*) ,
            avg:(total) )

select
  total > 10 as high_low, -- grouping column
  upper(billing_country),  -- grouping column
  count(*),    -- reduced column
  avg(total)   -- reduced column
from invoice
  group by upper(billing_country), (total > 10) ;

Group Delegates¶

A group delegate is a principled mechanism for choosing one value among many during group reduction. The group delegate generalizes the aggregate functions max() and min() by allowing the user to choose their own ordering criteria. The group delegate is a function in unfamiliar clothing.

The group delegate may be found in reduction place. A column parenthesisized and then postfixed by the F-OVER sigil <~ followed by an explicit ordering criteria.

track(*)
  |> %(genre_id ~> count:(*) as n, (name) <~ #(milliseconds desc))

With an empty ordering (bare <~) the delegate row is arbitrary:

track(*)
  |> %(genre_id
        ~> count:(*) as n,
        (name as longest) <~ #(milliseconds desc),
        (name as arbitrary)<~ )

Some Sql vendors implement this functionality directly with dedicated syntax – the DISTINCT ON syntax.

Whole-Group Aggregate Functions¶

To aggregate without grouping, omit the grouping columns:

invoice(*)
  |>  %( ~>  count:(*) , sum:(total) )

  select
    count(*),
    sum(total)
  from invoice;

The GROUP-PIPE ~> provides a shorter form for a single aggregate:

invoice(*) ~>  count:(*)

Note. The GROUP-PIPE is different from the AGG-AND. This form replaces the |> pipe. It is pure sugar.

Internal Distinct¶

Some aggregates accept a distinct modifier on their input. The INNER-MODULO sigil % prefixes the column:

customer(*)
    |>  %( country ~>
            count:(%city) ,
            count:(%support_rep_id))

select
  country,
  count(distinct city),
  count(distinct support_rep_id)
from customer
  group by country;

Filter Clauses¶

The IF-ONLY sigil | constrains which values enter an aggregate:

customer(*)
  |>  %( country ~>
         count:(%city) ,
         count:(%support_rep_id),
         count:(last_name | length:(last_name) > 7)
            as long_lastname_count)

For dialects supporting FILTER:

select
  country,
  count(distinct city),
  count(distinct support_rep_id),
  count(last_name)
    filter
      (where length(last_name) > 7) as long_lastname_count
from customer
  group by country;

For dialects without FILTER, delightql emits a CASE expression:

select
  country,
  count(distinct city),
  count(distinct support_rep_id),
  count(case when length(last_name) > 7
            then last_name else null end) as long_lastname_count
from customer
  group by country;

Having¶

Filter on reduced columns by placing a predicate after the group by:

customer(*)
  |> %( country ~> count:(*) as customer_count)
      ,  customer_count > 4

Read this as: “group customers by country, count each group, then keep only groups with more than 4 rows.”

select
  country,
  count(*) as customer_count
from customer
  group by country
    having count(*) > 4;

Why does SQL have both WHERE and HAVING?

SQL has an implicit order of operations. WHERE filters rows before grouping; HAVING filters groups after aggregation. The two keywords signal this distinction. For a historical reflection on this issue, see .

The abstraction is leaky – most programmers soon recognize that HAVING is equivalent to wrapping in a subquery and filtering with WHERE:

SELECT country, count(*) AS customer_count
FROM customer
GROUP BY country
HAVING count(*) > 4;


-- equivalent to:

SELECT * FROM (
SELECT country, count(*) AS customer_count
FROM customer
GROUP BY country
) WHERE customer_count > 4;

Because delightql has explicit order of operations, no separate syntax is needed. The predicate simply follows the group by:

customer(*)
|> %(country ~> count:(*) as customer_count),
customer_count > 4

Placing the filter earlier would be an error, customer_count does not exist until after the aggregation.

Where¶

Selection (σ in Codd’s relational algebra, WHERE in SQL) filters rows without changing schema. SQL’s use of “select” for projection is unfortunate – Codd used “select” for row filtering. Delightql follows Codd’s terminology.

Delightql uses the comma (conjunction) to attach predicates to relations. This is the same syntax as joins and comes directly from Prolog.

Domain Predicates¶

invoice(*), total > 10

select * from invoice where total > 10;

Multiple predicates conjoin naturally:

  invoice(*), total > 10,
    trim:(lower:(billing_country))="usa"

select * from invoice
  where total > 10
    and trim(lower(billing_country))
      IS NOT DISTINCT FROM 'usa';

Scope restricts commutativity. Predicates can only reference columns already in scope. This is invalid:

// WONT WORK because total is not yet in scope
  customer(*),
    total > 10,
    trim:(lower:(billing_country))="usa",
    invoice(*)

But once columns are in scope, predicates may be reordered:

invoice(*),
  total > 10,
  trim:(lower:(billing_country))="usa"

// commutativity allowed when all LVars are in scope

invoice(*),
  trim:(lower:(billing_country))="usa",
  total > 10

Null-safe vs Null-dangerous equality. Delightql reserves the = sigil for the SQL comparison operator IS NOT DISTINCT FROM. To use the traditional (dangerous) equality in SQL, use +sql_eq(x,y).

invoice(*), total > 10,
    trim:(lower:(billing_country))="usa",
    +sql_eq(billing_state,"CA")

select * from invoice
  where total > 10
    and trim(lower(billing_country))
      IS NOT DISTINCT FROM 'usa'
    and billing_state='CA';

Three-Valued Logic

Null has been with databases since the very beginning and so has the debate about its semantics and danger.

SQL provides ‘good enough’ semantics for its usage in the set operations of distinct, grouping, union and intersect, but it can be a foot-gun in other circumstances.

The simplest display of its behavior below:

select
    null=null,
    null is null,
    null is not null,
    1=null,
    1 is null,
    1 is not null,
    1 in (select null union all select 2),
    1 not in (select null union select 2),
    1 in (select null union all select 1),
    1 not in (select null union select 1)
;

shows many odd results

null=null                              =  null
null is null                           =  1
null is not null                       =  0
1=null                                 =  null
1 is null                              =  0
1 is not null                          =  1
1 in (select null union all select 2)  =  null
1 not in (select null union select 2)  =  null
1 in (select null union all select 1)  =  1
1 not in (select null union select 1)  =  0

Infix domain predicates

If a programmer requires the use of the traditional Sql = they can use the named functor: +sql_eq(left,right). Likewise, for SQL’s != there is +sql_ne(left,right)

The row-correspondence exception.

The table above describes local value tests – conditions comparing values within one row or against a ground term. An ordinary comparison matching two relation row occurrences uses SQL =, whether the matches feed a multiplying join, an EXISTS/NOT EXISTS, or a correlated scalar subquery.

IS NOT DISTINCT FROM in correspondence would treat NULL as a matchable value and can explode cardinality. Suppressing the extra rows with EXISTS does not make that correspondence correct.

Joins establish structural correspondence – “these rows belong together.” NULL means absence, and absence does not make a correspondence. Filters test value equality, where null-safety matters because rows should not silently disappear.

SQL clause placement is not the equality rule: a correlated EXISTS often has an inner WHERE, and still matches two row occurrences. A local inner.x = null beside its correlation stays null-safe.

There is no cardinality/nulljoin gate. If an absent key has a real matchable meaning, first map both sides to an explicit non-NULL key and match those keys. The NULL itself never establishes correspondence.

Argumentative Grounding¶

When using argumentative functor notation, a ground term in argument position induces selection:

album(album_id,title,22)

select
  album_id,
  title
from album where artist_id IS NOT DISTINCT FROM 22;

All argumentative grounding uses null-safe equality.

The grounded column (artist_id IS NOT DISTINCT FROM 22) filters rows and is excluded from projection. Multiple grounds compound:

album(album_id,"Coda",22)

SELECT album_id
FROM album
WHERE
  title IS NOT DISTINCT FROM 'Coda'
  AND artist_id IS NOT DISTINCT FROM 22;

Any domain expression that reduces to a ground term may also be used in argumentative position:

album(album_id,upper:("iv"),(20 + 2))

select
  album_id
from album where title IS NOT DISTINCT FROM upper('iv') and artist_id IS NOT DISTINCT FROM (20+2);

The Prolog heritage is evident in this syntax and extends to joins – covered in a later section.

Semi-Joins and Anti-Joins¶

Semi-joins (∃ or ⋉) and anti-joins (∄ or ▷) test for existence without contributing columns. They ask “can you prove this?” rather than “give me this data.”

The PROVE sigil + prefixes a semi-join:

employee(*) as e, +customer(, e.employee_id=support_rep_id)

SELECT *
FROM employee AS e
WHERE
  EXISTS (
    SELECT 1
    FROM customer
    WHERE
      support_rep_id = e.employee_id
  );

The DISPROVE sigil \+ prefixes an anti-join: This syntax comes directly from Prolog’s negation-as-failure.

employee(*) as e, \+ customer(, e.employee_id=customer.support_rep_id)

select
  *
from employee e
  where not exists (select 1 from customer
                      where support_rep_id = e.employee_id);

The join condition(s) appears inside the parentheses – this is called interior notation. The relation is tested for provability, not joined for data. The correlated row match still has join equality: two NULL keys do not correspond. + and \+ change how matching rows are observed, not how they match. A comparison confined to one interior row, such as customer.support_rep_id = null, remains a local null-safe test.

The in Predicate¶

customer(*), +_(state@"MA";"TX";"CA";"ON")

Syntactic sugar provides the familiar form:

customer(*), state in ("MA";"TX";"CA";"ON")

Both ask whether the customer’s state corresponds to one candidate. With these non-NULL constants, the filtering SQL can be written:

select
  *
from customer where state in ('MA','TX','CA','ON');

The unsugared form generalizes to multi-column comparisons:

customer(*), +_( country, state @
                 "USA","CA";
                 "USA","TX";
                 "Canada","ON")

SELECT *
FROM customer
WHERE
  ('USA' = country
  AND 'CA' = state)
  OR ('USA' = country
  AND 'TX' = state)
  OR ('Canada' = country
  AND 'ON' = state);

+_ is an existential observer of the same anonymous table an unmarked comma member would join. When a column from one row is matched to a candidate row, NULL never establishes correspondence, even under +. For example, _(a @ null), +_(a @ null) has no answer. A wholly ground null in (null) is a local value test instead and remains true.

Relational in¶

The literal form tests candidates in a fixed list. The relational form tests candidate rows from a query. Both are existence observations over the ordinary row-correspondence match, not a promise to emit SQL’s three-valued IN (SELECT ...).

The right-hand side is any DQL relation (a table access, a pipe chain, or an anonymous table):

customer(*), support_rep_id in employee(|> (employee_id))

SELECT * FROM customer
  WHERE EXISTS (SELECT 1 FROM employee AS e
                WHERE e.employee_id = customer.support_rep_id);

When the relation already has exactly one column, projection is unnecessary:

customer(*) |> %(support_rep_id) : support_rep
employee(*), employee_id in support_rep(*)

WITH support_rep AS (SELECT DISTINCT support_rep_id FROM customer)
SELECT * FROM employee
  WHERE EXISTS (SELECT 1 FROM support_rep AS r
                WHERE r.support_rep_id = employee.employee_id);

Tuple relational in¶

Multi-column matching extends the tuple in syntax ((x,y) in (1,2;3,4)) to relations. The relation must produce exactly as many columns as the left-hand tuple:

customer(*), (city, country) in employee(|> (city, country))

SELECT * FROM customer
  WHERE EXISTS (SELECT 1 FROM employee AS e
                WHERE e.city = customer.city
                  AND e.country = customer.country);

Negation: not in¶

employee(*), employee_id not in employee(|> (reports_to))

SELECT * FROM employee
  WHERE NOT EXISTS (SELECT 1 FROM employee AS m
                    WHERE m.reports_to = employee.employee_id);

The negative form is anti-existence, not SQL NOT IN: a NULL candidate does not poison a nonmatching probe. A NULL employee key corresponds to no candidate key, including another NULL; positive in then fails and negative not in succeeds for that row. A wholly ground test such as null in (null) is a local null-safe value comparison and succeeds.

Arity rule

The relation must produce exactly as many columns as the left side has elements – one for a scalar, N for an N-tuple. A mismatch is a compile-time error.

Relation to semi-joins

Relational in is syntactic sugar over the semi-join notation introduced above. col in R(|> (c)) desugars to +R(, col = c); col not in R(|> (c)) desugars to \+R(, col = c).

Inverted In¶

The anonymous semi-join syntax permits an inversion – ground the header, vary the rows:

customer(*),
    +_("Dublin" @
      city;
      state;
      country)

select
  *
from customer
  where city = 'Dublin'
    or state = 'Dublin'
    or country = 'Dublin';

This asks: “does ‘Dublin’ appear in any of these columns?” The columns become the rows of the anonymous table; the constant becomes the match target. Because 'Dublin' is non-NULL, SQL = is a result-equivalent filter here. Grounding a candidate’s header to NULL is instead a local null-safe test.

SQL supports Inverted In

Though it might be a revelation to some – including the author! – the inverted in is standard SQL and is fully supported by all dialects:

select
  *
from customer
  where 'Dublin' in
    (city,state,country);

Similarly, to test if one column equals any of several others:

customer(*), +_(city @ state; country)

select
  *
from customer
  where city = state
    or city = country;

Here city is supplied by the customer row and each candidate is an anonymous-table row. + keeps or drops the customer depending on whether such a row corresponds; it does not turn NULL-to-NULL into a match. A wholly ground anonymous test still follows the local ground-value law.

Sigma Predicates¶

Predicates can be defined and reused. A sigma predicate is a rule that expands into selection criteria: Defining sigma predicates is covered in DDL.

 no_data("NA";"N/A";"UNKNOWN")

 empty(column) :- null=column
 empty(column) :- trim:(column)=""
 empty(column) :- +no_data(upper:(column))

Use with semi-join or anti-join syntax:Delightql applies De Morgan’s laws, distributing negation across disjunctive clauses.

customer(*),
  \+empty(company),
  \+empty(state)

WITH no_data(value) AS (VALUES ('NA'), ('N/A'), ('UNKNOWN'))
SELECT *
FROM customer
WHERE
  (company IS NULL
    OR trim(company) IS NOT DISTINCT FROM ''
    OR EXISTS (SELECT 1 FROM no_data WHERE upper(company) = no_data.value)
  ) IS NOT TRUE
  AND (state IS NULL
    OR trim(state) IS NOT DISTINCT FROM ''
    OR EXISTS (SELECT 1 FROM no_data WHERE upper(state) = no_data.value)
  ) IS NOT TRUE;

Like and Between¶

SQL’s LIKE and BETWEEN have special syntax. Delightql maps functor notation to these constructs:

track(*), +like(name,"%Love%"), \+between(milliseconds,120000,600000)

The above delightql transpiles to the following Sql.

select
  *
from track
  where
    (name like '%Love%') is true and
    (milliseconds between 120000 and 600000) is not true;

To match a value against several patterns at once, like_any takes the value, then &, then the patterns as rows. Under + it keeps the rows whose value matches at least one pattern; under \+, the rows whose value matches none:

customer(*), \+like_any(email & "%.com"; "%.org")(*)

It is equivalent to:

select *
from customer
where not exists (
  select 1
  from (select '%.com' as pattern union all select '%.org') as p
  where email like p.pattern
);

The patterns can be any one-column relation — +like_any(email, domains(*))(*). Without the + it joins instead of filtering: each row gains the pattern it matched, once per match. A NULL value matches no pattern.

Disjunction¶

Two syntaxes express OR:

Keyword form (recommended). The or keyword binds predicates within a sigma clause:

invoice(*)
  , trim:(lower:(billing_country)) = "usa"
        or total > 10
  , billing_state != "CA"
  |> %( billing_country
          ~>
        count:(*) as invoice_count,
        avg:(total) )

Sigil form. The SEMI-OR sigil ; requires parentheses to capture scope:

invoice(*)
  , (trim:(lower:(billing_country)) = "usa"
        ; total > 10 )
  , billing_state != "CA"
  |> %( billing_country
          ~>
        count:(*) as invoice_count,
        avg:(total) )

Prefer the keyword form – it reads more clearly and avoids parenthesis errors. See “Precedence and Scoping” for details.

Join¶

Joins extend a relation’s schema by combining columns from multiple sources. Every join is a filtered cross product – the join condition determines which pairings survive.

Delightql evaluates joins left to right. Each table must be in scope before its columns can be referenced.

Cross Join¶

media_type(*), genre(*)

SELECT * FROM media_type CROSS JOIN genre;

Two relations joined with no condition produce a Cartesian product. The resulting cardinality is the product of both input cardinalities. This is rarely intended.

Inner Join¶

album(*), artist(*), album.artist_id = artist.artist_id

SELECT * FROM album
  JOIN artist ON album.artist_id = artist.artist_id;

The join condition follows the tables it correlates. Multiple conditions conjoin naturally:

customer(*), employee(*),
  customer.support_rep_id = employee.employee_id,
  customer.country = employee.country

Scope is left to right. This is an error:

// INVALID: artist not yet in scope
album(*), artist.artist_id = album.artist_id, artist(*)

USING Shorthand¶

The .(cols) operator specifies USING columns:

album(*), artist(*.(artist_id))

SELECT * FROM album JOIN artist USING (artist_id);

Multiple columns are comma-separated:

invoice_line(*), track(*.(track_id, unit_price))

SELECT * FROM invoice_line JOIN track USING (track_id, unit_price);

USING and explicit ON differ operationally: USING retains one copy of the matched column; ON retains both.

USING may combine with explicit conditions:

track(*), album(*.(album_id)), track.name = album.title

ON vs WHERE Inference¶

For inner joins, the placement of conditions in ON versus WHERE is semantically equivalent. For outer joins, it differs: ON conditions govern the match while preserving nulls; WHERE conditions filter the result and eliminate nulls.

Delightql infers placement from column references:

• Condition references multiple tables → ON
• Condition references one table → WHERE

artist(*), album?(*),
  artist.artist_id = album.artist_id,   // two tables → ON
  album.title = "Greatest Hits"       // one table → WHERE

SELECT * FROM artist
  LEFT OUTER JOIN album
    ON artist.artist_id = album.artist_id
WHERE album.title = 'Greatest Hits';