Coming from SQL

If you write SQL, you already know most of Relix: the same questions, asked with operators rather than clauses. This page puts the statements you write every day beside their Relix, so you can read one in terms of the other.

Every pair here is checked on each build. The SQL runs on a real database, the Relix runs in the engine, and the two must return the same rows.

One word to watch

In SQL, SELECT chooses columns. In Relix, SELECT chooses rows: it is the relational-algebra selection, the thing SQL calls WHERE. Choosing columns is PROJECT. Every other keyword means what you would guess.

Relix reads inside out. Each operator takes a relation in parentheses and returns one, so a query is a pipeline you read from the innermost name outwards. Every operator also has a symbol from relational algebra (σ for SELECT, π for PROJECT), and the examples here can be switched between the two.

The tables

The examples use two small tables, plus one lookup table for the classification example further down. An empty cell is NULL.

Customers

Orders

Tiers

A lookup table that names a size band by its lower bound: an amount belongs to the highest band whose threshold it reaches.

Filtering rows

WHERE is SELECT. The condition comes first, then the relation it filters.

SELECT * FROM orders WHERE status = 'OPEN' AND amount > 100

SELECT status = 'OPEN' AND amount > 100 (Orders)

σ status = 'OPEN' ∧ amount > 100 (Orders)

A list of values and a null test read the way you would expect.

SELECT * FROM orders WHERE status IN ('OPEN', 'HELD')

SELECT status IN ('OPEN', 'HELD') (Orders)

σ status ∈ ('OPEN', 'HELD') (Orders)

A list may also be written in braces, as a set: status IN {'OPEN', 'HELD'}. A list of one value has to be, status IN {'OPEN'}, since one value in parentheses is just that value.

SELECT * FROM customers WHERE city IS NULL

SELECT city IS NULL (Customers)

σ city IS NULL (Customers)

SQL's other predicate spellings are written as they are in SQL: <>, BETWEEN, LIKE, IS DISTINCT FROM, IS TRUE and IS NOT TRUE, and = ANY or > ALL over a list.

SELECT * FROM orders WHERE amount BETWEEN 50 AND 100 AND status <> 'HELD'

SELECT amount BETWEEN 50 AND 100 AND status <> 'HELD' (Orders)

σ amount BETWEEN 50 ∧ 100 ∧ status <> 'HELD' (Orders)

A row value compares several columns at once.

SELECT * FROM orders WHERE (customer_id, status) = (10, 'OPEN')

SELECT (customer_id, status) = (10, 'OPEN') (Orders)

σ (customer_id, status) = (10, 'OPEN') (Orders)

Choosing and computing columns

The column list is PROJECT. A computed column takes its name from ->, where SQL uses AS.

SELECT order_id, amount * 1.2 AS gross FROM orders

PROJECT order_id, amount * 1.2 -> gross (Orders)

π order_id, amount * 1.2 → gross (Orders)

CASE is written as SQL writes it, in both its searched and its simple form.

SELECT order_id, CASE WHEN amount >= 100 THEN 'large' ELSE 'small' END AS size FROM orders

PROJECT order_id, CASE WHEN amount >= 100 THEN 'large' ELSE 'small' END -> size (Orders)

π order_id, CASE WHEN amount ≥ 100 THEN 'large' ELSE 'small' END → size (Orders)

SELECT order_id,
       CASE status WHEN 'OPEN' THEN 'open' WHEN 'HELD' THEN 'held' ELSE 'closed' END AS state
FROM orders

PROJECT order_id,
        CASE status WHEN 'OPEN' THEN 'open' WHEN 'HELD' THEN 'held' ELSE 'closed' END -> state (Orders)

π order_id,
        CASE status WHEN 'OPEN' THEN 'open' WHEN 'HELD' THEN 'held' ELSE 'closed' END → state (Orders)

Most other functions answer to their SQL names, so COALESCE, NULLIF, UPPER, MD5, JSON_VALUE, CURRENT_TIMESTAMP and the || concatenation are written as you would write them in SQL.

SELECT name, COALESCE(city, 'unknown') AS city FROM customers

PROJECT name, COALESCE(city, "unknown") -> city (Customers)

π name, COALESCE(city, "unknown") → city (Customers)

SELECT name || ' (' || COALESCE(city, '-') || ')' AS label FROM customers

PROJECT name || " (" || COALESCE(city, "-") || ")" -> label (Customers)

