# Relix

> Relix is a relational-algebra query language with an embedded Java engine. A `.relix` script declares data (inline tables, CSV or JSON files, databases, HTTP APIs), names intermediate results as views, and marks the results it wants with `query`. Each operator takes a relation in parentheses and returns one, so a query reads from the innermost relation outwards. It is not SQL and not a pipe language: there is no `FROM`, no `WHERE` clause and no `|>`.

Everything below this summary is enough to write a correct script. Three sources, in order of authority:

- **Syntax**: the [grammar](https://relix.darkcollective.com/reference/language/grammar.ebnf.txt), plain-text EBNF held to the parser on every build. Read it first for anything non-trivial.
- **Meaning and idiom**: this file. Every example in it is run on every build, and the output shown is the engine's.
- **Detail**: the [language reference](https://relix.darkcollective.com/reference/index.html), one page per operator and function.

For choosing *which* operator a problem needs — as opposed to how one works — the [problem-solving manual](https://relix.darkcollective.com/solving/index.html) classifies a question by its shape (grain, quantifier, difficulty) and hands back the recipe that solves it.

Two machine-readable companions, for a tool rather than a reader: [everything in one file](https://relix.darkcollective.com/llms-full.txt) is every manual page concatenated for a single retrieval, and [operators.json](https://relix.darkcollective.com/reference/operators.json) is a catalog of every operator, predicate and installed function with its glyph, keyword and reference page.

## The shape of a script

```relix
-- Data: an inline table (a Markdown table between [ and ]).
Orders := [
| order_id | customer_id | amount | status    |
|----------|-------------|--------|-----------|
| 1        | 1           | 120    | completed |
| 2        | 1           | 80     | completed |
| 3        | 2           | 50     | pending   |
];

-- A view: a name for an expression. Views cost nothing; use them freely.
Completed := { SELECT status = 'completed' (Orders) };

-- A result: either a named view, or an expression in braces.
query { GROUP customer_id, SUM(amount) -> total (Completed) };
```

```
 customer_id  total
 ───────────  ─────
           1    200
(1 row)
```

A script has three kinds of statement, each ending in `;`:

- **Data**: `Name := [ …table… ];`, or `source Name from csv("file.csv") { header: true, schema: { id: NUMBER, name: STRING } };`. A database source is `source Name from database { url: "${DB_URL}", table: "orders", schema: { … } };`. Column types are `NUMBER`, `STRING`, `BOOLEAN`, `DATE`, `TIME`, `TIMESTAMP`, `DURATION` and `ANY`.
- **Views**: `Name := { expression };`. The braces are required.
- **Results**: `query Name;` or `query { expression };`. There is no `query Name { … }` form.

Comments are `--` to the end of the line and `/* … */`. `//` is not a comment.

## Operator cheat sheet

Every operator has a Unicode glyph and an ASCII keyword that parse to the same thing. Either may be used, and they mix freely. When generating code, the ASCII forms are the safer choice.

| SQL idea | Relix (ASCII) | Glyph | Example |
|---|---|---|---|
| `WHERE` | `SELECT cond (R)` | `σ` | `SELECT amount > 100 (Orders)` |
| column list, `AS` | `PROJECT cols (R)` | `π` | `PROJECT order_id, amount * 1.2 -> gross (Orders)` |
| `DISTINCT` | `DISTINCT (R)` | `δ` | `DISTINCT (PROJECT status (Orders))` |
| `GROUP BY` + aggregates | `GROUP keys, AGG(x) -> name (R)` | `γ` | `GROUP customer_id, SUM(amount) -> total, COUNT(*) -> n (Orders)` |
| `HAVING` | `SELECT` over a `GROUP` | | `SELECT total > 150 (GROUP customer_id, SUM(amount) -> total (Orders))` |
| `ORDER BY` | `SORT keys (R)` | `τ` | `SORT amount DESC (Orders)` |
| `LIMIT`, `OFFSET` | `LIMIT n (R)`, `LIMIT offset, n (R)` | `λ` | `LIMIT 3 (SORT amount DESC (Orders))` |
| top N per group | `TOP n keys PER cols (R)` | | `TOP 1 amount DESC PER customer_id (Orders)` |
| rename | `RENAME (old -> new) (R)` | `ρ` | `RENAME (name -> customer) (Customers)` |
| `JOIN … USING` | `A JOIN B` (natural join on every shared column name) | `⋈` | `Customers JOIN Orders` |
| `JOIN … ON` | `A >< cond B` | `⨝` | `Orders >< Orders.customer_id = Customers.customer_id Customers` |
| `LEFT` / `RIGHT` / `FULL JOIN` | `A LJOIN cond B`, `A RJOIN cond B`, `A FJOIN cond B` | `⟕ ⟖ ⟗` | `Customers LJOIN Customers.customer_id = Orders.customer_id Orders` |
| `EXISTS` / `NOT EXISTS` | `A SEMI cond B`, `A ANTI cond B` | `⋉ ▷` | `Customers ANTI Customers.customer_id = Orders.customer_id Orders` |
| `UNION` / `UNION ALL` | `A UNION B`, `A UALL B` | `∪ ⊎` | |
| `EXCEPT` / `INTERSECT` | `A EXCEPT B`, `A INTERSECT B` | `− ∩` | |
| `CROSS JOIN` | `A CROSS B` | `×` | |

The outer joins are also spelled `|><`, `><|` and `|><|`.

Predicates are `= != < <= > >=` (glyphs `≠ ≤ ≥`), joined by `AND`, `OR` and `NOT` (`∧ ∨ ¬`), plus `x IN {1, 2}`, `x NOT IN {…}`, `x LIKE 'A%'`, `x IS NULL` and `x IS NOT NULL`.

The aggregates are `SUM`, `AVG`, `COUNT(*)`, `COUNT(expr)`, `MIN`, `MAX`, `COLLECT`, `ARGMAX(rank, value)` and `ARGMIN(rank, value)`. The scalar functions include `Len`, `UCase`, `LCase`, `Trim`, `Left`, `Right`, `Mid`, `Replace`, `Abs`, `Round`, `Power`, `IIf(cond, a, b)` (SQL's `CASE`), `Coalesce`, `Nz`, `YEAR`, `MONTH`, `DAY`, `DATE_TRUNC`, `NOW()`, `CStr` and `CDbl`.

## Rules that trip people up

- **`SELECT` filters rows.** It is relational selection, SQL's `WHERE`. Columns are chosen with `PROJECT`.
- **The input comes last, in parentheses**: `SORT amount DESC (Orders)`. A join's condition sits **between** its two inputs: `A >< A.id = B.a_id B`.
- **A function call's parenthesis touches the name.** `Round(x, 2)` is a call. In `SORT name (Users)`, the space makes `name` a sort key and `(Users)` the input.
- **Grouping keys have no braces**: `GROUP region, SUM(x) -> total (R)`. Braces build a struct and silently group by something else. A `GROUP` needs at least one aggregate. For distinct values, use `DISTINCT (PROJECT col (R))`.
- **Name aggregates with `->`.** Otherwise the column gets a derived name such as `sum_amount` or `count_expr`.
- **A condition join keeps both sides' columns.** Where a name clashes, the right side's copy gets an `_r` suffix (`customer_id_r`). A natural join (`JOIN`) keeps one copy of each shared column. Refer to a side's column as `Relation.column` inside a join condition.
- **Sets use braces**: `status IN {'open', 'held'}`. `IN ('open', 'held')` is a syntax error.
- **Conditions must compare.** Write `SELECT active = true (R)`, not `SELECT active (R)`.
- **`NULL` only appears in a null test**: `x IS NULL`, `x = NULL`, `x = ⊥`. There is no NULL literal to write in a value position.
- **`<>` is not an operator.** Use `!=`.
- **Quotes.** Inside `{ … }`, strings take single or double quotes. At statement level (a `source` path or a field value), strings take double quotes only.
- **Views are only names.** `A := { … };` names an expression. Using `A` means exactly what writing the expression in its place would mean, and costs nothing at run time. Split any query with more than one idea in it into named steps.
- **Reserved words as relation names need backticks**: `` PROJECT name (`order`) ``.
- **Ties are real.** `LIMIT 1 (SORT …)` and `TOP 1 …` keep exactly one row: the first in input order among equal keys. To return every row that ties for the best, compare against the maximum instead, as below.

## From question to query

The questions below all use the same two tables. Each block is run after the ones before it, so later blocks use the views earlier blocks define. Bob has only a pending order, and Dan has no orders at all.

```relix
Customers := [
| customer_id | name  | city   |
|-------------|-------|--------|
| 1           | Alice | London |
| 2           | Bob   | Leeds  |
| 3           | Carol | London |
| 4           | Dan   | York   |
];

Orders := [
| order_id | customer_id | amount | status    |
|----------|-------------|--------|-----------|
| 1        | 1           | 120    | completed |
| 2        | 1           | 80     | completed |
| 3        | 2           | 50     | pending   |
| 4        | 3           | 200    | completed |
];

Completed := { SELECT status = 'completed' (Orders) };
```

The method, whatever the question: find the relations that hold the data, filter rows with `SELECT`, combine with `JOIN`, aggregate with `GROUP`, choose the output columns with `PROJECT`, and name each step as a view. Sort and limit only when the question asks for an order or a count of rows, and keep ties unless it asks for exactly one.

### Which customer spent the most? (aggregate, then compare against the maximum)

```relix
Spend := { GROUP customer_id, SUM(amount) -> total (Completed) };
Best  := { GROUP MAX(total) -> total (Spend) };

-- Spend JOIN Best is a natural join on `total`: it keeps every customer at the maximum.
query { PROJECT name, total (Customers JOIN (Spend JOIN Best)) };
```

```
 name   total
 ─────  ─────
 Alice    200
 Carol    200
(2 rows)
```

Alice and Carol tie. `LIMIT 1 (SORT total DESC (…))` or `TOP 1 total DESC (…)` would return Alice alone.

### Which city brought in the most completed revenue? (join first, then aggregate)

```relix
CityRevenue := { GROUP city, SUM(amount) -> revenue (Customers JOIN Completed) };
TopCity     := { GROUP MAX(revenue) -> revenue (CityRevenue) };

query { PROJECT city, revenue (CityRevenue JOIN TopCity) };
```

```
 city    revenue
 ──────  ───────
 London      400
(1 row)
```

### Who has placed more than one order? (SQL's HAVING is a SELECT over a GROUP)

```relix
OrderCounts := { GROUP customer_id, COUNT(*) -> orders (Orders) };

query { PROJECT name, orders (Customers JOIN (SELECT orders > 1 (OrderCounts))) };
```

```
 name   orders
 ─────  ──────
 Alice       2
(1 row)
```

### Which customers have spent nothing? (an anti join: absence is not a group)

Grouping `Orders` can only produce customers who appear in `Orders`, so no aggregate over it finds Dan. Absence is answered by `ANTI`: the left rows with no match on the right.

```relix
query { PROJECT name (Customers ANTI Customers.customer_id = Completed.customer_id Completed) };
```

```
 name
 ────
 Bob
 Dan
(2 rows)
```

### How many orders has each customer placed, including none? (a left join, then COUNT of a column)

A left join keeps Dan with NULLs for the order columns. `COUNT(order_id)` counts non-NULL values, so Dan counts 0:

```relix
query { GROUP name, COUNT(order_id) -> orders (Customers LJOIN Customers.customer_id = Orders.customer_id Orders) };
```

```
 name   orders
 ─────  ──────
 Alice       2
 Bob         1
 Carol       1
 Dan         0
(4 rows)
```

`COUNT(*)` counts rows, and Dan's NULL-padded row is a row, so this is **wrong** for the question:

```relix
query { GROUP name, COUNT(*) -> orders (Customers LJOIN Customers.customer_id = Orders.customer_id Orders) };
```

```
 name   orders
 ─────  ──────
 Alice       2
 Bob         1
 Carol       1
 Dan         1
(4 rows)
```

### What does a GROUP over no rows return? (it depends on the keys)

With no grouping keys, `GROUP` always returns one row: a count of nothing is 0, and `SUM`, `AVG`, `MIN` and `MAX` of nothing are NULL (project the column through `Coalesce(refunded, 0)` for a zero). With grouping keys, there are no groups, so there are no rows.

```relix
query { GROUP COUNT(*) -> refunds, SUM(amount) -> refunded (SELECT status = 'refunded' (Orders)) };
query { GROUP customer_id, COUNT(*) -> refunds (SELECT status = 'refunded' (Orders)) };
```

```
 refunds  refunded
 ───────  ────────
       0  NULL
(1 row)
 customer_id  refunds
 ───────────  ───────
(0 rows)
```

### What are the three largest orders? (sort, then limit)

```relix
query { LIMIT 3 (SORT amount DESC (Orders)) };
```

```
 order_id  customer_id  amount  status
 ────────  ───────────  ──────  ─────────
        4            3     200  completed
        1            1     120  completed
        2            1      80  completed
(3 rows)
```

### What is each customer's largest order? (TOP … PER, not a GROUP)

`GROUP customer_id, MAX(amount)` would return the amount alone. `TOP … PER` returns the whole row.

```relix
query { TOP 1 amount DESC PER customer_id (Orders) };
```

```
 order_id  customer_id  amount  status
 ────────  ───────────  ──────  ─────────
        1            1     120  completed
        3            2      50  pending
        4            3     200  completed
(3 rows)
```

### Which columns does a condition join produce? (both sides, with the right side's clashes renamed)

```relix
query { Completed >< Completed.customer_id = Customers.customer_id Customers };
```

```
 order_id  customer_id  amount  status     customer_id_r  name   city
 ────────  ───────────  ──────  ─────────  ─────────────  ─────  ──────
        1            1     120  completed              1  Alice  London
        2            1      80  completed              1  Alice  London
        4            3     200  completed              3  Carol  London
(3 rows)
```

Where the join is on equal column names, `Completed JOIN Customers` says the same thing and keeps one `customer_id`.

### Mistakes the parser rejects

```relix-invalid
query { SELECT status IN ('open', 'held') (Orders) };
```

```relix-invalid
query { SELECT status <> 'open' (Orders) };
```

```relix-invalid
query { GROUP region (Orders) };
```

## Language

- [Grammar, as plain-text EBNF](https://relix.darkcollective.com/reference/language/grammar.ebnf.txt): the complete syntax of every statement, operator and token, held to the parser on every build. Read this first when generating anything non-trivial.
- [Grammar, with notes and examples](https://relix.darkcollective.com/reference/language/grammar.html): the same grammar with an example for each rule, and the rules EBNF cannot state.
- [Operator spellings](https://relix.darkcollective.com/reference/language/spellings.html): every glyph beside its ASCII keyword.
- [Coming from SQL](https://relix.darkcollective.com/sql.html): everyday SQL statements beside their Relix, each pair checked against a real database.
- [Getting started](https://relix.darkcollective.com/reference/getting-started.html): a first script, built up one statement at a time.
- [Views and queries](https://relix.darkcollective.com/reference/language/assignment.html): `:=` and `query`.
- [Inline tables](https://relix.darkcollective.com/reference/language/inline-table.html): the Markdown and CSV table forms.
- [Sources](https://relix.darkcollective.com/reference/language/source.html): CSV, JSON and database sources, and their schemas.
- [HTTP sources](https://relix.darkcollective.com/reference/language/http-source.html): reading JSON from a REST or GraphQL API.
- [Connections](https://relix.darkcollective.com/reference/language/connection.html): one database connection shared by many sources.
- [Functions](https://relix.darkcollective.com/reference/language/def.html): defining your own scalar function with `def`.

## Operators

- [Selection](https://relix.darkcollective.com/reference/operators/select.html), [projection](https://relix.darkcollective.com/reference/operators/project.html), [rename](https://relix.darkcollective.com/reference/operators/rename.html), [distinct](https://relix.darkcollective.com/reference/operators/distinct.html), [sort](https://relix.darkcollective.com/reference/operators/sort.html), [limit](https://relix.darkcollective.com/reference/operators/limit.html)
- [Grouping and aggregation](https://relix.darkcollective.com/reference/operators/group.html)
- [Natural join](https://relix.darkcollective.com/reference/joins/natural-join.html), [theta join](https://relix.darkcollective.com/reference/joins/theta-join.html), [left outer join](https://relix.darkcollective.com/reference/joins/left-outer-join.html), [semi join](https://relix.darkcollective.com/reference/joins/semi-join.html), [anti join](https://relix.darkcollective.com/reference/joins/anti-join.html)
- [Union](https://relix.darkcollective.com/reference/set-operations/union.html), [difference](https://relix.darkcollective.com/reference/set-operations/difference.html), [intersection](https://relix.darkcollective.com/reference/set-operations/intersection.html), [division](https://relix.darkcollective.com/reference/set-operations/division.html)
- [Top N per group](https://relix.darkcollective.com/reference/advanced/top.html)
- [Rolling aggregates](https://relix.darkcollective.com/reference/operators/rolling.html), [ranking](https://relix.darkcollective.com/reference/operators/window-ranking.html), [LAG and LEAD](https://relix.darkcollective.com/reference/operators/window-offset.html)
- [Unnest](https://relix.darkcollective.com/reference/operators/unnest.html) and [COLLECT](https://relix.darkcollective.com/reference/aggregates/collect.html), for nested arrays

## Optional

- [The whole language reference](https://relix.darkcollective.com/reference/index.html): one page per operator, predicate and function.
- [Beyond SQL](https://relix.darkcollective.com/beyond-sql.html): graph traversal, optimisation under constraints, lineage, sessions and time-aligned joins.
- [Transitive closure](https://relix.darkcollective.com/reference/advanced/closure.html), [paths](https://relix.darkcollective.com/reference/advanced/path.html) and [recursion](https://relix.darkcollective.com/reference/advanced/fix.html)
- [AS-OF join](https://relix.darkcollective.com/reference/joins/asof-join.html) and [sessionization](https://relix.darkcollective.com/reference/advanced/sessionize.html), for time series
- [Declarative optimisation](https://relix.darkcollective.com/reference/advanced/optimize.html): knapsack and allocation problems
- [The programming guide](https://relix.darkcollective.com/guide/index.html): running Relix from Java