Language reference
Find an operator
Every operator, predicate and function, with the glyph and keyword forms that reach it. These are the same names the engine's own documentation lookup resolves.
Operators
- σSelection
SELECTFilter rows by a conditionσselectselectionwherehaving - πProjection
PROJECTKeep or rename a subset of columnsπprojectprojectionselect listselect *fromcolumn alias - ρRename
RENAMERename a relation or its columnsρrenamealiasastable aliasself joinself-join - γAggregation
GROUPGroup rows and aggregateγgroupgroupbyaggregationgroup by - τSort
SORTSort rows by one or more columnsτsortordernulls firstnulls lastnull orderingorder by - Shuffle
SHUFFLEReturn every row once, in a uniformly random ordershuffle - Roll
ROLLEndless uniform draws with replacement from a finite face set (a die roll)rolldiedice - λLimit
LIMITRestrict row count (TOP N / OFFSET)λlimitwith tiesfetch firsttiesfetch nextrownum - δDistinct
DISTINCTEliminate duplicate rowsδdistinctselect distinct - μUnnest
UNNESTExpand an array column to rowsμunnest - ∀Universal
FORALLUniversal quantification (∀ / FORALL)∀forall - Rolling
ROLLINGSliding / cumulative / framed window aggregationrollingwindow framerows betweenrange betweenover partitionmoving average - Window RankingROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK
window rankwindow-rankingrow_numberrankdense_rankpercent_rankntilecume_dist - Window OffsetLAG, LEAD, FIRST_VALUE, LAST_VALUE
window lagwindow-offsetlagleadfirst_valuelast_valuenth_valueprevious rownext rowpriorreplacedjust beforethe one after - Pivot
PIVOTRotate rows to columnspivot - Unpivot
UNPIVOTRotate columns to rowsunpivot - Tree
TREEFold an adjacency relation into nested documentstree - ωWhy
WHYReify a tuple's lineage provenance as a queryable columnωwhyprovenancelineage
Joins
- ⋈Natural join
JOINMatch on all shared columns⋈joinnatural-joinnatural join - USING join
USINGMatch on the named shared columns onlyusingjoin usingusing-joinusing joinleft join usingfull join using - ⨝Theta join
><Match on an explicit condition⨝theta-jointheta joinjoin on>< - ⟕Left outer join
|><Keep all left rows; NULLs for non-matches on right⟕left-outer-joinleft outer joinleft join|><ljoin - ⟖Right outer join
><|Keep all right rows; NULLs for non-matches on left⟖right-outer-joinright outer joinright join><|rjoin - ⟗Full outer join
|><|Keep all rows from both sides⟗full-outer-joinfull outer joinfull joinonly one ofeither tableboth tables|><|fjoin - ⋉Semi join
SEMIKeep left rows that have at least one match on right⋉semi-joinsemi joinin subquerysemi - ▷Anti join
ANTIKeep left rows with no match on right▷anti-joinanti joinnot in subqueryanti - Universal semi join
USEMIKeep left rows that match every row on the rightusemiuniversal-semi-joinuniversal semi joinuniversal semi-joinmatches everyall rows match - AS-OF joinMatch the nearest row in time
as ofasof-joinas-of joinasof - Interval joinMatch rows whose time intervals overlap (Allen-set predicates)
interval joininterval-join - Lateral join
LATERALCorrelated subquery used as a tablelaterallateral-joinlateral joinouter lateralcross applyouter applyleft join lateral
Set operations
- ×Cross product
CROSSCartesian product of two relations×crosscross product - ∪Union
UNIONSet union — deduplicated rows from both sides∪union - ⊎Union all
UALLBag union — all rows from both sides, duplicates kept⊎union-allunion alluall - ⊔Outer union
OUNIONUnion of relations with different schemas⊔outer-unionouter unionounion - −Difference
DIFFRows in the left side that are not in the right−-differencediffminusexcept - ∩Intersection
INTERRows present in both sides∩intersectioninterintersect - ÷Division
DIVRows in left related to ALL rows in right÷divisiondiv - ∆Symmetric difference
SYMDIFFRows in one side but not both∆symmetric-differencesymmetric differencesymdiff - ∘Composition
COMPOSESingle relational hop (pipe one relation through another)∘compositioncompose
Aggregates
- SUM
SUMSum of a numeric expressionsum - AVG
AVGAverage of a numeric expressionavgaverage - COUNT
COUNTCount rows or non-null valuescountcount distinctcount(distinctdistinct count - MIN
MINMinimum valuemin - MAX
MAXMaximum valuemax - COLLECT
COLLECTGather group values into an arraycollect - ARGMAX
ARGMAXYield value from row with max rankargmax - ARGMIN
ARGMINYield value from row with min rankargmin - STDDEV
STDDEVStandard deviationstddevstandard deviationstd dev - STDDEV_SAMP
STDDEV_SAMPSample standard deviationstddev_samp - STDDEV_POP
STDDEV_POPPopulation standard deviationstddev_pop - VARIANCE
VARIANCEVariancevariance - VAR_SAMP
VAR_SAMPSample variancevar_samp - VAR_POP
VAR_POPPopulation variancevar_pop - MEDIAN
MEDIANThe middle valuemedian - PERCENTILE_CONT
PERCENTILE_CONTThe value a fraction of the group lies below, interpolatedpercentile_contpercentilequantilewithin group - PERCENTILE_DISC
PERCENTILE_DISCThe first value a fraction of the group reachespercentile_disc - STRING_AGG
STRING_AGGJoin a group's values into one stringstring_agggroup_concatlistaggstring aggregatestring aggregationjoin stringsarray_agg - BOOL_AND
BOOL_ANDWhether every value in a group is truebool_andeveryall trueboolean andlogical and aggregate - BOOL_OR
BOOL_ORWhether any value in a group is truebool_orany trueboolean orlogical or aggregate - MODE
MODEThe most frequent valuemodestats_modemost frequentmost common value - FILTER
FILTERAggregate only the rows where a condition holdsfilteraggregate filterfilter whereconditional aggregatecount ifsum if - GROUPING
GROUPINGWhich keys a ROLLUP, CUBE or GROUPING SETS row rolled upgroupinggrouping_idrollupcubegrouping setssubtotalgrand total
Predicates
- ComparisonRow comparisons: =, !=, <, >, ≤, ≥
=comparison!=≠<><>≤≥<=>= - Regex matchDoes a regular expression match: ~, ~*, MATCHES
~regex-matchregexregular expressionregexp~*!~matchesrlikenot matches!~* - BETWEEN
BETWEENRange test, both ends includedbetweennot betweenrange - IS DISTINCT FROMNull-safe comparison: NULL equals NULL
is distinct fromis-distinct-fromis not distinct fromdistinct fromnull-safe<=> - IS TRUETruth test: IS [NOT] TRUE, FALSE or UNKNOWN
is truetruth-testis not trueis falseis not falseis unknownis not unknownunknowntruth value - Row valueCompare several columns as one: (a, b) = (1, 2)
(a, b) = (c, d)row-valuerow value comparisontuple comparisonrow comparisoncomposite comparison - EXISTS
EXISTSWhether a relation has a row: EXISTS (σ k = R.k (S)), NOT EXISTSexistsnot existswhere existshas a matchhas no matchsemi join subqueryanti join subquery - ANY / ALL
ANYCompare a value with each value of a list or a relation: x > ALL {1, 2}anyquantifiedquantified comparisonsomeall= any> all - ∧AND
ANDLogical conjunction∧and - ∨OR
ORLogical disjunction∨or - ¬NOT
NOTLogical negation¬not - ∈IN
INSet-membership test∈innot in∉ - IS NULLNULL / NOT NULL test
is nullis-nullisnullnull⊥ - LIKE
LIKEPattern matching with wildcardslikelike escapeilikenot ilikecase-insensitive like
Literals
| Page | Written as | What it does |
|---|---|---|
| String, number and boolean | "…" scalar-literals literal literals string string literal strings number number literal numbers quote quotes escape boolean true false | String, number and boolean literals |
| Date | date '…' date-literal date literal | Date literal |
| Time | time '…' time-literal time literal | Time literal |
| Timestamp | timestamp '…' timestamp-literal timestamp literal | Timestamp literal |
| TimestampTz | timestamptz '…' timestamptz timestamptz literal timestamp with time zone datetimeoffset offset | A timestamp that keeps its offset |
| Duration | duration '…' duration-literal duration literal interval | Duration literal |
| Array | [expr, …] array-construction array construction array list | Array construction literal |
| Struct | {name: expr} struct-construction struct construction struct record tuple tuple constructor row value composite named_struct object | Struct construction literal |
| Truth relations | unit truth-relations truth relation empty dee dum | Zero-column truth relations |
Language and scripts
| Page | Written as | What it does |
|---|---|---|
| Assignment | := assignment | Name a relation expression |
| Source | source | Declare an external data source |
| HTTP source | http http-source http source | HTTP / JSON data source |
| Connection | connection | Declare a named database connection |
| GEDCOM source | gedcom gedcom-source gedcom source ged | Read a GEDCOM genealogy file as individuals and families |
| MongoDB source | mongodb mongodb-source mongodb source mongo | Read a MongoDB collection as a relation |
| JSON source | json json-source json source json file records | Read a JSON file as a relation, with no declared columns |
| Generator source | generator generator-source generator source naturals primes | A generated series of rows: Range, Naturals, Primes |
| Env | env environment env statement | Name the environment file and profile a script's placeholders come from |
| Log source | log-source log source access log clf | Read a web server access log as a typed relation |
| Namespace | namespace | Declare a namespace |
| Import | import | Import definitions from another script |
| Inline table | [| … |] inline-table inline table | Literal relation value written inline |
| Def | def function definition define function create function user-defined function udf | Define a scalar or relation function |
| Def relation | def relation def-relation tvf | Define a relation-valued (table-valued) function |
| With ordinality | with ordinality with-ordinality ordinality | Attach a row number when unnesting |
| Relate | relate relationship relationships | Declare a named, bounded relationship between two relations |
| Delimited identifier | `name` delimited-identifier delimited identifier backtick quoted-identifier | Backtick a name that collides with a reserved word |
| Bound parameter | $name parameter parameters bound parameter bind $ | A value supplied when the query runs, never written into its text |
| Comments | -- comment comments /* | Ignore text: -- to end of line, / … / for a block |
| Operator spellings | σ / select spellings spelling ascii unicode glyph glyphs syntax | Every operator's Unicode glyph and its ASCII equivalent |
| Grammar | ebnf grammar bnf | The whole language as one EBNF grammar |
| Relation catalog | relix.relations introspection-relations relations boundedness bounded unbounded row_count | relix.relations — every relation the script can name, its kind, size and whether it ends |
| Column catalog | relix.columns introspection-columns columns distinct_count null_count | relix.columns — every column of every relation, its type and its distinct and NULL counts |
| Logical plan catalog | relix.plan introspection-plan plan relix.dependencies dependencies expression tree | relix.plan / relix.dependencies — every view's expression tree, and which view reads which relation, as rows |
| Connection catalog | relix.connections introspection-connections connections | relix.connections — the declared connections, by name and dialect, with nothing secret |
| Introspection stdlib | relix.unused introspection introspection-stdlib relix.deps relix.impact relix.find relix.schema relix.cycles relix.funcs | relix.unused / deps / impact / find / schema / cycles / funcs — introspection shipped as Relix |
| Observability feed | relix.events introspection-events events relix.rules feed | relix.events / relix.rules — the previous run's engine decisions, as rows |
| Component inventory | relix.version introspection-version version components inventory | relix.version — the engine, the facade and every discovered provider, as rows |
| Candidate keys | relix.keys introspection-keys keys candidate key candidate keys | relix.keys — the collected candidate keys of every relation, one row per key column |
| Reserved namespace | relix.catalog introspection-catalog catalog reserved namespace | relix.catalog — the relix.* surface describing itself |
| CASE | case case when case expression searched case simple case when then decode conditional | A value chosen by conditions: CASE WHEN c THEN a ELSE b END |
| Subquery | ( relation ) subquery sub-query scalar subquery correlated subquery correlated nested query inner query select in select value from another table | A relation used as a value: its one column in its one row, per row when it reads the row |
| COLLATE | collate collation nocase case-insensitive case insensitive ignore case binary collation locale | Compare, sort and group strings under a collation: s COLLATE NOCASE |
| CAST | cast convert conversion type conversion to_number to_string try_cast parse value | Convert a value to a type: CAST(x AS NUMBER) |
| AT TIME ZONE | at time zone at-time-zone time zone timezone conversion zone | The wall-clock time in a zone: ts AT TIME ZONE 'Europe/London' |
| EXTRACT | extract date part datepart date_part day of week iso week | A field of a date or time: EXTRACT(WEEK FROM d) |
| Arithmetic and concatenation | + arithmetic * / % || concatenation concatenate concat operator remainder operator modulo | Arithmetic and concatenation: + - * / % || |
Advanced operators
| Page | Written as | What it does |
|---|---|---|
| Closure | closure rclosure ⁺ transitive-closure linked chain either direction reachable | Transitive / reflexive-transitive closure |
| Cluster | cluster | Partition rows into clusters |
| Cover | cover | Combinatorial test-suite generation (t-way covering arrays) |
| Downsample | downsample | Reduce row density over a time / numeric axis |
| Fixpoint | fix fixpoint | General monotone recursion (least fixpoint) |
| Iterate | iterate iteration pagerank convergence | Replace-each-round iteration: PageRank, state machines, convergence |
| Optimize | optimize | LP / ILP declarative optimisation |
| Path | path levels below steps hops distance | Enumerate shortest / all paths in a graph |
| Record linkage | record-linkage linkage entity-resolution deduplication dedupe fuzzy-match similarity-join | Find rows that probably describe the same thing: similarity join, best match, CLUSTER |
| Sample | sample bernoulli tablesample | Bernoulli (row-probability) random sampling |
| Reservoir | reservoir reservoir-sample order by random() order by random | Reservoir (fixed-size) random sampling |
| Sessionize | sessionize | Gap-and-island / sessionisation |
| Solve | solve | Constraint solving over a relation |
| Top-K | top topk top-k top with ties | Return top-K rows per group |
| Trace | trace | Diagnostic operator — print rows as they pass through |
| Optimizer | optimizer optimiser rules rewrites | Logical rewrite rules — what fires, when, and how to see it |
| Pushdown | pushdown push-down explain | What folds into a backend and what the engine computes |
Functions
| Page | Written as | What it does |
|---|---|---|
| Len | len | String length |
| UCase | ucase | Upper-case |
| LCase | lcase | Lower-case |
| Trim | trim | Strip surrounding whitespace |
| LTrim | ltrim | Strip leading whitespace |
| RTrim | rtrim | Strip trailing whitespace |
| Left | left | Leftmost characters |
| Right | right | Rightmost characters |
| Mid | mid | Substring |
| InStr | instr | Find substring position |
| Chr | chr | Character from code |
| Asc | asc | Code of first character |
| Replace | replace | Substitute text |
| LPAD | lpad | Pad on the left |
| RPAD | rpad | Pad on the right |
| REVERSE | reverse | Characters in reverse order |
| REPEAT | repeat | A string written n times |
| SPLIT_PART | split_part | One field of a delimited string |
| CONCAT_WS | concat_ws | Join with a separator, skipping NULLs |
| INITCAP | initcap | Capitalise each word |
| MD5 | md5 | A string's MD5 digest, in hex |
| SHA1 | sha1 | A string's SHA-1 digest, in hex |
| SHA256 | sha256 | A string's SHA-256 digest, in hex |
| SHA2 | sha2 | A string's SHA-2 digest of a given length, in hex |
| HASHBYTES | hashbytes | SQL Server's digest by algorithm name, in hex |
| STANDARD_HASH | standard_hash | Oracle's digest by algorithm name, in hex |
| REGEXP_LIKE | regexp_like | Does a regular expression match |
| REGEXP_REPLACE | regexp_replace | Replace a regular-expression match |
| REGEXP_SUBSTR | regexp_substr | The text a regular expression matches |
| REGEXP_COUNT | regexp_count | How many times a regular expression matches |
| CHAR | char | Character from code |
| CHAR_LENGTH | char_length | String length |
| LENGTH | length | String length |
| LOWER | lower | Lower-case |
| REPLICATE | replicate | A string written n times |
| UPPER | upper | Upper-case |
| SUBSTRING | substring | Part of a string, as SQL counts it |
| SUBSTR | substr | Part of a string, as SQL counts it |
| STRPOS | strpos | Position of a substring |
| CHARINDEX | charindex | Position of a substring, SQL Server's order |
| ASCII | ascii | Code of the first character |
| CONCAT | concat | Join values as text, skipping NULLs |
| JSON_VALUE | json_value | A scalar read out of JSON text |
| JSON_QUERY | json_query | An object or array read out of JSON text |
| JSON_EXTRACT | json_extract | Any value read out of JSON text |
| Levenshtein | levenshtein | Edit distance between two strings |
| EditSimilarity | editsimilarity | Edit distance as a score from 0 to 1 |
| JaroWinkler | jarowinkler | Similarity of short strings, favouring a shared start |
| Trigram | trigram | Overlap of three-character pieces |
| Soundex | soundex | Phonetic key for a name |
| Abs | abs | Absolute value |
| Int | int | Floor to integer |
| Fix | fix | Truncate toward zero |
| Round | round | Round to nearest |
| Ceil | ceil | Round up |
| Sgn | sgn | Sign |
| Sqr | sqr | Square root |
| Log | log | Logarithm |
| Log10 | log10 | Base-10 logarithm |
| Exp | exp | Exponential |
| Sin | sin | Sine |
| Cos | cos | Cosine |
| Tan | tan | Tangent |
| Atn | atn | Arctangent |
| Asin | asin | Arcsine |
| Acos | acos | Arccosine |
| Atan2 | atan2 | Two-argument arctangent |
| Degrees | degrees | Radians to degrees |
| Radians | radians | Degrees to radians |
| Pi | pi | The constant π |
| Power | power | Raise to a power |
| Mod | mod | Remainder |
| Rand | rand | Random number |
| CEILING | ceiling | Round up |
| FLOOR | floor | Floor to integer |
| RANDOM | random | Random number |
| SIGN | sign | Sign |
| SQRT | sqrt | Square root |
| LN | ln | Logarithm |
| TRUNC | trunc | Truncate towards zero, to places |
| TRUNCATE | truncate | Truncate towards zero, to places |
| NOW | now current_timestamp localtimestamp | Current timestamp |
| CURRENT_DATE | current_date | Today |
| CURRENT_TIME | current_time | Current clock time |
| YEAR | year | Extract year |
| MONTH | month | Extract month |
| DAY | day | Extract day of month |
| HOUR | hour | Extract hour |
| MINUTE | minute | Extract minute |
| SECOND | second | Extract second |
| DOW | dow | Day of week, Sunday 0 |
| ISODOW | isodow | ISO day of week, Monday 1 |
| DOY | doy | Day of year |
| WEEK | week | ISO week number |
| ISOYEAR | isoyear | ISO week-numbering year |
| QUARTER | quarter | Quarter of the year |
| EPOCH | epoch | Seconds since 1970 |
| TIMEZONE | timezone | Wall-clock time in a zone |
| FROM_TZ | from_tz | A local wall-clock time read as an instant in a zone |
| MAKE_DATE | make_date | A date from its parts |
| LAST_DAY | last_day | Last day of the month |
| DATEDIFF | datediff timestampdiff | Calendar units between two dates |
| DATE_TRUNC | date_trunc | Truncate timestamp to a unit |
| DATEADD | dateadd | Add a number of units to a date |
| ADD_MONTHS | add_months | Move a date by whole months, as Oracle does |
| DATE_ADD | date_add | Add an interval to a date, as MySQL writes it |
| DATE_SUB | date_sub | Subtract an interval from a date, as MySQL writes it |
| AGE | age | The years, months and days between two dates |
| MINUTES | minutes | Measure a duration in minutes |
| SECONDS | seconds | Measure a duration in seconds |
| DAYS | days | Measure a duration in days |
| to_date | to_date | Parse a string to a DATE |
| to_timestamp | to_timestamp | Parse a string to a TIMESTAMP |
| to_time | to_time | Parse a string to a TIME |
| to_timestamptz | to_timestamptz | A TIMESTAMPTZ from text or a timestamp |
| DATEFROMPARTS | datefromparts | A date from its parts |
| EOMONTH | eomonth | Last day of the month |
| TIMESTAMPADD | timestampadd | Add a number of units to a date |
| IIf | iif if | Inline if / conditional value |
| Nz | nz | Null-to-value |
| Coalesce | coalesce ifnull nvl | First non-null |
| NULLIF | nullif | Value-to-null |
| Switch | switch | Multi-branch conditional value |
| Choose | choose | Pick a value by position |
| IsNull | isnull | Test for missing value |
| IsNumeric | isnumeric | Test if value looks numeric |
| CStr | cstr | Convert to string |
| CInt | cint | Convert to integer |
| CDbl | cdbl | Convert to number |
| CBool | cbool | Convert to boolean |
| TO_CHAR | to_char | Format a date or number as text |
| FORMAT | format | A number with grouped digits |
| Entries | entries | Read an object's fields as rows |
| ARRAY_TO_STRING | array_to_string | Join an array's elements into one string |
| CARDINALITY | cardinality | The number of elements in an array |
| ARRAY_LENGTH | array_length | The number of elements in an array |
| LEAST | least | Smallest of several values |
| GREATEST | greatest | Largest of several values |