π name || " (" || COALESCE(city, "-") || ")" → label (Customers)

A CASE is the Switch function underneath, and you will meet that spelling too: condition–value pairs, tried in order, and an optional last argument for the ELSE. IIf(cond, a, b) is the two-way form.

SELECT order_id,
       CASE WHEN amount >= 100 THEN 'large'
            WHEN amount >= 50  THEN 'medium'
            ELSE 'small' END AS size
FROM orders

PROJECT order_id, Switch(amount >= 100, "large", amount >= 50, "medium", "small") -> size (Orders)

π order_id, Switch(amount ≥ 100, "large", amount ≥ 50, "medium", "small") → size (Orders)

Where the branches are really a set of bands — tiers, grades, price brackets — the relational move is to make them data and join. The Tiers table above is those bands; an AS-OF join matches each order to the highest threshold at or below its amount, which is exactly what the searched CASE computes, one branch per band:

SELECT order_id,
       CASE WHEN amount >= 100 THEN 'large'
            WHEN amount >= 50  THEN 'medium'
            ELSE 'small' END AS size
FROM orders

PROJECT order_id, label -> size (
    Orders ASOF INNER Orders.amount >= Tiers.threshold Tiers
)

π order_id, label → size (
    Orders ASOF INNER Orders.amount ≥ Tiers.threshold Tiers
)

The bands now live in a table you can query, edit and reuse rather than in the text of one query, and adding a tier is a row, not another WHEN. If the bands instead carried both a lower and an upper edge (lo, hi), a theta join on the range (Orders >< Orders.amount >= Tiers.lo AND Orders.amount < Tiers.hi Tiers) would say the same thing; for bands over time, see the interval join.

Like SQL, a projection keeps duplicate rows. DISTINCT removes them.

SELECT DISTINCT status FROM orders

DISTINCT (PROJECT status (Orders))

δ (π status (Orders))

Sorting and limiting

ORDER BY is SORT, and LIMIT wraps the sorted relation.

SELECT * FROM orders ORDER BY amount DESC LIMIT 3

LIMIT 3 (SORT amount DESC (Orders))

λ 3 (τ amount DESC (Orders))

A page further down is LIMIT … OFFSET, or the standard's OFFSET … FETCH, which is the same query.

SELECT * FROM orders ORDER BY order_id LIMIT 2 OFFSET 3

LIMIT 2 OFFSET 3 (SORT order_id ASC (Orders))

λ 2 OFFSET 3 (τ order_id ASC (Orders))

SELECT * FROM orders ORDER BY order_id OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY

OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY (SORT order_id ASC (Orders))

OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY (τ order_id ASC (Orders))

Each sort key takes NULLS FIRST or NULLS LAST.

SELECT * FROM customers ORDER BY city NULLS FIRST, name

SORT city ASC NULLS FIRST, name ASC (Customers)

τ city ASC NULLS FIRST, name ASC (Customers)

WITH TIES keeps every row that ties with the last one kept, as SQL's FETCH FIRST … WITH TIES does.

SELECT * FROM orders ORDER BY status DESC FETCH FIRST 1 ROWS WITH TIES

LIMIT 1 WITH TIES (SORT status DESC (Orders))

λ 1 WITH TIES (τ status DESC (Orders))

Grouping

GROUP BY and the aggregates go in one operator: the grouping columns, then the aggregates. HAVING is an ordinary SELECT over the grouped result.

SELECT customer_id, SUM(amount) AS total, COUNT(*) AS orders
FROM orders
GROUP BY customer_id

GROUP customer_id, SUM(amount) -> total, COUNT(*) -> orders (Orders)

γ customer_id, SUM(amount) → total, COUNT(*) → orders (Orders)

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 150

SELECT total > 150 (GROUP customer_id, SUM(amount) -> total (Orders))

σ total > 150 (γ customer_id, SUM(amount) → total (Orders))

With no grouping columns, the aggregate is over the whole table.

SELECT COUNT(*) AS n, AVG(amount) AS average FROM orders

GROUP COUNT(*) -> n, AVG(amount) -> average (Orders)

γ COUNT(*) → n, AVG(amount) → average (Orders)

DISTINCT inside an aggregate and an aggregate's FILTER (WHERE …) are written as in SQL.

SELECT customer_id, COUNT(DISTINCT status) AS statuses,
       SUM(amount) FILTER (WHERE status = 'OPEN') AS open_total
