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. An empty cell is NULL.

Customers

Orders

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)

SELECT * FROM customers WHERE city IS NULL

SELECT city IS NULL (Customers)

σ city IS NULL (Customers)

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 the IIf function, and COALESCE is Coalesce.

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

PROJECT order_id, IIf(amount >= 100, "large", "small") -> size (Orders)

π order_id, IIf(amount ≥ 100, "large", "small") → size (Orders)

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

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

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

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))

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)

Joining

JOIN on its own is a natural join: it matches on the columns the two tables share, which is what USING does in SQL, and keeps one copy of each.

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

PROJECT order_id, name, amount (Orders JOIN Customers)

π order_id, name, amount (Orders ⋈ Customers)

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
)

Existence

EXISTS and NOT EXISTS are joins in their own right. 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)

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.