Manual
Language reference
One page per keyword, operator and built-in function, with worked examples.
This is the complete reference for the Relix relational-algebra language — one page per keyword, operator and built-in function. The pages are arranged below in a learning order: start with getting started, which runs a first script end to end, then work down through the statements of a .relix file and the core operators toward joins, set algebra, aggregation, nested data and the advanced operators. The glossary defines the vocabulary the rest of the manual assumes. Every operator has both a Unicode form (e.g. σ, ⋈, ∪) and an ASCII keyword form (e.g. SELECT, JOIN, UNION); the two are interchangeable and produce identical results. Each page gives the syntax, a plain-English description, real-world examples, and "See Also" links to related pages. The harder operators include a fully worked example with sample data and the expected result.
New to relational algebra? Read the sections in order. Already fluent? Jump straight to the operator you need from the tables below.
1. Getting started
New here? This is the whole language in one page: bring data in, filter and combine it, name the interesting steps, ask for a result. Everything below is detail on one of those four moves.
| Page | What it does |
|---|---|
| Getting started | Your first script — declare data, name a result, run it |
| Glossary | The vocabulary: relation, tuple, bag vs set, materialisation, pushdown |
| Operators by task | The operator for each task SQL has no operator for, named in a question's words |
2. Language & scripts
The shape of a .relix file — declaring data, naming results, and reusing code.
| Page | What it does |
|---|---|
| source | Declare an external data source (CSV, database, …) |
| http source | Read JSON from an HTTP/REST API (headers, auth, body) |
| connection | Share one database connection across many sources |
| mongodb source | Read a MongoDB collection as a relation |
| json source | Read a JSON file as a relation, with no declared columns |
| generator source | A generated series of rows: Range, Naturals, Primes |
| env | Name the environment file and profile a script's placeholders come from |
| gedcom source | Read a GEDCOM genealogy file as individuals and families |
| log source | Read a web server access log as a typed relation |
| relate | Declare named, bounded relationships between relations (schema graph) |
| inline-table | Embed small reference data directly in a script |
| assignment & query | Name a view with := and mark a result query |
| delimited identifier | Backtick a name that collides with a reserved word (`order`) |
| bound parameter | $name — a value supplied when the query runs, never written into it |
| comments | -- to end of line, /* … */ for a block |
| operator spellings | Every operator's Unicode glyph and its ASCII equivalent |
| grammar (EBNF) | The whole language as one EBNF grammar — every statement, operator and token |
| namespace | Declare the script's namespace |
| import | Reuse symbols defined in another file |
| def | Define a scalar (per-value) function |
| def … : RELATION | Define a table-valued (relation-returning) function |
| relation catalog | relix.relations — every relation the script can name, with its kind, size and whether it ends |
| column catalog | relix.columns — every column of every relation, with its type and distinct and NULL counts |
| logical plan catalog | relix.plan / relix.dependencies — every view's expression tree, and which view reads which relation, as rows |
| connection catalog | relix.connections — the declared connections, by name and dialect |
| introspection stdlib | relix.unused / relix.deps / relix.impact / relix.find / relix.schema / relix.cycles / relix.funcs — the engine's own introspection, shipped as Relix |
| observability feed | relix.events / relix.rules — the previous run's engine decisions, as queryable rows |
| component inventory | relix.version — the engine, the facade and every discovered provider, as queryable rows |
| candidate keys | relix.keys — the collected candidate keys of every relation, one row per key column |
| reserved namespace | relix.catalog — the relix.* surface describing itself |
3. Core operators
The everyday relational-algebra operators — the ones you reach for first.
| Operator | Unicode / ASCII | What it does |
|---|---|---|
| Selection | σ / SELECT | Keep rows that match a condition |
| Projection | π / PROJECT | Pick, reorder, rename or compute columns |
| Rename | ρ / RENAME | Rename a relation or its columns |
| Distinct | δ / DISTINCT | Remove duplicate rows |
| Sort | τ / SORT / ORDER [BY] | Order rows by one or more keys |
| Shuffle | SHUFFLE | Return every row once, in a uniformly random order |
| Roll | ROLL | Endless uniform draws with replacement from a finite face set (a die roll) |
| Limit | λ / LIMIT | Take the first N rows (with optional offset) |
4. Predicates
The conditions that go inside a σ (selection) or join.
| Page | Unicode / ASCII | What it does |
|---|---|---|
| Comparison | = ≠ < ≤ > ≥ | Compare two values |
| Range test | BETWEEN, NOT BETWEEN | Value within a range, both ends included |
| Null-safe comparison | IS [NOT] DISTINCT FROM | Compare two values, NULL equal to NULL |
| Truth test | IS [NOT] TRUE / FALSE / UNKNOWN | Which of its three values a boolean has, never UNKNOWN |
| Row value comparison | (a, b) = (c, d), (a, b) IN {…} | Compare several columns as one, ordering lexicographically |
| Quantified comparison | ANY / SOME / ALL | Compare a value with each value of a list or a one-column relation |
| EXISTS | EXISTS (…), NOT EXISTS (…) | Whether a relation, often about the row tested, has a row |
| And | ∧ / AND | Both conditions hold |
| Or | ∨ / OR | Either condition holds |
| Not | ¬ / NOT | Negate a condition |
| Null test | ⊥ / NULL / IS [NOT] NULL | Test for a missing value |
| Set membership | ∈ / IN, ∉ / NOT IN | Test membership in a set or a one-column relation |
| Pattern match | LIKE, NOT LIKE, ESCAPE | Wildcard string match (% = any run, _ = any one char) |
| Regex match | ~ / MATCHES | Does a regular expression match (~* ignores case) |
5. Joins
Combining two relations. The outer joins keep unmatched rows (with NULLs); the semi/anti joins filter the left relation by the existence of a match.
| Join | Unicode / ASCII | What it does |
|---|---|---|
| Natural join | ⋈ / JOIN | Match on shared column names |
| USING join | ⋈ USING / JOIN … USING | Match on the named shared columns only — inner, left, right or full |
| Theta join | ⨝ / >< | Match on an explicit condition |
| Left outer join | ⟕ / |>< / LJOIN | Keep all left rows |
| Right outer join | ⟖ / ><| / RJOIN | Keep all right rows |
| Full outer join | ⟗ / |><| / FJOIN | Keep all rows from both sides |
| Semi join | ⋉ / SEMI | Left rows that have a match |
| Anti join | ▷ / ANTI | Left rows that have no match |
| Universal semi join | USEMI | Left rows that match every right row |
| AS-OF join | ASOF | Match each row to the nearest-in-time row |
| Interval join | IJOIN | Match intervals by an Allen relation (OVERLAPS, DURING, …) |
| Lateral join | LATERAL, OUTER LATERAL | Evaluate a TVF call or a relation per left row, reading that row's columns |
6. Set operations
Treating relations as sets of rows.
| Operation | Unicode / ASCII | What it does |
|---|---|---|
| Union | ∪ / UNION | Rows in either (deduplicated) |
| Union all | ⊎ / UALL | Rows in either (keeps duplicates) |
| Outer-union | ⊔ / OUNION | Merge different-schema relations (align common columns, NULL-pad the rest) |
| Intersection | ∩ / INTER / INTERSECT | Rows in both |
| Difference | − / DIFF / MINUS / EXCEPT | Rows in the first but not the second |
| Symmetric difference | ∆ / SYMDIFF | Rows in exactly one |
| Cartesian product | × / CROSS | Every combination of rows |
| Division | ÷ / DIV | Which X relate to all of the Y |
| Composition | ∘ / COMPOSE | Chain on a shared column and drop it |
7. Grouping & aggregation
Collapse many rows into summary rows with γ (GROUP) and aggregate functions.
| Page | What it does |
|---|---|
| Group / Aggregation | γ / GROUP [BY] — group rows and aggregate |
| SUM | Total of a numeric expression |
| AVG | Mean of a numeric expression |
| COUNT | Rows (COUNT(*)) or non-NULL values (COUNT(expr)) |
| MIN | Smallest value |
| MAX | Largest value |
| COLLECT | Gather a group's values into an array (NEST) |
| ARGMAX | A value from the row with the maximum |
| ARGMIN | A value from the row with the minimum |
| STDDEV | Standard deviation, of a sample |
| STDDEV_SAMP | Sample standard deviation |
| STDDEV_POP | Population standard deviation |
| VARIANCE | Variance, of a sample |
| VAR_SAMP | Sample variance |
| VAR_POP | Population variance |
| MEDIAN | The middle value |
| PERCENTILE_CONT | The value a fraction of the group lies below, interpolated |
| PERCENTILE_DISC | The first value a fraction of the group reaches |
| STRING_AGG | Join a group's values into one string (GROUP_CONCAT, LISTAGG) |
| BOOL_AND | Whether every value in a group is true (EVERY) |
| BOOL_OR | Whether any value in a group is true |
| MODE | The most frequent value, a tie going to the smallest (STATS_MODE) |
| FILTER | AGG(x) FILTER (WHERE c) — aggregate only the rows where a condition holds |
| GROUPING | Which keys a ROLLUP, CUBE or GROUPING SETS row rolled up (GROUPING_ID) |
| Window / Rolling | ROLLING … OVER … ROWS, ROWS/RANGE BETWEEN, PARTITION — sliding, cumulative, framed or whole-partition aggregate added as a column (no row collapse) |
| Window / Ranking | WINDOW ROW_NUMBER/RANK/DENSE_RANK/PERCENT_RANK/CUME_DIST/NTILE — per-partition rank column added to every row |
| Window / Offset | WINDOW LAG/LEAD/FIRST_VALUE/LAST_VALUE/NTH_VALUE — adjacent / boundary row value added as a column (no row collapse) |
8. Nested data (NF²)
Relix relations can hold structs and arrays. These operators build and flatten nested values.
| Page | What it does |
|---|---|
| Struct construction | Build a nested object { … } in a projection |
| Array construction | Build an array [ … ] in a projection |
| COLLECT | Aggregate a group's values into an array (NEST) |
| Unnest | μ / UNNEST — expand an array into rows (the inverse of COLLECT) |
| WITH ORDINALITY | Add a 1-based position column to an unnest |
| Unpivot | UNPIVOT — fold named columns into rows (wide → long) |
| Pivot | PIVOT … BY … PER — turn distinct key values into columns (long → wide) |
| Tree | TREE key BY parentKey [ORDER …] AS children — fold an adjacency relation into nested documents (recursive COLLECT) |
9. Literals & temporal types
Writing constant values, including the first-class temporal types.
| Page | What it does |
|---|---|
| String, number and boolean literals | "text" or 'text', 42, 3.25, 1e6, TRUE / FALSE |
| CASE | CASE WHEN c THEN a … ELSE d END, CASE x WHEN v THEN a … END — a value chosen by conditions |
| Subquery | (γ COUNT(*) → n (σ k = R.k (S))) — a relation's one value, per row when it reads the row |
| COLLATE | s COLLATE NOCASE, s COLLATE 'en-US' — compare, sort and group strings ignoring case or by a language's order |
| CAST | CAST(x AS type) — convert a value to NUMBER, STRING, BOOLEAN, DATE, TIME or TIMESTAMP |
| AT TIME ZONE | ts AT TIME ZONE 'Europe/London' — the wall-clock time in a zone |
| EXTRACT | EXTRACT(field FROM d) — a field of a date or time, day of week and ISO week among them |
| Arithmetic and concatenation | + - * /, SQL's % and || |
| DATE literal | DATE '2026-01-31' |
| TIME literal | TIME '09:30:00' |
| TIMESTAMP literal | TIMESTAMP '2026-01-31T09:30:00Z' |
| TIMESTAMPTZ literal | TIMESTAMPTZ '2026-01-31T09:30:00+02:00', which keeps its offset |
| DURATION literal | DURATION 'PT90M', DURATION 'P1M', INTERVAL '1 month' |
| Truth relations | UNIT / EMPTY (aliases DEE / DUM) — the two zero-column relations |
10. Built-in functions
Scalar functions usable in any expression (projection, selection, aggregate argument). An argument that must be a whole number (a length, a position, a character code, a count of units) has its fraction cut off toward zero, so Left(s, 1.9) is Left(s, 1); Round and CInt are the functions that round.
11. Advanced operators
Beyond standard SQL — recursion, per-group ranking, sampling, the declarative solver, combinatorial covering, and universal quantification. Each of these pages includes a fully worked example.
| Page | What it does |
|---|---|
| Universal quantification | ∀ / FORALL — groups where every row satisfies a condition |
| Top-K per group | TOP … PER — the N highest rows in each group |
| Transitive closure | CLOSURE / RCLOSURE — reachability over a binary relation |
| Connected components | CLUSTER … AS — label each node with its undirected component id |
| Record linkage | similarity join → TOP 1 … PER → CLUSTER — find the rows that probably describe the same thing |
| Bounded path reachability | PATH … HOPS m TO n AS — pairs reachable within a hop window, with shortest distance |
| Optimal-path extraction | TRACE … VIA … MINIMIZE|MAXIMIZE AS — cheapest/longest path with route array |
| General recursion | FIX — least-fixpoint recursion (WITH RECURSIVE) |
| Iteration | ITERATE — repeat a step until it settles: PageRank, state machines, convergence |
| Goal-seek | SOLVE — fill the one blank in an equation, per row |
| Declarative optimisation | OPTIMIZE — knapsack / allocation under constraints |
| Combinatorial covering | COVER — minimal all-pairs (pairwise) subset |
| Bernoulli sampling | SAMPLE p — keep each row with probability p |
| Reservoir sampling | SAMPLE n ROWS — an exact-count uniform sample |
| DOWNSAMPLE | DOWNSAMPLE ts BY '5m' USING AVG — time-series bucket aggregation |
| Sessionization | SESSIONIZE ts GAP DURATION 'PT30M' PER … AS — split an ordered stream into sessions by an idle gap |
| Why (lineage provenance) | ω / WHY — reify a tuple's lineage (which source rows produced it) as a queryable nested column |
| Query optimizer | The logical rewrite rules (SEL-…, LIM-…) and how to see which fired |
| Pushdown | What the backend computes and what the engine does — the boundary relix explain draws |