Why any of this is possible
In Relix, every operation takes tables in and gives a table back. Without exception.
That sounds like a technicality. It is the whole reason the rest of this document exists.
SQL grew its advanced features as clauses bolted onto SELECT. GROUP BY, window functions, PIVOT, TABLESAMPLE, WITH RECURSIVE — each has its own rules about where in the statement it may appear, what may follow it, and what it may be nested inside. So you cannot freely feed one into another. You wrap it in a subquery, then another, and the statement grows outward until it is only readable by the person who wrote it, and only on the day they wrote it.
Relix has no statement shape to bolt onto. A ranking is a thing you apply to a table; so is a graph traversal, so is solving an optimisation problem. Each hands back an ordinary table that anything else will accept. New capabilities compose with the old ones for free — which is why the list below can be as long as it is.
Part one
Things SQL can do, but makes you suffer for
Nothing here is impossible in SQL. Each has a known idiom, and each idiom is something you look up every time, get subtly wrong at least once, and cannot explain to the person reviewing it. That cost is real: it is why these queries get written badly, or not at all.
ARGMAX · TOP … PER
The biggest order for each customer
Not the biggest order overall — the biggest one per customer. Or the most recent reading per sensor, the top earner per department, the latest status per ticket. It is probably the single most common thing people find unreasonably hard in SQL.
In SQL
Either a window function whose result you then have to filter in an outer query, or the “join back to a MAX subquery” dance — which quietly returns two rows when there is a tie.
In Relix
TOP 1 amount DESC PER customer (Orders)
GROUP customer, ARGMAX(amount, order_id) -> biggest (Orders)
Two shapes, because there are two questions.
TOP keeps whole rows — you get the winning order itself, with all its columns. TOP 3 gives a per-customer leaderboard, and TOP 1, 2 skips the winner and returns second and third place.
ARGMAX is for when you want one value from the winning row rather than the row. Read ARGMAX(amount, order_id) as “rank the group by amount, then give me the order_id of whichever row won”. Against the three orders below it returns Alice's order 3 and Bob's order 5 — the ids, not the amounts:
Input
customer order_id amount
Alice 1 120
Alice 3 200
Bob 5 75
Result
customer biggest
Alice 3
Bob 5
The two arguments are what makes it useful: the column you rank by and the column you want back are allowed to differ. “Which sales rep closed our largest deal”, “what was the status at the most recent reading” — that is one aggregate, not a self-join.
ROLLING · WINDOW
Running totals and rankings, without collapsing the rows
A seven-day moving average of daily sales. A running balance down a statement. Each product's rank within its category. Each order's gap since that customer's previous one. What these share is that you want a figure computed across a group of rows, added to every row, with nothing collapsed.
In SQL
This is what window functions are for, and they are SQL's most powerful feature and its least readable: OVER (PARTITION BY … ORDER BY … ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Powerful, genuinely hard to read, and you cannot filter on the result without wrapping the whole thing in another query, because a WHERE clause runs before the window does.
In Relix
ROLLING AVG(price) OVER 3 ROWS
SORT trade_time ASC PER ticker AS avg3 (Ticks)
WINDOW RANK() SORT price DESC PER ticker AS rnk (Ticks)
Split into two operators, because there were always two ideas wearing one syntax. ROLLING is an aggregate over a moving or cumulative span of rows — averages, running sums. WINDOW covers ranking and neighbour-peeking: RANK, ROW_NUMBER, NTILE, and LAG/LEAD for “compare this row to the one before it”.
The filtering problem disappears rather than being solved: the result is a table, so filtering it is the next thing you write, not an outer query you have to wrap around it. Where the underlying database supports window functions, Relix hands the work to it.
SESSIONIZE
Grouping a stream of events into sessions
A visitor's clicks become browsing sessions when there is a half-hour gap. A machine's telemetry becomes bursts of activity separated by idle time. A card's transactions become shopping trips. The rule is always the same: start a new group whenever the gap since the previous event exceeds some threshold.
In SQL
The “gaps and islands” problem — famous enough to be interview material. Three or four chained CTEs: LAG to get the previous timestamp, a comparison to flag boundaries, a running sum over the flags to number the groups.
In Relix
SESSIONIZE ts GAP DURATION 'PT30M'
PER user_id AS session (Events)
Every row survives; a session column is added. Because nothing was collapsed, you can carry straight on — filter to sessions longer than five events, join sessions to outcomes, whatever comes next.
DOWNSAMPLE
Bucketing a time series down to something you can plot
A month of per-second readings from a thousand sensors is tens of millions of rows and an unplottable chart. You want it per hour, per host, averaged — and often you want a fixed number of buckets, because the chart is 400 pixels wide regardless of how long a period the user picked.
In SQL
A GROUP BY over a truncated timestamp, where the truncation is spelled date_trunc, DATE_FORMAT, or a floor-division on an epoch depending on the vendor. Getting a fixed number of buckets rather than a fixed width means computing the width first, in a separate round trip.
In Relix
DOWNSAMPLE ts BY '1h' USING AVG PER host (Metrics)
DOWNSAMPLE ts BY '1h' USING AVG PER host FOR 24 ROWS (Metrics)
The timestamp column is replaced by a bucket column holding the start of each interval. The second form is the one that is genuinely awkward elsewhere: FOR 24 ROWS asks for twenty-four buckets and lets the engine work out how wide they need to be — which is what a dashboard actually wants, and what makes the same query correct for a one-hour window and a one-year one.
FORALL · division
“Every one of them”, as a question you can actually write
Which delivery routes had every stop completed on time? Which candidates hold all the required certifications? Which customers have bought every product in the range? These are ordinary business questions and they are notoriously awful in SQL.
In SQL
A double negative: NOT EXISTS (… NOT EXISTS …) — “there is no required item for which there is no matching purchase”. Correct, and essentially unreadable. There is no FOR ALL in SQL, so this is the only route.
In Relix
FORALL route : on_time = 'Y' (Stops)
There is a matching operator for the set-membership form of the same idea — “customers who bought every product in this list” — and a comparison form, for questions like “which of our products undercut every competitor's price”. Each is one line; each is the double-negative subquery you would otherwise be reading in review.
SEMI · ANTI
Filtering by whether a match exists — without the NULL trap
Customers who have placed an order. Accounts with no activity this quarter. You want to filter one table by whether something exists in another, without pulling in the other table's columns or accidentally duplicating rows when there are several matches.
In SQL
EXISTS and NOT EXISTS are correct. NOT IN reads better and carries a trap that has cost real money: if the subquery returns even one NULL, the whole condition becomes unknown and the query returns nothing, silently, with no error.
In Relix
Customers SEMI (Customers.cid = Orders.cid) Orders
Customers ANTI (Customers.cid = Orders.cid) Orders
These are joins that keep the left rows and discard the right side's columns — “has a match” and “has no match”. Because they are operators rather than a subquery inside a condition, the trap has nowhere to live: there is no NOT IN to reach for by mistake.
PIVOT · UNPIVOT
Turning rows into columns, and back
A finance table arrives with twelve monthly columns and you need one row per month. Or the reverse: you have region-by-quarter rows and someone wants a quarter across the top. It is the most mundane reshaping task there is, and it is where SQL is at its least portable.
In SQL
PIVOT exists in some vendors, spelled differently, absent in others. Going the other way usually means one SELECT per column glued together with UNION ALL — twelve of them for twelve months.
In Relix
PIVOT amount BY quarter PER region (Sales)
UNPIVOT (jan, feb, mar) AS (month, value) (Wide)
One caveat worth knowing rather than discovering: a pivot's output columns depend on the data, so the engine cannot know the shape until it runs. Relix keeps that uncertainty contained rather than letting it leak into everything downstream.
SYMDIFF · COMPOSE
Reconciling two systems
Two extracts of the same customer list, and you want the disagreements — records in one and not the other, in either direction. This is a nightly reconciliation job in most organisations.
In SQL
There is no symmetric difference, so you write (A EXCEPT B) UNION (B EXCEPT A) and mention both tables twice. Easy to get right; easy also to edit one half and forget the other.
A companion operator, COMPOSE, chains two relationships through their shared column and drops the middle: “employees to departments” composed with “departments to buildings” gives you employees to buildings directly. It is a join and a projection, named for what it is actually for.
LATERAL · table functions
A query you can name, and call with arguments
“The orders for a customer” is a query you have written thirty times with a different customer id each time. What you want is to define it once, give it a name and a parameter, and then run it for every row of another table — the customer's own id going in as the argument.
In SQL
A view cannot take an argument. Vendor table-valued functions can, but they are a separate database object created outside the query, in a procedural dialect that differs everywhere. Calling one per row needs LATERAL or CROSS APPLY, which not every engine has.
In Relix
def ordersFor(cid: NUMBER): RELATION :=
{ SELECT customer_id = cid (Orders) };
Customers LATERAL ordersFor(customer_id)
A named, parameterised query is an ordinary definition in the same file, not a database object someone has to deploy. LATERAL runs it once per left row with that row's values as the arguments — which is how you express “for each customer, their three most recent orders” without a window function.
When the engine can see that the arguments do not actually depend on the left row, it quietly stops calling it per row.
SAMPLE · SEED
A sample you can reproduce tomorrow
Ten percent of a large table, to develop against. Or exactly five hundred rows, chosen uniformly, for a manual quality review. And crucially: the same rows when a colleague runs it, or when you re-run it next week to check whether a fix worked.
In SQL
TABLESAMPLE exists in some vendors, attaches only to a table rather than to a result, and takes a seed in some dialects and not others. The fixed-count version is usually ORDER BY RANDOM() LIMIT n, which sorts the entire table to take five hundred rows from it.
In Relix
SAMPLE 0.1 SEED 42 (Orders)
SAMPLE 500 ROWS SEED 42 (SELECT region = 'north' (Orders))
Both forms apply to any table-shaped thing, including the result of the filter and joins you just wrote — not only to a stored table. The fixed-count form makes a single pass and holds only the rows it is keeping, rather than sorting everything. SEED makes the result reproducible; leave it out and you get a fresh draw each run.
Part two
Things SQL sends you somewhere else for
Below this line, some of these have no query to compare against at all. The rest have one that teams route around — a recursive CTE nobody wants to own, or a different spelling per vendor. Either way the job ends up leaving the database: exported to a spreadsheet, a Python script, or a specialist tool, and coming back, if it comes back, as a number nobody can trace.
OPTIMIZE
Deciding, not just reporting
You have a maintenance backlog and a budget per region: which repairs should you fund to get the most value? You have a shortlist of assets and a mandate: how should the fund be split across them? Databases report on decisions after they are made. They do not make them.
In SQL
Not expressible as a query. A procedural language or a MADlib-style extension can put a solver inside the database; in practice the data is exported, an analyst runs a solver in Excel or Python, and the answer is pasted back — at which point the query that produced the inputs and the decision made from them are two different artefacts that drift apart.
In Relix
OPTIMIZE MAXIMIZE SUM(value)
SUBJECT TO SUM(cost) <= 50000
PER region (Backlog)
The rows that come back are the repairs to fund. A second mode splits a total rather than picking a subset — each row gets a weight between two bounds, with the weights constrained to sum to one, which is the portfolio-allocation shape. Both run inside the query, so the inputs can be a join across three systems and the decision stays attached to the data that justified it.
SOLVE
Filling in the missing figure
A pricing sheet where some rows have the total and the rate but no principal, and others have the principal and rate but no total. In a spreadsheet you would reach for goal-seek, once, by hand, per row.
In SQL
You would write a CASE per blank column, with the arithmetic rearranged by hand in each branch — three ways of writing the same equation, which must all be updated together when the formula changes.
In Relix
SOLVE total = principal * rate (Loans)
You state the relationship once. Whichever column is blank in a given row is the one the engine works out, rearranging the arithmetic itself — row by row, so different rows can have different gaps.
WHY · provenance
Where did this number come from?
An auditor points at one row of a report and asks which source records produced it. Or a figure looks wrong and you need to know what fed it, through five joins and two aggregations. Today the answer is reconstructed by hand, and the reconstruction is a guess.
In SQL
Nothing. A database can tell you which tables a query touched. It cannot tell you which rows in them combined to produce a specific row of the answer.
In Relix
WHY (SELECT region = 'north' (Orders JOIN Customers))
Every output row comes back with an extra column listing the exact input rows behind it, and how they combined. It is a column like any other, so you can filter and join on it — “show me every figure in this report that depended on the ledger we now know was wrong” is a query.
The same machinery answers a family of related questions by swapping one setting: how many distinct ways a result could have been derived, the lowest clearance level required to see it, or the cheapest path that produced it. That generality is not a coincidence — it comes from the same closure property this document opened with.
CLOSURE · FIX · PATH
Following a chain of links to the end
Which parts, at any depth, contain this component? Who ultimately reports to this director? If we change this table, what downstream reports break? The link is one hop in the data; the question is about arbitrarily many.
In SQL
WITH RECURSIVE, which does exist — and is verbose, subtly easy to get wrong, and loops forever on cyclic data unless you remember the guard. Most teams have exactly one person who will write one.
In Relix
CLOSURE part, contains (BillOfMaterials)
PATH src, dst HOPS 1 TO 3 AS depth (Edges)
CLOSURE follows the chain as far as it goes. PATH is the bounded version — “everything within three hops”, stamped with how many hops it took, which is what you want for “second-degree connections” or blast-radius questions. A general form exists for recursion that is not simply link-following, with a round limit so cyclic data stops rather than hangs.
TRACE
The cheapest route — and the route itself
Not just “can I get from A to B and what does it cost”, but “which legs did I take”. Multi-leg shipping, network paths, currency conversion chains, transfer routes between accounts.
In SQL
A recursive CTE can accumulate a cost. Carrying the route along with it means string-concatenating a path column as you go and hoping nobody's identifier contains your separator.
In Relix
TRACE origin, dest VIA cost MINIMIZE
AS route (Flights)
You get the cheapest total and the sequence of legs that achieved it, as data rather than as a string you have to parse back apart.
CLUSTER
Working out who is connected to whom
Accounts linked by a shared device or address form a ring. Servers linked by traffic form a segment. Records linked by fuzzy matches form one real customer. Nobody labelled these groups; they exist only as a consequence of the links.
In SQL
Connected components — a recursive CTE per group, or an iterative job run outside the database until the labels stop changing. Usually the latter, on a schedule, in a separate system.
In Relix
CLUSTER account_a, account_b AS ring (SharedDevices)
Each row gets the id of the group it landed in. Count rows per group and you have ring sizes; join back to balances and you have exposure per ring — in the same query, because a table came back.
ASOF
The rate that was in effect at the time
Each transaction needs the exchange rate that applied at that moment — not today's rate, and not an exact timestamp match, because the rate table only changes when the rate changes. Same shape: the price list version live when the order was placed, the config in force when the alert fired, the quote standing when the trade executed.
In SQL
A correlated subquery per row taking the most recent rate at or before the transaction time, or a window-function ranking followed by a filter. Both are slow, and both are wrong in a way that only shows up at boundaries.
In Relix
Trades ASOF (Trades.sym = Quotes.sym
AND Trades.at >= Quotes.at) Quotes
Read it as a join whose matching rule is “nearest at or before”, rather than “equal”. You can bound how stale a match may be — “only if the quote is less than five minutes old” — which is the part that is genuinely painful to hand-write. Where the underlying database can do this itself, Relix pushes the work down to it instead of pulling the rows over.
IJOIN
Which periods overlapped which
Which staff shifts were running during each incident? Which insurance policies were in force during the claim window? Which deployments overlapped the outage? Everything here has a start and an end, and the question is about how two spans relate.
In SQL
Hand-written endpoint comparisons. “Overlaps”, “contains”, “meets” and “starts” are four different sets of inequalities, and getting the boundary conditions right — does touching count as overlapping? — is where the bugs live.
In Relix
Shifts IJOIN OVERLAPS (s_start, s_end,
i_start, i_end) Incidents
Thirteen named relationships between two time spans, each with the boundary rules settled once and correctly. You say which relationship you mean; you never write the inequalities.
COVER
Generating a test plan that is small but complete
Six browsers, four operating systems, five locales, three payment methods. Testing every combination is 360 runs. Testing a handful is a gamble. What you actually want is the smallest set of configurations in which every pair of settings appears together at least once — the standard, and effective, approach to combinatorial testing.
In SQL
Not a query. This is what a dedicated test-generation tool does, fed by hand from a list of parameters that has already drifted from the real ones.
In Relix
COVER 2 (Browsers × Systems × Locales)
The parameters are tables, so they can be the real ones — the live list of supported locales, the current price tiers, the tenants actually on the platform. That is the part a standalone tool cannot do: its inputs are a file someone maintains, and this one's inputs are the system of record.
COLLECT · UNNEST · TREE
Documents, not just flat rows
Real data has depth. An order has line items; a customer has addresses; a JSON payload from an API has whatever shape it has. Flattening it into rows to query it, then reassembling it to hand it on, is work that produces nothing.
In SQL
Bolted on late and differently by each vendor — json_agg here, ARRAY_AGG there, UNNEST or LATERAL FLATTEN or OPENJSON to go the other way. They are not treated as opposites, so round-tripping does not reliably return what you started with.
In Relix
TREE id BY parent_id AS children (Parts)
Gathering values into a nested column and unpacking them again are exact opposites here, and the engine knows it — do both and it removes the pair rather than doing the work. TREE is the same idea taken to arbitrary depth: give it a table where each row names its parent and it returns a properly nested document — a bill of materials, an org chart, a threaded comment tree — in one step.
OUNION
Merging sources that don't quite match
Three acquired companies, three customer tables, overlapping but not identical columns. You want one list, aligning what lines up and leaving blanks where it does not.
In SQL
UNION requires both sides to have the same number of columns in the same order and types. So you hand-write a select list per source padded with NULL AS missing_column, and repeat that whenever a source changes.
In Relix
LegacyCustomers OUNION AcquiredCustomers
Columns are matched by name, the rest are filled with blanks, and the result's shape is the union of both. It is the operator for the situation you are actually in, rather than the one you would be in if the data were tidy.
relix.*
Asking the engine questions about itself
“If I change this table, what breaks?” is the question every migration starts with, and the one nobody can answer confidently. Relix publishes its own knowledge — relations, columns, dependencies, functions, even the internal shape of each query — as ordinary tables.
In SQL
information_schema gives you a catalogue, but it is a flat listing. Turning “A depends on B” into “everything that ultimately depends on B” is the recursive query you were avoiding two sections ago.
In Relix
CLOSURE dependent, depends_on (relix.dependencies)
That single line is the argument of this whole document in miniature. The catalogue is a table, chain-following is an operator that takes a table, so the impact analysis is the two of them next to each other. Nothing had to be built to make it work; it followed from both halves already existing.
federation
And all of it, across systems at once
A Postgres database, a MySQL replica, a folder of CSVs, a MongoDB collection, and a JSON endpoint. In Relix these are all just relations, and everything above applies to a query spanning them.
In SQL
A query belongs to one database. Crossing that boundary means a pipeline that copies data somewhere central first — at which point the answer is as fresh as the last load, and the copy is a system to own.
In Relix
Sources are declared once; a query names them as if they were tables side by side.
This is what makes the earlier sections matter more than they first appear. Optimising a budget across one table is convenient. Optimising it across a candidate set assembled from a warehouse, a vendor API and a spreadsheet — with the lineage of every input still attached — is not something you had a way to do. Where a source can do part of the work itself, the engine hands it over rather than dragging the rows across; where it cannot, the engine does it.