FROM orders
GROUP BY customer_id

GROUP customer_id, COUNT(DISTINCT status) -> statuses,
      SUM(amount) FILTER (WHERE status = 'OPEN') -> open_total (Orders)

γ customer_id, COUNT(δ status) → statuses,
      SUM(amount) FILTER (WHERE status = 'OPEN') → open_total (Orders)

ROLLUP, CUBE and GROUPING SETS go where the grouping columns go, and add the subtotal rows SQL adds: GROUP ROLLUP(customer_id), SUM(amount) -> total (Orders) is GROUP BY ROLLUP (customer_id), with a grand-total row whose customer_id is NULL. The GROUP page covers all three, and GROUPING tells a subtotal row from a group.

Joining

JOIN on its own is a natural join: it matches on every column the two tables share, and keeps one copy of each.

SELECT order_id, name, amount FROM orders NATURAL JOIN customers

PROJECT order_id, name, amount (Orders JOIN Customers)

π order_id, name, amount (Orders ⋈ Customers)

USING names the columns to match on, as it does in SQL.

SELECT order_id, name, amount FROM orders JOIN customers USING (customer_id)

PROJECT order_id, name, amount (Orders JOIN Customers USING (customer_id))

π order_id, name, amount (Orders ⋈ Customers USING (customer_id))

A join on a condition is written ><, with the condition between the two relations. Other comparisons work as well as equality.

SELECT o.order_id, c.name
FROM orders o JOIN customers c ON o.customer_id = c.customer_id AND o.amount > 90

PROJECT order_id, name (
    Orders >< Orders.customer_id = Customers.customer_id AND Orders.amount > 90 Customers
)

π order_id, name (
    Orders ⨝ Orders.customer_id = Customers.customer_id ∧ Orders.amount > 90 Customers
)

A left join is |><: every customer, with the orders they have, and NULL where they have none.

SELECT c.name, o.order_id
FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id

PROJECT name, order_id (
    Customers |>< Customers.customer_id = Orders.customer_id Orders
)

π name, order_id (
    Customers ⟕ Customers.customer_id = Orders.customer_id Orders
)

RIGHT JOIN is ><| and FULL JOIN is |><|, and all three take USING in place of a condition.

Subqueries and existence

A subquery is a relation in parentheses, written where a value goes. Its value is the one column of its one row.

SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders)

SELECT amount > (GROUP AVG(amount) -> average (Orders)) (Orders)

σ amount > (γ AVG(amount) → average (Orders)) (Orders)

A subquery can read the row it is computed for. Name that row's columns with their relation, Customers.customer_id, and the subquery is worked out for each row. A name without its relation is always the subquery's own column, where SQL would look outwards for it.

SELECT c.name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders
FROM customers c

PROJECT name, (GROUP COUNT(*) -> n (SELECT customer_id = Customers.customer_id (Orders))) -> orders (Customers)

π name, (γ COUNT(*) → n (σ customer_id = Customers.customer_id (Orders))) → orders (Customers)

EXISTS, NOT EXISTS, IN, ANY and ALL take a subquery too, as they do in SQL.

SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)

SELECT EXISTS (SELECT customer_id = Customers.customer_id (Orders)) (Customers)

σ EXISTS (σ customer_id = Customers.customer_id (Orders)) (Customers)

SELECT * FROM orders WHERE amount > ALL (SELECT amount FROM orders WHERE customer_id = 10)

SELECT amount > ALL (PROJECT amount (SELECT customer_id = 10 (Orders))) (Orders)

σ amount > ALL (π amount (σ customer_id = 10 (Orders))) (Orders)

An existence test is also a join in its own right, and that is the idiomatic way to write one. SEMI keeps the rows that have a match, and ANTI keeps the rows that have none. Neither repeats a row for each match, and ANTI has no NOT IN null trap.

SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)

Customers SEMI Customers.customer_id = Orders.customer_id Orders

Customers ⋉ Customers.customer_id = Orders.customer_id Orders

SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)

Customers ANTI Customers.customer_id = Orders.customer_id Orders

Customers ▷ Customers.customer_id = Orders.customer_id Orders

Combining results

UNION, EXCEPT and INTERSECT work on whole relations, and like SQL's they remove duplicates. UALL is UNION ALL.

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders

PROJECT customer_id (Customers) EXCEPT PROJECT customer_id (Orders)

π customer_id (Customers) EXCEPT π customer_id (Orders)

