Comparison to SQL

RhyDB queries have a pipeline syntax rather than SQL clauses.

Correspondence

SQL concept	RhyDB query-language operation
`FROM`	Start the pipeline with a table name.
`WHERE`	`.filter(condition)`
`SELECT` columns	`.project({columns})`
`SELECT *` except some columns	`.projectout({columns})`
computed column	`.map({name := expression})`
`GROUP BY`	`.group(by := {columns}, aggs := {aggregates})`
`HAVING`	`.filter(condition)` after `.group(…)`
`ORDER BY`	`.order(by := {keys})`
`LIMIT` / `OFFSET`	`.limit(n)` / `.offset(n)`
`JOIN`	`.join(otherPipeline, leftColumn = rightColumn)`
`UNION ALL`	`.unionall(otherPipeline)`
`INSERT INTO … SELECT`	`query.insertInto(table)`
`CREATE TABLE`	`createTable(table, {column := type, …})`

The same summary in both styles

-- SQL
SELECT country, COUNT(*) AS count
FROM data
WHERE date >= DATE '2024-01-01'
GROUP BY country
ORDER BY count DESC
LIMIT 10;

-- RhyDB query language
data
  .filter(date >= '2024-01-01'::date)
  .group(by := {country}, aggs := {count := count()})
  .order(by := {count.desc()})
  .limit(10)

Differences

Order matters: each method receives the result produced immediately before it.
Records and sets use braces, and := assigns a name inside a record.
The available tables and columns are configured per RhyDB instance.
Sequence predicates and aggregations understand reference coordinates, mutations, and ambiguity.
RhyDB currently provides count() and sum() as its aggregate functions; it is not a general SQL engine.
Missing values are tested with column.isNull() and column.isNotNull(); column = null is an error rather than the always-unknown comparison SQL allows.
! is a set complement rather than SQL’s NOT. Comparisons never match null cells, so country <> 'Germany' drops the rows whose country is null while !(country = 'Germany') keeps them.

●

Reading a query

Read a RhyDB query from top to bottom as a series of intermediate tables. Operations such as map, project, and group change the schema.