DelightQL Language Reference

Introduction

Delightql is a logic-inspired query language that transpiles to SQL. This document provides a reference to the language via an enumeration of all of its features.

Each feature comes with at least one example and a short description to motivate usage. While structured like an introduction by beginning with foundations and growing from there, this reference is not a beginner’s text and presupposes a familiarity with SQL. A reader with such a background will find features easier to locate and to understand.

The reference is organized into sections familiar to SQL users: DQL, DDL, and DML.

• Data Querying Language (DQL): Any feature that is used to return a result via querying. All examples here may be directly run in the delightql read-eval-print loop (REPL).
• Data Definition Language (DDL): Any feature that is used to create or drop tables, views or functions. These correspond to SQL DDL statements and to Prolog assertion-mode rules.
• Data Manipulation Language (DML): Any feature that is used to modify an existing set of data, i.e. update, delete, or insert.
• Keywords, Identifiers, and Ground Terms: An index of lexicographical syntax needed to identify identifiers, constant (ground) values, and keywords.
• Precedence: The precedence rules for parsing operators and expressions, both at the level of table expressions and of domain expressions.
• Directives: An index of the special pseudo-relations and operators used to configure the language, and attach databases.
• System Tables: A listing of the system tables available to introspect code, execution, and the state of the databases.

Throughout this reference, examples of SQL illustrate how a delightql expression could be transpiled. Such SQL examples should be read as both descriptive of translation semantics and rigorous to final results. Actual SQL from these delightql examples may differ – for one, different SQL targets The term target indicates the dialect of SQL which will be the transpile target for the delightql transpiler. will likely have different implementations.

As an example of this point, consider the following SQL for an OUTER JOIN.

  SELECT *
  FROM employee FULL OUTER JOIN department
    ON employee.DepartmentId = department.DepartmentId;

Some SQL dialects do not have this native syntax, but can achieve the same result, with a lot of ceremony – example copied from:.

SELECT
  employee.LastName,
  employee.DepartmentId,
  department.DepartmentName,
  department.DepartmentId
FROM employee
JOIN department
  ON employee.DepartmentId = department.DepartmentId
UNION ALL
SELECT
  employee.LastName,
  employee.DepartmentId,
  CAST(NULL AS varchar(20)),
  CAST(NULL AS integer)
FROM employee
WHERE
  NOT EXISTS (
    SELECT *
    FROM department
    WHERE
      employee.DepartmentId = department.DepartmentId
  )
UNION ALL
SELECT
  CAST(NULL AS varchar(20)),
  CAST(NULL AS integer),
  department.DepartmentName,
  department.DepartmentId
FROM department
WHERE
  NOT EXISTS (
    SELECT *
    FROM employee
    WHERE
      employee.DepartmentId = department.DepartmentId
  );

Both the above queries execute a full outer join with significantly different syntax while capturing the same semanticsIt is operationally easy to define semantic equality in our discussions: if two different queries under all circumstances produce the exact same table (same schema, same cardinality), then they are semantically equivalent. In such circumstances, this reference will choose the simplest and clearest SQL.

Terminology

Delightql takes inspiration from the logic programming world as well as from SQL, so there will be many places in this reference where terminology from both may be used.

As a mini-tutorial for those unfamiliar, consider the following table of terminology equivalences:

SQL and logic programming terminology equivalences

This table is neither complete nor free of caveats. There is no noveltyin the nothing new under the sun sense to this congruence as Codd and Kowalski both showed how relational algebra query languages and Prolog respectively could be built from predicate calculus. Many disciplines starting in the 1970s continue to examine the ways in which these technologies are two sides of the same coin, but may still yet inform the other.A notable example of this cross-pollination is how the academic research from deductive databases and Datalog provided the semantics – and permission – for SQL to adopt recursive common table expressions

Some features from one area have no equivalence in the other. For instance, the SQL NULL has no logic programming analog, and features that derive from this distinction (like outer joins) likewise find no purchase.

Core Terminology

Some terms in this reference are used often enough to warrant the following few early paragraphs. These terms are

• domain
• functor
• predicate and relation and their distinction
• sigilization

Domain

The term domain is used throughout this book much in the same way it is used throughout mathematics, as the set of possible values for a given quantity. In SQL parlance, this is not to be confused with type, as the domain of heights should not be confused with the domain of Kelvin temperatures even though both could be modeled as positive real numbers.

Highlighting the term domain is done to create an emphasis on non-domain things. Delightql makes this distinction by asserting that things are either domain values or predicate values, either domain expressions or predicate expressions.

This is a somewhat artificial delineation as tables/predicates being cartesian products have their own very real input space and domain.

To be concrete:

• Domain values include 1, "roger", and true; domain expressions include 1+2 and upper("roger").
• Predicate values include employee; predicate expressions include generate_series(1,10) or (select i_am_a_subquery from inner) as newtable.

Functor Form, or just Functor

A functor form is an identifier followed by a pair of parentheses and the arguments contained within. This definition is syntactic only, and has no assigned semantics until we establish a context. With apologies to Prolog, we shorten the term functor form to just functor.

Within the context of delightql a functor is usually understood to be a table and always understood to be a predicate (as predicates generalize tables).

// Access the table foo and its three columns
// which we call a,b, and c
foo(a,b,c)

// Access the table bar and all its columns
bar(*)

In the context of delightql and most logic languages, the functor notation is self-denoting. This means several things, but practically we can say the following:

• With functor notation, traditional notions of inputs and outputs are blurred, and often meaningless (each argument may be either input or output).
• A functor cannot be assigned to a (scalar) variable because it denotes itself as a relation, and not as a scalar (domain value).

In contrast, most other programming languages use functor syntax to denote a function and/or (perhaps) a subroutine.

    foo(a,b,c)

implies

• if a subroutine: an execution/call only
• if a function: an execution/call and substitution by the domain value that it produces.

Under these languages, arguments are passed into a function (a, b, and c) and are consumed. Here, the function syntax is operationally equivalent to the value it returns, and thus can be assigned to domain variables.

That delightql, like Prolog, assigns the concept of a predicate/relation to the functor does not mean the absence of functions.

In Prolog, functions re-use the predicate functor syntax via the designation of a known argument (called a mode) to be the output. Prolog has additional nuances about functions which we will sidestep.

Delightql provides a special syntax that remains reminiscent of a relation: a functor with a colon : between the identifier and the pair of parentheses, which we will call a function functor.

    count:(*)
    length:(last_name)
    foo:(x,y,z)

The colon asks us to read foo of x and y and z or length of last_name.

Predicate vs Relation vs Table

The terms predicate, relation, and table lack any universal authority, yet each carries emphasis depending on whom you’re talking to. This section exists not for philosophy but because Prolog, SQL, and therefore delightql all take different positions at different times on whether these are separate concepts, and how strongly to emphasize the chosen mental model.

A predicate/relation/table can be viewed as:

• An arbitrary subset of a Cartesian product (the mathematical view)
• A named storage entity for data queries (the database view)
• A named evaluator of truth in logical statements (the logic view)
• A generalization of functions (the relational view delightql emphasizes)

SQL treats relation and table as interchangeable, embracing the first two definitions. It reserves predicate for built-in truth evaluators over traditional domains like numbers and strings – as in where age>20 and last_name like "%son", where two predicates compose into one.

Prolog favors predicate, with occasional references to relation and rare references to table. But it maps its term to all three definitions above. A Prolog predicate can be stored data, a logical assertion, or a computable relationship.

Delightql uses these terms interchangeably, with some deliberate emphasis. It borrows from Prolog in favoring predicate for tables (extensional data), views (intensional data derived from extensional data), and built-in domain predicates like =, <, >, and like.

Like Prolog, delightql recognizes an operational difference between predicates resolvable from ground truths (finitary – backed by actual stored tuples) and those expressing relations over infinities. To wit, there is no table enumerating all pairs where one number is less than another. Prolog restricts such non-Herbrand expressions via moding. Delightql similarly won’t let you write the equivalent of select left_hand from ">" where right_hand=3.

There is one more definition worth surfacing:

• A predicate is a function of truth – given a tuple, it returns membership (true/false)

This is just a restatement of “named evaluator of truth,” but the framing foregrounds the boolean nature of predicates. In such situations, we ask not what data they contain, but whether a given tuple belongs. Delightql emphasizes this framing for what SQL calls semi-joins (EXISTS queries) and what Prolog expresses through provability contexts like negation-as-failure (\+). In these cases, we don’t care what the predicate returns – only whether it succeeds.

Sigilization

Delightql has few keywords compared to SQL. Where SQL uses words, delightql uses symbols.

Throughout this reference, the term sigilization describes delightql’s practice of representing operators with non-alphanumeric symbols rather than keywords.