SELECT customer_id FROM customers WHERE city = 'Leeds'
INTERSECT
SELECT customer_id FROM orders WHERE status = 'OPEN'

PROJECT customer_id (SELECT city = 'Leeds' (Customers))
    INTERSECT PROJECT customer_id (SELECT status = 'OPEN' (Orders))

π customer_id (σ city = 'Leeds' (Customers))
    INTERSECT π customer_id (σ status = 'OPEN' (Orders))

Window functions

The two window functions people write most have operators of their own. The top row per group is TOP … PER, which returns whole rows:

SELECT order_id, customer_id, status, amount
FROM (
    SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
    FROM orders o
) ranked
WHERE rn = 1

TOP 1 amount DESC PER customer_id (Orders)

A running total is ROLLING:

SELECT order_id, customer_id, status, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_id) AS running
FROM orders

ROLLING SUM(amount) OVER ALL ROWS SORT order_id ASC PER customer_id AS running (Orders)

ROLLING SUM(amount) OVER ALL ROWS τ order_id ASC PER customer_id AS running (Orders)

An aggregate over the whole partition is OVER PARTITION, and a frame is written as SQL writes it, ROWS BETWEEN 1 PRECEDING AND CURRENT ROW.

SELECT order_id, customer_id, status, amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders

ROLLING SUM(amount) OVER PARTITION PER customer_id AS customer_total (Orders)

Where Relix answers differently

Most SQL runs in Relix and returns what it returns on any database. The databases do not all agree with each other, though, and in a few places Relix gives the answer some of them give and not others. Each difference below is kept on purpose, and each has a spelling that matches the databases that answer otherwise.

Division never truncates. 7 / 2 is 3.5, as in MySQL and Oracle. PostgreSQL, SQL Server and SQLite give 3 for two integers. Relix has one exact number type, so there are no integer operands to divide. To drop the fraction as they do, write Fix, which rounds toward zero:

SELECT order_id, CAST(amount AS INTEGER) / 7 AS weeks FROM orders

PROJECT order_id, Fix(amount / 7) -> weeks (Orders)

π order_id, Fix(amount / 7) → weeks (Orders)

Strings compare case-sensitively, in code-point order, so 'Acme' = 'acme' is false and 'B' sorts before 'a'. MySQL and SQL Server compare without case by default. COLLATE NOCASE ignores case in one comparison, sort or grouping:

SELECT * FROM customers WHERE LOWER(name) = 'acme'

SELECT name COLLATE NOCASE = 'acme' (Customers)

σ name COLLATE NOCASE = 'acme' (Customers)

NULL sorts as the largest value: last in an ascending sort and first in a descending one, as in PostgreSQL and Oracle. MySQL, SQL Server and SQLite sort it as the smallest. Here a plain ascending sort puts the customer with no city last:

SELECT * FROM customers ORDER BY city NULLS LAST, name

SORT city ASC, name ASC (Customers)

τ city ASC, name ASC (Customers)

To put NULL first, as those databases do, write NULLS FIRST on the key, as in Sorting and limiting.

REGEXP_REPLACE replaces the first match, as PostgreSQL's does. MySQL and Oracle replace every match. Pass the flag 'g', or an occurrence of 0 after the start position, to replace them all:

SELECT name, REGEXP_REPLACE(name, '[aeiou]', '') AS consonants FROM customers

PROJECT name, REGEXP_REPLACE(name, '[aeiou]', '', 'g') -> consonants (Customers)

π name, REGEXP_REPLACE(name, '[aeiou]', '', 'g') → consonants (Customers)

A backslash in LIKE is not an escape. PostgreSQL and MySQL read '50\%' as a literal percent sign. Relix refuses the pattern and asks for an escape character to be named, as the standard does: amount_text LIKE '50!%' ESCAPE '!'.

AT TIME ZONE always turns an instant into a local reading. A timestamp in Relix is an instant, so ts AT TIME ZONE 'Europe/London' gives the time a London clock showed at that moment. PostgreSQL does that for a timestamptz. For a timestamp column it goes the other way, reading the value as a London wall-clock time and giving the instant it names. That direction is FROM_TZ(local_ts, 'Europe/London') here.

Where to go from here

The language reference has a page for each of these operators, and Beyond SQL covers the questions that have no SQL counterpart at all: routes through a graph, choices under a budget, lineage, sessions and time-aligned joins.