For example, delightql sigilizes the DISTINCT relational operator with a % symbol followed by parentheses:

users(*) |> %(last_name)
// SQL: SELECT DISTINCT last_name FROM users

To continue this example, delightql recognizes that GROUP BY is simply DISTINCT extended with aggregation. The same % sigil handles both – separate grouping columns from aggregate functions with ~> to get the equivalent of SQL’s GROUP BY:

users(*) |> %(last_name ~> count:(*), sum:(salary) as salary_by_last_name)
// SQL: SELECT count(*), sum(salary) as salary_by_last_name FROM users GROUP BY last_name

Formatting

All code examples will be inset within colored text boxes, annotated with the primary language. These annotations are located atop the upper right corner of the code box.

• Boxes annotated with Sql are explanatory transpilations
• Boxes annotated with delightql are delightql examples of query expressions that are available during query mode
• Boxes annotated with delightql AM are delightql examples of assertion mode constructs, for defining rules, tables, updates, etc. (DDL and DML).

The below example shows a simple query in query mode:

employee(*)

and the next shows assertion mode:

 /* Sigma Rule, column is moded to require instantiation */
 empty(column) :- {}=column
 empty(column) :- trim:(column)=""
 empty(column) :- +no_data(column)

Inline text follows the following typography convention:

• Sql keyword syntax is italicized in fixed-width font code blocks, such as update, or select or drop.

• Identifiers (regardless of language) are styled as monospace code (e.g. employee, or last_name) and are not italicized.

• Delightql sigils are bounded by double parentheses, (⸨ ⸩). This is done to prevent confusion by separating the punctuation belonging to the language from the punctuation of the reference. Examples: ,, or |>. To repeat: only the syntax within the double parentheses (⸨ ⸩) is valid delightql syntax.

• Delightql sigils are often accompanied by their sigil name in a capitalized bold font. These names provide easier search values within the reference. Examples: R-PIPE for |>, GROUP-MODULO for %(◌). Both the sigils and the sigil are found in the [DqlVoc] section of this reference.

• Certain phrases will be italicized, indicating that there is a formal definition for this phrase and found in a glossary at the end of the reference. Examples:

• stacked notation

• sigma clauses
• column ordinality
• current piped relation
• etc.

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 that will become evident within a few pages – pipelining a relation through transformations, left to right, with consistent associativity and scoping.

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(EmployeeId  , LastName ,
         FirstName   , Title    , ReportsTo,
         BirthDate   , HireDate , Address, City,
         State       , Country  , PostalCode,
         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. Prefer named access (covered later) when arity exceeds a handful of columns.

Case insensitivity

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

• employeeid
• EmployeeId
• EMPLOYEEID

Stropping

When a name collides with a keyword or contains illegal characters (spaces, for instance), delimit it with backticks: `Employee Id`.

For more information on identifiers, and keywords and constants, see Keywords, identifiers and Ground Terms.

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

hr.employee(*) as e

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

select * from hr.employee as e;

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

client1::production::hr.employee(*) as e

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

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:

// 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.

clean_employees(hr.employee(*))(*)

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

The transpiled SQL depends on how the predicate was defined. Defining higher-order rules is covered in the DDL section. Given this definition:

clean_employees(T(*))( * ) :-
  T(*) as t
    |> $(trim:())( t.LastName, t.FirstName)
    |> $(to_iso:())( t.BirthDate, t.HireDate)
    |> -( SSN)

the query clean_employees(hr.employee(*))(*) produces:

select
    EmployeeId,
    trim(LastName)    as LastName,
    trim(FirstName)   as FirstName,
    Title,
    ReportsTo,
    to_iso(BirthDate) as BirthDate,
    to_iso(HireDate)  as HireDate,
    Address,
    City,
    State,
    Country,
    PostalCode,
    Phone,
    Fax,
    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 LastName, FirstName, BirthDate, HireDate, and SSN qualifies:

clean_employees(batch.employee_2019(*))(*)

The pipeline form (covered later) is equivalent:

batch.employee_2019(*)
    |> clean_employees(*)

Order By

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

employee(*)
    |>  #(FirstName,LastName)

select
  *
from employee order by FirstName, LastName;

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

employee(*)
    |>  #(Salary descending,LastName ascending)

select
  *
from employee order by Salary desc, LastName 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:

employee(*) , # < 20

select * from employee limit 20;

Read this as: “all columns of employee 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:

employee(*), department(*.(DepartmentName)), #<20

select
  *
from employee join department using(DepartmentName)
  limit 20;

employee(*), #<20, department(*.(DepartmentName))

select
  *
from (select * from employee limit 20)
  join department using(DepartmentName);

Projection

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

Project Retain

employee(*)
  |>  (FirstName , LastName)

select
  FirstName,
  LastName
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: columns to the left are no longer in scope after the projection. Only the projected columns continue forward.

Projections can be chained:

employee(*)
  |>  (FirstName , LastName)
  |>  (FirstName )

select FirstName from employee;
-- -- optimized from:
-- select FirstName
--   from (
--     select
--       FirstName,
--       LastName
--     from employee);

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

// Error: FirstName not in scope
employee(*)
  |>  (LastName)
  |>  (FirstName)

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

Rename

Rename a column during projection with as:

employee(*)
  |>  (FirstName as f, LastName)

select
  FirstName as f,
  LastName
from employee;

Generalized Projection

Columns can be transformed during projection using domain functions:

employee(*)
    |>  ( upper:(FirstName) as f,
          upper:(LastName) as LastName,
          3 + Salary as salary_plus_three)

select
  upper(FirstName) as f,
  upper(LastName) as LastName,
  3 + Salary as salary_plus_three
from employee;

Note the colon in upper:(FirstName). 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(EmployeeId , LastName , _ , _ ,
        _ , _ , _ , _ , _ ,
        _ , _ , _ , _ , _ , _ )

select
  EmployeeId,
  LastName
from employee;

Only the first two columns are retained; the rest are projected away. The identifiers EmployeeId and LastName 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(*)
  |> -(BirthDate, Email)

select
    EmployeeId,
    LastName,
    FirstName,
    Title,
    ReportsTo,
    --  BirthDate, -- column projected out
    HireDate,
    Address,
    City,
    State,
    Country,
    PostalCode,
    Phone,
    Fax,
    -- Email
from employee;

All columns except BirthDate 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:())( LastName, FirstName, Title, ReportsTo)

select
    EmployeeId,
    upper(LastName)  as  LastName,
    upper(FirstName) as  FirstName,
    upper(Title)     as  Title,
    upper(ReportsTo) as  ReportsTo,
    BirthDate,
    HireDate,
    Address,
    City,
    State,
    Country,
    PostalCode,
    Phone,
    Fax,
    Email
from employee;

The first parentheses contain the function; the second lists the target columns. The function upper:() is written as arity-0 – a curried form where the column value fills the implicit first argument. This notation borrows from Elixir’s pipe conventions.

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:())( LastName, FirstName, Title, ReportsTo)
  |> $(to_iso:())( BirthDate, HireDate)

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

Chained covers (repetitive but clear):

employee(*)
  |>  $(upper:())(FirstName,LastName)
  |>  $(trim:())(FirstName,LastName)

Containment composition using F-PARAM @ as a placeholder:

employee(*)
  |>  $(trim:(upper:(@)) )(FirstName,LastName)

Pipe composition using F-PIPE /->:

employee(*)
 |>  $(upper:() /-> trim:() )(FirstName,LastName)

All three produce:

select
    EmployeeId,
    trim(upper(LastName))  as  LastName,
    trim(upper(FirstName)) as  FirstName,
    Title,
    ReportsTo,
    BirthDate,
    HireDate,
    Address,
    City,
    State,
    Country,
    PostalCode,
    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
    EmployeeId,
    LastName,
    FirstName,
    Title,
    ReportsTo,
    BirthDate,
    HireDate,
    Address,
    City,
    upper(State) as State,
    Country,
    PostalCode,
    '--------' 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:())(LastName, FirstName | Department = "Executive")

SELECT
  EmployeeId,
  CASE
    WHEN Department = 'Executive' THEN upper(
      LastName
    )
    ELSE LastName
  END AS LastName,
  CASE
    WHEN Department = 'Executive' THEN upper(
      FirstName
    )
    ELSE FirstName
  END AS FirstName,
  Title,
  Department,
  ReportsTo,
  BirthDate,
  HireDate,
  Address,
  City,
  State,
  Country,
  PostalCode,
  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 | Department = "Executive")

select
    EmployeeId,
    LastName,
    FirstName,
    Title,
    Department,
    ReportsTo,
    BirthDate,
    HireDate,
    Address,
    City,
    State,
    Country,
    PostalCode,
    case when Department = 'Executive'
         then 'REDACTED' else Phone end as Phone,
    case when Department = 'Executive'
         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 function composition and chaining:

employee(*)
  |> $(upper:() /-> trim:())(FirstName, LastName | Country = "USA")

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

employee(*)
  |> $$( _:(Department = "Executive" -> upper:(LastName);  _ -> LastName)  as LastName,
         _:(Department = "Executive" -> upper:(FirstName); _ -> FirstName) as FirstName)

Regular Expression Column Addressing

A regular expression can select columns by name pattern:

employee(*)
  |> ( /Date/ )

select
    BirthDate,   -- matches Regex /Date/
    HireDate     -- matches Regex /Date/
from employee;

Both BirthDate and HireDate match the pattern /Date/. The regex applies only to column names (not namespaces or indexes). Delightql uses UNIX BRE syntax within the REGEX sigil / /. Append i for case-insensitive matching: /date/i.

Restrictions. Regex column addressing may only appear in PROJECT-IN, PROJECT-OUT, or the second parentheses of MAP-COVER. It cannot:

• Be followed by as
• Be passed directly to a function
• Appear in EMBED +( ), BASIC-COVER $$( ), RENAME-COVER *( ), or GROUP-MODULO

Rename Cover

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

employee_2019(*)
  |> *( FamilyName as LastName)

select
    EmployeeId,
    FamilyName as LastName,
    FirstName,
    Title,
    ReportsTo,
    BirthDate,
    HireDate,
    Address,
    City,
    State,
    Country,
    PostalCode,
    Phone,
    Fax,
    Email
from employee_2019;

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_2019(*)
  |> +(  strftime:('%Y',BirthDate) - 2 as two_years_before_birth )

select
    *,
    strftime('%Y',BirthDate) - 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):

f_to_c:(f) :- (f - 32.0)*0.5556
f_to_k:(f) :- f_to_c:(f)+273.15

?- boston_temps(*)
     |> +$(f_to_c:() as :"{@}_c")( /_temp/ )
     |> +$(f_to_k:() as :"{@}_k")( /_temp/ )

SELECT
  month,
  daily_max_temp,
  daily_min_temp,
  daily_avg_temp,
  (daily_max_temp - 32.0) * 0.5556 AS daily_max_temp_c,
  (daily_min_temp - 32.0) * 0.5556 AS daily_min_temp_c,
  (daily_avg_temp - 32.0) * 0.5556 AS daily_avg_temp_c,
  (daily_max_temp - 32.0) * 0.5556
  + 273.15 AS daily_max_temp_k,
  (daily_min_temp - 32.0) * 0.5556
  + 273.15 AS daily_min_temp_k,
  (daily_avg_temp - 32.0) * 0.5556
  + 273.15 AS daily_avg_temp_k
FROM boston_temps;

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 daily_max_temp_c, daily_min_temp_c, etc. The second parentheses specify the target columns–here, all columns matching /_temp/.

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
  Department
from employee
  group by Department;

-- Produces the same result as:

select
  distinct Department
from employee;

Delightql reflects this congruence with unified syntax.

Distinct

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

employee(*)
  |> %(Department)

select
  distinct Department
from employee;

Multiple columns return distinct combinations:

employee(*)
  |> %(Department, State)
  |> #(Department,State descending)

select
  distinct Department, State
from employee
    order by Department asc, State desc;

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

employee(*)
  |> %(*)  //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):

employee(*)
  |> %(Department ~>  count:(*) , sum:(Salary) )

select
  Department,  -- grouping column
  count(*),    -- reduced column
  sum(Salary)  -- reduced column
from employee
  group by Department;

Grouping columns may be expressions:

employee(*)
    |> %( Salary > 50000  as high_low,
          upper:(Department) ~>
            count:(*) ,
            avg:(Salary) )

select
  Salary > 50000 as high_low, -- grouping column
  upper(Department),  -- grouping column
  count(*),    -- reduced column
  avg(Salary)  -- reduced column
from employee
  group by upper(Department), (Salary > 50000) ;

Unsafe Reduced Column

Some SQL dialects prohibit non-aggregated columns in a group by, since the result would be arbitrary:

select
  Department,  -- grouping column
  LastName,    -- arbitrary reduced column
  FirstName   -- arbitrary reduced column
from employee
  group by Department;

The ACCEPT-ARB sigil ~? explicitly requests such columns:

  employee(*)
    |> %(Department ~? LastName, FirstName)

This is most useful when an aggregate function creates natural affinity to a specific row. SQLite provides useful semantics when the aggregate is max() or min() – it returns values from the row containing the max or min.

  employee(*)
    |> %(Department
          ~>
        max:(Salary)
          ~?
        LastName, FirstName)

Whole-Group Aggregate Functions

To aggregate without grouping, omit the grouping columns:

employee(*)
  |>  %( ~>  count:(*) , sum:(Salary) )

  select
    count(*),
    sum(Salary)
  from employee;

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

employee(*) ~>  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:

employee(*)
    |>  %( Department ~>
            count:(%LastName) ,
            count:(%BirthDate))

select
  Department,
  count(distinct LastName),
  count(distinct BirthDate)
from employee
  group by Department;

Filter Clauses

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

employee(*)
  |>  %( Department ~>
         count:(%LastName) ,
         count:(%BirthDate),
         count:(LastName | length:(LastName) > 10)
            as long_lastname_count)

For dialects supporting FILTER:

select
  Department,
  count(distinct LastName),
  count(distinct BirthDate),
  count(LastName)
    filter
      (where length(LastName) > 10) as long_lastname_count
from employee
  group by Department;

For dialects without FILTER, delightql emits a CASE expression:

select
  Department,
  count(distinct LastName),
  count(distinct BirthDate),
  count(case when length(LastName) > 10
            then LastName else null) as  long_lastname_count
from employee
  group by Department;

Having

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

employee(*)
  |> %( Department ~> count:(*) as employee_count)
      ,  employee_count > 50

Read this as: “group employees by Department, count each group, then keep only groups with more than 50 rows.”

select
  Department,
  count(*) as employee_count
from employee
  group by Department
    having count(*) > 50;

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 Department, count(*) AS employee_count
FROM employee
GROUP BY Department
HAVING count(*) > 50;


-- equivalent to:

SELECT * FROM (
SELECT Department, count(*) AS employee_count
FROM employee
GROUP BY Department
) WHERE employee_count > 50;

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

employee(*)
|> %(Department ~> count:(*) as employee_count),
employee_count > 50

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

Domain Functions

Functions on domains are ordinary functions–what most programming languages simply call “functions.” The qualifier “on domains” distinguishes them from functions on relations (table-valued functions, covered elsewhere). Here, “domain” means data type: the addition function + in SELECT Salary + bonus FROM employee expects numeric operands.

SQL provides several syntaxes for functions: standard functor notation, infix operators, and CASE expressions. This section covers how to call domain functions in delightql. For defining functions, see Function Definition.

Domain functions can appear anywhere a column is valid – they are substitutable for the value they compute:

• During projection, as a transformation of a column
• During selection, as a transformation prior to comparison
• During grouping, as a transformation prior to grouping
• During aggregation, as a reduction of multiple values

Delightql’s default function syntax incorporates a colon: foo:(x) rather than foo(x). This functional functor notation distinguishes functions from relations. Delightql’s syntax derives from Prolog, where foo(x,y) denotes a relation. Functional functor notation marks the subset of relations that are functions – mappings that return exactly one value per input.

Standard Function Invocation

employee(*),
  length:(LastName) > 5
  |> +( trim:(upper:(LastName)) as CapitalizedLastName)

Three functions appear in two contexts:

• length:() in a predicate (sigma clause)
• trim:() and upper:() in an embed, using containment composition

The colon signals to both compiler and programmer that the functor returns a domain value, not a relation.

Function Pipe Composition

The F-PIPE sigil /-> composes functions left to right:

employee(*), length:(LastName) > 5
  |> +( LastName /-> upper:() |-> trim:() as CapitalizedLastName)

The pipe begins with a domain expression (a column, literal, or function call). Each subsequent function is in curried form – the piped value fills the first argument.

When the value belongs in a different argument position, use the F-PARAM sigil @:

employee(*), length:(LastName) > 5
  |> +(  BirthDate /-> strftime:("%Y",@) as BirthYear )

The LAMBDA sigil :( ) creates an inline function. The @ marks where the piped value is placed.

employee(*), length:(LastName) > 5
    |> +(  BirthDate /-> strftime:("%Y",@) |-> :( @ + 2) as BirthYearPlusTwo,
           BirthDate /-> strftime:("%Y",@) |-> sqrt:() as SqrtOfBirthYear)

Domain Operators

Delightql supports standard arithmetic operators:

Arithmetic domain operators

No implicit precedence. Delightql requires explicit parentheses when mixing operators. There is no PEMDAS. See “Order of Operations” for the conventional rules delightql declines to adopt. This is a deliberate design choice favoring clarity over convention.

_(first,second,third @ 1.3,20,30)
    |> ( 1 + (first / third),
        -third / ((first * second) / 23.33),
        (third % 11) - 42)

select
  1+(first/third),
  -third / ((first*second)/23.33),
  (third % 11) - 42
from (select 1.3 as first, 20 as second, 30 as third);
--  1.04333333333333|-26.9192307692308|-34

Case Simple

SQL’s CASE expression serves as a switch statement. Delightql represents it as what it is: a function, and therefore a relation.

employee(*)
    |> +(  _:(Department @
            "engineering"  -> "tech";
            "data science" -> "tech";
            _              -> "other") as kind )

select
  *,
  case Department
    when 'engineering' then 'tech'
    when 'data science' then 'tech'
    else 'other'
  end as kind
from employee;

The ANON-FUNC sigil _:( ) creates an anonymous case function. The F-AND sigil -> separates input patterns (left) from output values (right). The SEMI-OR sigil ; separates cases. The header Department @ binds the input to the Department column.

This is stacked notation applied to functions: the -> acts as a special comma that declares a functional dependency – columns left of the arrow are inputs, columns right are outputs.

Named case functions. The same notation defines reusable functions in assertion mode:

department_kind(
  Department     -> kind
  ------------------
  "engineering"  -> "tech";
  "data science" -> "tech";
  _              -> "other"
)

?- employee(*)
  |> +(  department_kind:(Department) as kind )

The predicate department_kind is both a table (two columns, three rows) and a function (input determines output). The -> tells the compiler which column is the input when invoked as a function.

Without ->, the predicate is valid but not callable as a function:

//WILL NOT WORK!! (at least if you want to use it for function calls)
//    .. it is a perfectly acceptable predicate
department_kind("engineering"  , "tech")
department_kind("data science" , "tech")
department_kind( _             , "other")

Prolog calls these input/output declarations modes or adornments. Delightql’s -> serves the same purpose: columns left of the arrow must be instantiated inputs.

Case Search Function

Case search evaluates conditions rather than matching values. Two syntaxes exist.

Condition-first notation uses -> pointing to the return value:

students(*)
    |> %(  _:( grade > 90              ->  "A";
            grade > 80, grade <=90  ->  "B";
            grade > 70, grade <=80  ->  "C";
            grade > 60, grade <=70  ->  "D";
            _                       ->  "F") as score
            ~> count:(*) )
    |>  #(score)

Conditions can be conjoined with , (and). For disjunction, use the keyword or:

students(*)
    |> %(  _:( grade > 90  or apple_given="true" -> "A";
              grade > 80, grade <=90            -> "B";
              grade > 70, grade <=80            -> "C";
              grade > 60, grade <=70            -> "D";
              _                                 -> "F") as score
            ~> count:(*) )
    |>  #(score)

Like SQL’s CASE, the first matching clause wins.

members(*)
    |> (  profile_nm,
          account_nm,
          location,
          _:(
             "north india m" | location in ("in";"rajkot"), profile_nm="sally";
             "north india f" | location in ("in";"rajkot");
             "pakistan f"    | location in ("pk"; "france"), profile_nm="sally";
             "pakistan m"    | location in ("in";"rajkot")
          ) as continent )
    |>  #(profile_nm,account_nm)

Format Function and Strings

Format strings interpolate column values into text. The F-STRING sigil : prefixes a string literal:

employee(*)
  |> +( :"{LastName}, {FirstName} making ${Salary}" as readable)

select
    EmployeeId,
    LastName,
    FirstName,
    Title,
    ReportsTo,
    BirthDate,
    HireDate,
    Salary,
    Address,
    City,
    State,
    Country,
    PostalCode,
    Phone,
    Fax,
    Email,
    LatName || ', ' || FirstName || ' making $' || Salary as readable
from employee;

Braces { } enclose column names. Format strings also permit the usage of the following escape sequences:

Strings in delightql are only double-quoted. Any double-quoted string without a colon is a raw string and accepts neither interpolation nor escape sequences. Delightql also uses the triple-double-quotes to make for easier embedding of double quotes. These too may be used as string interpolators with a preceding colon.

_(1) |> ("""
  This is a banner and
  has no \n as a newline escape
  but the ones you typed are
  there because they are the bytes
  you typed
  """)

_(1) |> (:"""
  This is a banner and
  HAS the \n as a newline escape
  AND the ones you typed are
  there because they are the bytes
  you typed.  Also, you can type a
  double-quote as " or \Q and a
  single-quote as ' or \q
  """)

Window/Analytic Functions

Window functions combine aggregation with per-row results. They aggregate over a dynamic window but return one value per row – still scalar functions by definition. Array languages are the closest analog in other programming paradigms.

employee(*)
    |> (EmployeeId,
        DepartmentId,
        Salary,
        dense_rank:( <~ %(DepartmentId),#(Salary)) as ranking )

SELECT
  EmployeeId,
  DepartmentId,
  Salary,
  dense_rank() OVER (
    PARTITION BY
      DepartmentId
    ORDER BY Salary
  ) AS ranking
FROM employee;

The F-OVER sigil <~ introduces the window specification. Everything before <~ is passed to the function; everything after defines the window frame.

Window specification syntax. Comma-separated, all optional:

• %( ) – partition clause (one allowed)
• #( ) – order clause (one allowed)
• rows(from, to), range(from, to), or groups(from, to) – frame specification (one allowed)

Frame Bounds:

Window frame bound syntax

Examples:

  ntile:( 10  <~  %(DepartmentId),#(-Salary), groups(_,_))
  ntile:( 10  <~  %(DepartmentId),#(-Salary), groups(+1,_))
  ntile:( 10  <~  %(DepartmentId),#(-Salary), rows(1,.))
  ntile:( 10  <~  %(DepartmentId),#(-Salary), rows(_,-(upto*2)))
  ntile:( 10  <~  %(DepartmentId),#(-Salary), range(.,upto*2) )

  ntile(10) over
    ( partition by DepartmentId order by Salary desc
      groups between
        unbounded preceding and unbounded following)
  ntile(10) over
    ( partition by DepartmentId order by Salary desc
      groups between 1 following and unbounded following)
  ntile(10) over
    ( partition by DepartmentId order by Salary desc
      rows between 1 preceding and current row)
  ntile(10) over
    ( partition by DepartmentId order by Salary desc
      rows between unbounded preceding and (upto*2) preceding)
  ntile(10) over
    ( partition by DepartmentId order by Salary desc
      range between current row and (upto*2) following)

Default window. For an empty window specification, use <~ with nothing following:

  employee(*)
    |> +(  row_number:( <~ ) as row_number )

select
  *,
  row_number() over () as row_number
from employee;

Scalar Subquery

Scalar subqueries return a single value (one row, one column) usable anywhere a column is valid. Delightql transforms a relation into a scalar subquery by postfixing its name with : and using interior notation.

Uncorrelated. The subquery is independent of the outer query:

employee(*)
    |> (FirstName,
        LastName,
        Salary,
        employee:( ~> avg:(Salary)) as AvgSalary)

select
  FirstName,
  LastName,
  Salary,
  (select avg(Salary) from employee) as AvgSalary
from employee;

The F-COLON sigil : after the relation name signals a scalar subquery. The interior notation–here ~> avg:(Salary) – must produce exactly one row and one column.

Correlated. The subquery references values from the outer query. Use an explicit condition to correlate on a column:

employee(*) as e
    |> (FirstName,
        LastName,
        Salary,
        employee:( ~> avg:(Salary)) as AvgSalary,
        employee:( , DepartmentName = e.DepartmentName ~> avg:(Salary)) as AvgSalaryInDept)

select
  FirstName,
  LastName,
  Salary,
  (select avg(Salary) from employee) as AvgSalary,
  (select avg(Salary) from employee
      where DepartmentName = e.DepartmentName) as AvgSalaryInDept
from employee e;

Context-Aware Functions

A context-aware function closes over columns from its invocation context rather than receiving them as explicit parameters.

employee(*)
  |> ( cost_of_living:(..) as col_adjusted_salary)

The UP-CONTEXT sigil .. signals that the function references columns from the surrounding scope. It is required – a reminder that the function is never truly nullary.

Context-aware functions are defined with .. in their signature:

// only works within the context of a
//  relation that has 'City' and 'Salary' columns

cost_of_living:( .. ) :-
  _:(  City in ("San Francisco";"Boston";"New York")
          -> Salary*0.8;
      City in ("Amarillo"; "Knoxville")
          -> Salary*1.45;
      _ -> Salary)

This function can be invoked on any relation with City and Salary columns – the free variables in its body. These columns are bound at the call site, not passed explicitly.

Context-aware functions have no SQL counterpart; they are expanded inline during transpilation.

Boolean Expressions as Columns

Predicates – what delightql calls sigma clauses – can appear in column position, returning boolean values. Some SQL dialects lack a boolean type; these transpile to 1 and 0.

employee(*)
    |> +( DepartmentCity="San Francisco"
            and Title!="Engineer"
                AS san_fran_engineer,
          DepartmentCity="San Francisco"
                AS san_fran,
          Salary > 150000
            or BonusPercentage > 200
                AS well_compensated,
          Title!="Engineer"
                AS is_engineer)

  select
    *,
    DepartmentCity = 'San Francisco' and Title != 'Engineer' as san_fran_engineer,
    DepartmentCity = 'San Francisco' as san_fran,
    Salary > 150000 or BonusPercentage > 200 as well_compensated,
    Title != 'Engineer' as is_engineer
  from employee;

Compound predicates must use keywords (and, or) rather than sigils ( , , ;) when appearing as column expressions.

Existence tests. Semi-joins and anti-joins also return booleans when used in column position:

employee(*)
 |> +( +department(,
         department.DepartmentId
          =employee.DepartmentId),
      \+ department(,
         department.DepartmentId
          =employee.DepartmentId),
      +between(Salary,50000,75000))

  select
    *,
    --
    exists (select 1 from department
      where department.DepartmentId=employee.DepartmentId),
    --
    not exists (select 1 from department
      where department.DepartmentId=employee.DepartmentId),
    --
    Salary between 50000 and 75000
  from employee;

Compound Data Constructors

The enclyphs { } and [ ] construct compound data–records and tuples. They are functions, though they look like syntax. Their behavior depends on context:

Compound data constructor behavior by position

-- scalar constructors
employee(*) |>
  ( [LastName,FirstName] as interior_tuple )
employee(*) |>
  ( {LastName,FirstName} as interior_record )

-- aggregate constructors
employee(*)
  |> ( Department
        ~>
      [LastName,FirstName] as table_of_tuples )
employee(*)
  |> ( Department
        ~>
      { LastName,FirstName } as table_of_records )

These constructors transpile to JSON in most SQL dialects. SQLite and Postgres both provide JSON as a data type with supporting functions. See SQLite JSON1. But the concept is not about JSON per se but about nested structure.

The compound data types introduced here provide groundwork for pivots, melts, and tree-grouping (covered later). Programmers needing arbitrary JSON manipulation can call SQL’s JSON functions directly:

json_array:(last_name, first_name).

Scalar Interior Record

The INTERIOR-RECORD enclyph { } creates a nested row addressable by name:

employee(*)
  |> (Department , { LastName,FirstName } as name  )

Scalar interior record result

Column names become keys. To specify different keys:

employee(*)
  |> (Department ,
      { "FirstName": FirstName ,
        "LastName" : LastName} as name  )

Access nested fields with JSON-access notation (see next section).

employee(*)
  |> (Department ,
      { "FirstName": FirstName ,
        "LastName" : LastName} as name  )
  |> ( Department, name:{.FirstName})

with
    _cpr0 as (
        select
          Department,
          json_object('FirstName',FirstName,
                      'LastName' ,LastName) as name
        from employee)
    select
        Department,
        name ->> "$.FirstName" as FirstName
    from _cpr0;

Aggregate Interior Record

In a reduction position, { } collects multiple records into a table:

employee(*)
  |> %(Department ~> { LastName,FirstName } as name )

  Accounting

  [
    {"LastName":"Moorrud","FirstName":"Erhard"},
    {"LastName":"Cowwell","FirstName":"Orlando"},
{"LastName":"Tuley","FirstName":"Hanan"},
{"LastName":"Unstead","FirstName":"Gretchen"}
  ]

  Business
  Development

  [
    {"LastName":"Marcone","FirstName":"Dinnie"},
    {"LastName":"Tuffell","FirstName":"Mathias"},
    {"LastName":"Harbord","FirstName":"Venita"},
    {"LastName":"Hinstock","FirstName":"Ashli"}
  ]

The outer [ ] in the JSON represents multiplicity – a list of rows. The interior table has a uniform schema of named columns represented as objects.

Scalar Interior Tuple

The INTERIOR-TUPLE enclyph [ ] creates a nested row addressable by position:

employee(*)
  |> (Department , [LastName,FirstName] as name )

Scalar interior tuple result

Access elements by index:

employee(*)
  |> (Department , [LastName,FirstName] as name )
  |> ( Department, name:[1] as first_name)

Aggregate Interior Tuple

In a reduction position, [ ] collects multiple tuples into a table:

employee(*)
  |> %(Department ~> [ LastName,FirstName ] as name )

   [
    ["Erhard","Moorrud"],
    ["Orlando","Cowwell"],
    ["Hanan","Tuley"],
    ["Gretchen","Unstead"]
   ]

  [
    ["Dinnie","Marcone"],
    ["Mathias","Tuffell"],
    ["Venita","Harbord"],
    ["Ashli","Hinstock"]
  ]

JSON’s [ ] does double duty here: the outer brackets indicate multiple rows; the inner brackets indicate tuples. This is a syntactic limitation of JSON, not a semantic ambiguity. If JSON had a distinct tuple syntax – perhaps parentheses – this overloading would not exist.

Nesting

The power of these constructors emerges when nested:

employee(*)
  ~>  {  Title ,
           "people_by_state":
             ~>{ State ,
                "people" : ~>{FirstName, LastName} } }
                  as people_by_state_within_title

This tree-structured output is covered in detail in the Tree Groups section.

Compound Data Pathing

Once compound data has been constructed, access its contents via pathing syntax.

Array access. Name the column, followed by a colon, the [ ] enclyph, and a 0-indexed position:

employee(*)
  |> (Department , [LastName,FirstName] as name )
  |> ( Department, name:[0] as first_name)

Record access. Name the column, followed by a colon, the { } enclyph, and a dot-prefixed key:

employee(*)
  |> (Department ,
      { "FirstName": FirstName ,
        "LastName" : LastName} as name  )
  |> ( Department, name:{.FirstName})

Nested access. Chain steps with dots to descend into nested structures:

users(*)
  |> ( {last_name, "hardcoded" : [ 1,'x'] } as packet)
  |> ( packet:{.hardcoded.1})
// returns 'x' for as many records as there are users

_(x @ [ 1 , 2 , {"hardcoded" : {"deeper": [ 2 , 3]}}]) as named_anon_table
  |> (x:[2.hardcoded.deeper.0])

Uniform dot notation. Unlike most languages, delightql does not alternate between .key and [index] when pathing. All steps – whether into a record or an array – use dot separation: key.1.nested.0 rather than key[1].nested[0]. The enclyph at the start ({ } or [ ]) establishes the top-level type; thereafter, dots suffice.

Addressing Columns by Index

Named columns are preferred for clarity, but some schemas resist naming–wide CSVs, auto-generated headers, legacy tables with hundreds of columns. For these, delightql provides index notation.

employee(*)
  |> ( |1|, |2|, |3| )

select
    EmployeeId, -- at position 1
    LastName,   -- at position 2
    FirstName   -- at position 3
from employee;

The INDEX enclyph | | encloses an integer literal (no expressions). Indices are 1-based; negative indices count from the end.

employee(*)
  |> (|-3|, |-2|, |-1|)

SELECT
  Phone,  -- position -3
  Fax,    -- position -2
  Email   -- position -1
FROM employee;

Indexing conventions. Column indices are 1-based, following SQL’s ORDER BY 1 convention. Array pathing is 0-based, following JSON/JavaScript convention. The first column is |1|; the first array element is [0].

Column Ranges

A COLUMN RANGE |start:end| selects a contiguous slice of columns by position. Both bounds are inclusive and 1-based.

users(*)
  |> ( |1:3| )

SELECT id, first_name, last_name
FROM users;

Either bound may be omitted. An open start means “from the first column”; an open end means “through the last column”:

users(*)
  |> ( |:3| )           -- first three columns

SELECT id, first_name, last_name
FROM users;

users(*)
  |> ( |5:| )           -- fifth column onward

SELECT age, status, country, created_at, last_login, balance
FROM users;

Negative indices count from the end, following the same convention as single ordinals:

users(*)
  |> ( |-3:-1| )        -- last three columns

SELECT created_at, last_login, balance
FROM users;

users(*)
  |> ( |:-2| )          -- all but the last column

SELECT id, first_name, last_name, email, age, status, country, created_at, last_login
FROM users;

Ranges can be scoped to a table alias, just like single ordinals:

users(*) as u
  |> ( u|1:3|, u|5:7| )

SELECT id, first_name, last_name, age, status, country
FROM users AS u;

Ranges compose with other operators. For example, EMBED-MAP can apply a function across a range of columns:

users(*)
  |> +$(:( @ + 100) as :"{@}_offset")(|2:5|)

Column range syntax summary

When ranges break down. The same caveats as single ordinals apply: schema changes silently shift what a range covers. Prefer named columns for stable queries; reserve ranges for exploration and hostile schemas.

Index notation works with PROJECT-OUT, RENAME-COVER, MAP-COVER, GROUP-MODULO, and other operators:

employee(*)
  |> -( |1|, |2| , |-2| )

Scoped Index Notation

In joins, indices can be scoped to a table alias:

employee(*) as e,
  department(*) as d, d.DepartmentName=e.DepartmentName
  |> (  |14| as email,
        e|1| as EmployeeId,
        d|-4| as DepartmentName)

Unscoped indices refer to the total column order across all joined tables. The following addressing schemes are available:

Index addressing schemes

When index notation breaks down. Total indexing across joins depends on column counts and join order. For this reason, it’s probably wise to reserve index notation for exploration, and managing hostile schemas.

Reposition Operator

The REPOSITION operator *[column as position] moves columns to specific positions without removing any columns.

users(*) |> *[email as 1]

SELECT email, id, first_name, last_name, age FROM users;
-- Before: (id, first_name, last_name, email, age)
-- After:  (email, id, first_name, last_name, age)

The column email moves to position 1; all other columns shift to accommodate.

Positive and Negative Positions

Positions are 1-indexed. Negative positions count from the end:

Position meanings for the reposition operator

users(*) |> *[id as -1]

Moves id to the last position:

Before: (id, first_name, last_name, email, age)
After:  (first_name, last_name, email, age, id)

Multiple Repositions

Multiple columns can be repositioned in a single operation:

users(*) |> *[email as 1, age as 2]

Before: (id, first_name, last_name, email, age)
After:  (email, age, id, first_name, last_name)

Columns are placed in the order specified; remaining columns fill the gaps.

Common Expressions

Delightql has two forms of common expression: the traditional common table expression, and a delightql-specific common function expression.

Common Table Expressions

Create a common table expression (CTE) by naming a functor with a glob, followed by a :, also known as SHADOW-NECK, followed by the query that is assigned to that name. This syntax is called pre-labeling.

Once, a common table expression is defined, it is sufficient to query from that as if it were a table.

adults(*) : users(*), age > 30
adults(*)

WITH "adults" AS (
  SELECT *
  FROM "users"
  WHERE "age" > 30
)
SELECT *
FROM "adults";

An alternate syntax, called post-labeling, allows the CTE to be named after the query by postfixing a valid query with the SHADOW-NECK : and a simple identifier:

users(*), age > 30 : adults
adults(*)

These syntaxes may be intermixed:

us_users(*): users(*), country = 'USA'
orders(*), status = 'completed' : completed_orders
us_users(*), completed_orders(*)

WITH "us_users" AS (
  SELECT *
  FROM "users"
  WHERE "country" IS NOT DISTINCT FROM 'USA'
),
"completed_orders" AS (
  SELECT *
  FROM "orders"
  WHERE "status" IS NOT DISTINCT FROM 'completed'
)
SELECT *
FROM "us_users"
CROSS JOIN "completed_orders";

Common Function Expressions

Create common function expressions (CFEs) – functions whose name is created for the duration of the query – by pre-labeling with a functional functor. A functional functor is a functor with a colon separating the identifier from the opening parenthesis. The SHADOW-NECK separates the functional functor on the left from any valid domain expression on the right. CFEs may only be created by pre-labeling.

enweirden:(age) :
  age /-> :(@ - 18) /-> max:(0) /-> min:(100)

users(*) |> (enweirden:(age) as silly, age)

SELECT
  min(max("age" - 18, 0), 100) AS "silly",
  "age" AS "age"
FROM "users";

CTEs and CFEs may be intermixed:

double:(x) : (x * 2)
users(*), age > 25 : adults
triple:(y) : (y * 3)
young_adults(*): adults(*), age < 40
young_adults(*)
  |> ( id,
      first_name,
      age,
      double:(age) as doubled,
      age /-> double:() /-> double:() as quadrupled,
      triple:(age) as tripled,
      double:(triple:(age)) as sextupled)

WITH adults AS (
    SELECT
        *
    FROM users
    WHERE age > 25
),
young_adults AS (
    SELECT
        *
    FROM adults
    WHERE age < 40
)
SELECT
    id,
    first_name,
    age,
    (age * 2) AS doubled,
    ((age * 2) * 2) AS quadrupled,
    (age * 3) AS tripled,
    ((age * 3) * 2) AS sextupled
FROM
    young_adults;

Recursive Common Table Expressions

A common table expression becomes recursive when one of its defining clauses references the CTE being defined. Delightql detects this self-reference and emits WITH RECURSIVE.

Sequence generation:

_(n @ 1) : nums
nums(*), n < 100 |> (n + 1 as n) : nums
nums(*) ~> sum:(n)

WITH RECURSIVE nums(n) AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 100
)
SELECT sum(n) FROM nums;

The first clause seeds the CTE with 1. The second clause references nums and increments until the condition fails. This is the standard pattern: base case, then recursive case with termination condition.

Multiple clauses accumulate:

Non-recursive CTEs can have multiple clauses that combine via UNION ALL:

users_2022(*) |> (first_name, last_name) : names
users_2023(*) |> (first_name, last_name) : names
users_2024(*) |> (first_name, last_name) : names
names(*)

When any clause references the CTE name, the entire CTE becomes recursive:

_(x @ 1) : nums
_(x @ 100) : nums
nums(*), x < 200 |> (x + 1 as x) : nums
nums(*)

Here, two anchor clauses (starting at 1 and 100) seed the recursion. Both sequences grow until reaching 200.

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

employee(*), Salary > 50000

select * from employee where Salary > 50000;

Multiple predicates conjoin naturally:

  employee(*), Salary > 50000,
    trim:(lower:(Department))="engineering"

select * from employee
  where Salary > 50000
    and trim(lower(Department))
      IS NOT DISTINCT FROM 'engineering';

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

// WONT WORK because Salary is not yet in scope
  Salary > 50000,
    trim:(lower:(Department))="engineering",
    employee(*)

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

employee(*),
  Salary > 50000,
  trim:(lower:(Department))="engineering"

// commutativity allowed when all LVars are in scope

employee(*),
  trim:(lower:(Department))="engineering",
  Salary > 50000

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 delightql’s == sigil.

employee(*), Salary > 50000,
    trim:(lower:(Department))="engineering",
    LastName=="John"

select * from employee
  where Salary > 50000
    and trim(lower(Department))
      IS NOT DISTINCT FROM 'engineering'
    and LastName='John';

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

The join-position exception.

The table above describes equality in filter position – conditions referencing columns from zero or one relation. In join position – conditions correlating columns from two or more relations – both = and == compile to SQL =.

This is the safe default for joins as IS NOT DISTINCT FROM in a join condition would treat NULL as a matchable value. The NULL-by-NULL cartesian product is almost never intended and can explode cardinality.

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.

The compiler already distinguishes these contexts: a condition referencing two relations becomes an ON clause; a condition referencing one relation becomes a WHERE clause. The equality semantics ride on this same distinction.

To opt into null-matching joins (the rare case where NULL-to-NULL correspondence is desired), use a danger gate:

employee(*) as e (~~danger://dql/cardinality/nulljoin ON~~),
  department(*) as d,
  e.DepartmentId = d.DepartmentId

Argumentative Grounding

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

stock_ownership(1,stock_id,stock_name,quantity)

select
  stock_id,
  stock_name,
  quantity
from stock_ownership where people_id IS NOT DISTINCT FROM 1;

All argumentative grounding uses null-safe equality.

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

stock_ownership(people_id,5,stock_name,120)

SELECT people_id, stock_name
FROM stock_ownership
WHERE
  stock_id IS NOT DISTINCT FROM 5
  AND quantity IS NOT DISTINCT FROM 120;

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

stock_ownership(people_id,(4 + 1),upper:("msft"),120)

select
  people_id
from stock_ownership where stock_id IS NOT DISTINCT FROM (4+1) and quantity IS NOT DISTINCT FROM 120 and stock_name IS NOT DISTINCT FROM upper('msft');

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, +fired_employees(, e.EmployeeId=id)

SELECT *
FROM employee AS e
WHERE
  EXISTS (
    SELECT 1
    FROM fired_employees
    WHERE
      id IS NOT DISTINCT FROM e.EmployeeId
  );

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

employee(*) as e, \+ fired_employees(, e.EmployeeId=f.id)

select
  *
from employee e
  where not exists (select 1 from fired_employees
                      where id IS NOT DISTINCT FROM e.EmployeeId);

The join condition(s) appears inside the parentheses – this is called interior notation. The relation is tested for provability, not joined for data.

The in Predicate

employee(*), +_(State@"MA";"TX";"AK";"AR")

Syntactic sugar provides the familiar form:

employee(*), State in ("MA";"TX";"AK";"AR")

Both transpile to:

select
  *
from employee where State in ('MA','TX','AK','AR');

The unsugared form generalizes to multi-column comparisons:

employee(*), +_( State, Department @
                 "MA","Engineering";
                 "TX","Engineering";
                 "CA","Sales")

SELECT *
FROM employee
WHERE
  ('MA' IS NOT DISTINCT FROM State
  AND 'Engineering' IS NOT DISTINCT FROM Department)
  OR ('TX' IS NOT DISTINCT FROM State
  AND 'Engineering' IS NOT DISTINCT FROM Department)
  OR ('CA' IS NOT DISTINCT FROM State
  AND 'Sales' IS NOT DISTINCT FROM Department);

Relational in

The literal form tests membership in a fixed list. The relational form tests membership in the result of a query — SQL’s IN (SELECT ...).

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

employee(*), DepartmentId in department(|> (DepartmentId))

SELECT * FROM employee
  WHERE DepartmentId IN (SELECT DepartmentId FROM department);

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

employee(*), State in valid_states(*)

SELECT * FROM employee
  WHERE State IN (SELECT State FROM valid_states);

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:

employee(*), (State, Department) in valid_combos(|> (State, Department))

SELECT * FROM employee
  WHERE (State, Department) IN
    (SELECT State, Department FROM valid_combos);

Negation: not in

employee(*), DepartmentId not in terminated_depts(|> (DepartmentId))

SELECT * FROM employee
  WHERE DepartmentId NOT IN (SELECT DepartmentId FROM terminated_depts);

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:

people(*),
    +_("MA" @
      birth_state;
      death_state;
      work_state;
      marriage_state)

select
  *
from people
  where birth_state IS NOT DISTINCT FROM 'MA'
    or death_state IS NOT DISTINCT FROM 'MA'
    or work_state IS NOT DISTINCT FROM 'MA'
    or marriage_state IS NOT DISTINCT FROM 'MA';

This asks: “does ‘MA’ appear in any of these columns?” The columns become the rows of the anonymous table; the constant becomes the match target.

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 people
  where 'MA' in
    (birth_state,death_state,work_state,marriage_state);

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

people(*), +_(birth_state @ death_state; work_state; marriage_state)

select
  *
from people
  where birth_state IS NOT DISTINCT FROM death_state
    or birth_state IS NOT DISTINCT FROM work_state
    or birth_state IS NOT DISTINCT FROM marriage_state;

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.

employee(*),
  \+empty(LastName),
  \+empty(FirstName)

SELECT *
FROM employee
WHERE
  LastName IS NOT null
  AND '' != trim(LastName)
  AND upper(LastName) NOT IN (
    'NA',
    'N/ A',
    'UNKNOWN'
  )
  AND (FirstName IS NOT null
  AND '' != trim(FirstName)
  AND upper(FirstName) NOT IN (
    'NA',
    'N/ A',
    'UNKNOWN'
  ));

Like and Between

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

employee(*), +like(Email,"%.com"), \+between(Salary,10000,100000)

The above delightql transpiles to the following Sql.

select
  *
from employee
  where
    Email like '%.com' and
    Salary not between 10000 and 100000;

Disjunction

Two syntaxes express OR:

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

employee(*)
  , trim:(lower:(Department)) = "executive"
        or Salary > 120000
  , Title != "Engineer"
  |> %( DepartmentCity
          ~>
        count:(*) as employee_count,
        avg:(Salary) )

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

employee(*)
  , (trim:(lower:(Department)) = "executive"
        ; Salary > 120000 )
  , Title != "Engineer"
  |> %( DepartmentCity
          ~>
        count:(*) as employee_count,
        avg:(Salary) )

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

employee(*), department(*)

SELECT * FROM employee CROSS JOIN department;

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

employee(*), department(*), employee.DepartmentId = department.DepartmentId

SELECT * FROM employee
  JOIN department ON employee.DepartmentId = department.DepartmentId;

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

employee(*), department(*),
  employee.DepartmentId = department.DepartmentId,
  employee.Location = department.Location

Scope is left to right. This is an error:

// INVALID: department not yet in scope
employee(*), department.DepartmentId = employee.DepartmentId, department(*)

USING Shorthand

The .(cols) operator specifies USING columns:

employee(*), department(*.(DepartmentId))

SELECT * FROM employee JOIN department USING (DepartmentId);

Multiple columns are comma-separated:

employee(*), department(*.(DepartmentId, LocationId))

SELECT * FROM employee JOIN department USING (DepartmentId, LocationId);

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

USING may combine with explicit conditions:

employee(*), department(*.(DepartmentId)), employee.StartDate > department.Founded

Multi-Table Joins

Joins chain left to right. Each table enters scope upon appearance:

employee(*),
  department(*.(DepartmentId)),
  location(*.(LocationId)),
  employee.StartDate > "2020-01-01"

SELECT * FROM employee
  JOIN department USING (DepartmentId)
  JOIN location USING (LocationId)
WHERE employee.StartDate > '2020-01-01';

After the third table, columns from all three are in scope.

Self-Join

Aliases distinguish multiple references to the same table:

employee(*) as e, employee(*) as mgr, e.ManagerId = mgr.Id
  |> (e.Name as Employee, mgr.Name as Manager)

SELECT e.Name AS Employee, mgr.Name AS Manager
FROM employee e
  JOIN employee mgr ON e.ManagerId = mgr.Id;

Argumentative Join

Shared identifiers across functors induce join conditions – Prolog-style unification:

employee(Name, Department), department(Department, location)

SELECT employee.Name, employee.Department, department.location
FROM employee
  JOIN department ON employee.Department = department.Department;

The variable Department appears in both functors, unifying the columns.

Multi-table example:

people(people_id, _, last_name),
  stock_ownership(people_id, stock_id, quantity),
  stocks(stock_id, stock_name),
  quantity < 200
  |> (last_name, stock_name)

Argumentative joins are idiomatic in Prolog. Delightql supports them but recommends .(cols) or explicit conditions for wide tables where positional notation becomes error-prone.

Outer Joins

The OUTER-IND sigil ? marks a relation as optional – it may contribute nulls when no match exists.

Left outer (right table optional):

employee(*), department?(*.(DepartmentId))

SELECT * FROM employee LEFT OUTER JOIN department USING (DepartmentId);

Right outer (left table optional):

employee?(*), department(*.(DepartmentId))

Full outer (either optional):

employee?(*), department?(*.(DepartmentId))

Outer joins work with explicit conditions:

employee(*), department?(*), employee.DepartmentId = department.DepartmentId

SELECT * FROM employee
  LEFT OUTER JOIN department ON employee.DepartmentId = department.DepartmentId;

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

employee(*), department?(*),
  employee.DepartmentId = department.DepartmentId,   -- two tables → ON
  department.Status = "active"                        -- one table → WHERE

SELECT * FROM employee
  LEFT OUTER JOIN department
    ON employee.DepartmentId = department.DepartmentId
WHERE department.Status = 'active';

The multi-table condition (employee.DepartmentId = department.DepartmentId) becomes the join’s ON clause. The single-table condition (department.Status = 'active') becomes a WHERE filter – employees with null or inactive departments are excluded.

Semi-Join and Anti-Join

Semi-joins and anti-joins test existence without adding columns – they are predicates, not joins. See Where.

Lateral Joins

Correlated subqueries that return multiple columns use interior relation syntax. See Interior Relations.

ER-Context Joins

When join relationships are defined via ER-context-rules (see DDL), the & and && operators provide concise join syntax:

under normal:
  users(*) & orders(*)

Equivalent to:

users(*), orders(*), users.id = orders.user_id

The & operator performs direct lookup; && finds a path through the ER-graph:

under normal: users(*) && items(*)
// Compiler finds: users -> orders -> items

ER-context joins compose with all other features—filters, projections, aggregations, additional explicit joins.

For defining ER-rules and contexts, see DDL: ER-Context Rules.

Unification and Logical Variables

Delightql inherits from Prolog a simple rule: identifiers unify when their names match exactly. To unify means to insist on equality and via such a simple semantic we can provide an alternate form to joining and filtering.

Unlike Prolog, however, delightql’s identifiers are qualified by their table names.

How Names Are Introduced

The way you access a table determines the names of its columns in scope:

Columns introduced by access pattern

Argumentative access introduces unqualified names – bare identifiers. Wildcard access introduces qualified names – prefixed by table name or alias.

This distinction is the source of most unification behavior.

Unification Creates Joins

When the same name appears in multiple places, unification creates a join condition:

users(user_id, name, _), orders(order_id, user_id, total, _)

Both introduce user_id. Unification produces:

SELECT users.name, orders.order_id, orders.total
FROM users, orders
WHERE users.user_id = orders.user_id;

No explicit join condition is needed here – the shared name insists on it. This is argumentative joining, covered in the Join chapter.

Wildcard Access and Qualification

users(*), orders(*)

This introduces users.user_id and orders.user_id – different names. No unification occurs; the result is a cross join.

To join with wildcard access, use explicit conditions:

users(*), orders(*), users.user_id = orders.user_id

Or use the USING operator .(cols):

users(*), orders(*.(user_id))

Qualified References in Argumentative Access

Argumentative patterns can reference lvars from other tables:

users(*) as u, orders(order_id, u.user_id, total, _)

The u.user_id in positional access matches the u.user_id from users(*) as u, creating unification. This mixes styles: wildcard for one table, positional for another, with explicit cross-reference.

A more elaborate example:

users(*) as u,
reviews(*) as r,
products(product_id, u.user_id, r.rating, _)

Here products unifies with users on u.user_id and with reviews on r.rating – a three-way join through positional cross-references.

Literals and Constraints

Ground terms in positional access create WHERE conditions:

users(user_id, name, "active", _)

The positional grounding filters rows where the third column equals "active". The column status has been unified with the ground value "active".

SELECT user_id, name FROM users WHERE status = 'active';

Self-Unification

The same name repeated in positional access forces equality:

users(user_id, name, user_id, _)

Columns 1 and 3 both bind to user_id. This filters to rows where those columns are equal:

SELECT user_id, name FROM users WHERE column1 = column3;

Anonymous Tables and Unification

Anonymous tables participate in unification through their header names:

users(user_id, name, status, _),
_(status @ "active"; "pending"; "suspended")

The anonymous table introduces status. This matches status from users, creating:

SELECT user_id, name, status
FROM users
WHERE status IN ('active', 'pending', 'suspended');

With wildcard access, qualification is required:

users(*) as u,
_(u.status @ "active"; "pending"; "suspended")

Without the u. prefix, no unification occurs – the anonymous table’s status wouldn’t match u.status.

Lvars as Data in Anonymous Tables

Anonymous tables can use lvars as data values, not just in headers.

Constraint: An lvar cannot appear both in a header and in the data rows of the same anonymous table.

Inverted IN Pattern

users(*) as u,
_("happy" @ u.status; u.feelings; u.worldview)

Find users where "happy" appears in any of these columns:

SELECT * FROM users u
WHERE 'happy' IN (u.status, u.feelings, u.worldview);

Or equivalently:

SELECT * FROM users u
WHERE u.status = 'happy'
   OR u.feelings = 'happy'
   OR u.worldview = 'happy';

The anonymous table’s header is a literal ("happy"); the data rows are lvars from users. This inverts the typical IN pattern.

EAV Transformation

users(*) as u,
_(attribute, value @
  "name", u.name;
  "email", u.email;
  "status", u.status;
  "created", u.created_at)

This is the melt pattern discussed in a later chapter.

Row-Wise Correspondence

users(*) as u,
orders(*) as o,
_(u.status, o.priority @
  u.feelings, o.urgency;
  u.mood, "high";
  "active", "rush")

Each row in the anonymous table represents a valid combination. The result includes only rows where (u.status, o.priority) matches one of the specified pairs:

SELECT *
FROM users u, orders o
WHERE (u.status = u.feelings AND o.priority = o.urgency)
   OR (u.status = u.mood AND o.priority = 'high')
   OR (u.status = 'active' AND o.priority = 'rush');

Summary of Unification Rules

Summary of unification rules

Set Operators

SQL’s set operators UNION, INTERSECT and EXCEPT all operate on union-compatible schemas with ordinality-based matching. This means that to use these operators in SQL you need to arrange an enumeration of columns from each table via position and type only, and that the number of columns must be the same.

It is a somewhat interesting corner of SQL as nowhere else (with the exception of the occasional group by 2) does SQL use the ordinality of a relation’s columns.

Delightql, of course, supports all of these operators but opens up the door to other functionality by

1. relaxing the same number requirement and permitting ragged unions and intersects
2. having a preference for naming-based access

To make this more concrete, consider delightql’s UNION CORRESPONDING:

  users(*) ; users_2024(*)

The semicolon sigil ; of UNION CORRESPONDING separates two tables much like a comma would for joining. With this operator columns are matched by their names and a super set of both tables’ columns are synthesized.

Delightql re-introducesThe SQL standard actually defines a CORRESPONDING BY clause (SQL-92 (ISO/IEC 9075:1992)) that can be used with INTERSECT, UNION, and EXCEPT, though, as usual, few actually implemented it. It’s even a reserved word. this CORRESPONDING name-based alignment mode as an alternative to SQL’s normal UNION-like ordinal alignment. Delightql prefers name-based alignment but still offers ordinal alignment for backwards compatibility.

Delightql’s UNION-like operators are the following:

Set operator alignment modes

Union All Corresponding (;)

Aligns by name, NULL-padding missing columns. Output schema: first relation’s columns, then non-overlapping columns from the second.This is closer in definition to OUTER UNION. The few SQLs that implement UNION ALL CORRESPONDING do so by outputting the intersection of the two column sets, instead of the union of the two column sets that delightql favors.

 _( a,b,c
    -------
    1,2,3;
    4,5,6)
    ;
 _( d,   a,b
   -------
   "foo",10,20;
   "bar",40,50)

Union All Corresponding is a ragged union.

Smart Union All (|;|)

Aligns by name, but requires both relations to have identical column count and names. Unlike SQL’s UNION/UNION-ALL position is irrelevant. The resulting schema is adopted from the first relation:

employee_2019(*)
  |;|  employee_2018(*)
  |;|  employee_2018(*)

SELECT
  EmployeeId, LastName,
  FirstName, Title, ReportsTo,
  BirthDate, HireDate,
  Address, City, State,
  Country, PostalCode, Phone,
  Fax, Email
FROM employee_2019
UNION ALL
SELECT
  EmployeeId, LastName,
  FirstName, Title, ReportsTo,
  BirthDate, HireDate,
  Address, City, State,
  Country, PostalCode, Phone,
  Fax, Email
FROM employee_2018
UNION ALL
SELECT
  EmployeeId, LastName,
  FirstName, Title, ReportsTo,
  BirthDate, HireDate,
  Address, City, State,
  Country, PostalCode, Phone,
  Fax, Email
FROM employee_2018;

Positional Union All (||)

Aligns by position. Requires identical column count. Useful for intentional realignment.The below example uses interior relations to shape each relation prior to the UNION ALL.:

users_2024(|> (last_name,first_name,age))
  ||
users_2023(|> (LastName,First,Age))

Set Semantics vs Multiset Semantics

All of delightql set operators are actually multiset operators inasmuch as they preserve duplicates. In other words, all set operators are ALL-flavored.

If set semantics are required, use DISTINCT ALL via |> %(*).

employee_2019(*) |;| employee_2018(*) |> %(*)

SELECT
  EmployeeId, LastName,
  FirstName, Title, ReportsTo,
  BirthDate, HireDate,
  Address, City, State,
  Country, PostalCode, Phone,
  Fax, Email
FROM employee_2019
  UNION  --- NOT UNION ALL
SELECT
  EmployeeId, LastName,
  FirstName, Title, ReportsTo,
  BirthDate, HireDate,
  Address, City, State,
  Country, PostalCode, Phone,
  Fax, Email
FROM employee_2018;

which is equivalent to

SELECT DISTINCT * FROM
  (SELECT
    EmployeeId, LastName,
    FirstName, Title, ReportsTo,
    BirthDate, HireDate,
    Address, City, State,
    Country, PostalCode, Phone,
    Fax, Email
  FROM employee_2019
    UNION ALL
  SELECT
    EmployeeId, LastName,
    FirstName, Title, ReportsTo,
    BirthDate, HireDate,
    Address, City, State,
    Country, PostalCode, Phone,
    Fax, Email
  FROM employee_2018)
;

Intersects via correlation

Having introduced the operators above, one would assume that a new sigil for intersects is in the offing. Instead delightql chooses to reuse the same syntax for correlations to represent a statement of which columns to intersect on.

After any union-flavored multiset operator, conjoin a condition that correlates the previous relations together. From such a union an intersection results:

employee_2019(*) as e1 |;|
  employee_2018(*) as e2,
  e1.EmployeeId = e2.EmployeeId

SQL’s INTERSECT only matches on the entire tuple, it lacks an INTERSECT ON/BY parameterization. Delightql’s correlation syntax lets you choose which columns to match on – a per-column intersection that SQL cannot express without rewriting the query as a pair of EXISTS subqueries.