Expressions¶

This page provides an overview of all the expression forms supported by the Relational Layer.

Function calls¶

Named functions are invoked using function-call syntax, such as COALESCE(a, b). They are documented in two sections:

Scalar functions, which operate on the values of a single row.
Aggregate functions, which aggregate values across multiple rows.

Arithmetic operators¶

The following standard arithmetic operators are supported:

+  addition
-  subtraction
*  multiplication
/  division
%  modulo

These are all binary operators. If either of the two operands is NULL, the result is NULL.

Comparison operators¶

The following standard comparison operators are supported:

<  less than
>  greater than
<=  less than or equal to
>=  greater than or equal to
=  equal to
!=  not equal to
<>  not equal to (alias for !=)

These are all binary operators. If either of the two operands is NULL, the result is NULL.

Some examples:

SELECT * FROM products WHERE price > 100
SELECT * FROM products WHERE price <= 50
SELECT * FROM products WHERE name = 'Widget'
SELECT * FROM products WHERE stock != 0

The following predicates extend equality comparison to handle NULL explicitly:

Unlike = and !=, the IS DISTINCT FROM form treats NULL as a comparable value and never returns NULL.

The Relational Layer also provides the following comparison predicates:

Each of the predicates listed above has a negated NOT form (for example, NOT BETWEEN).

Logical operators¶

The following logical operators are supported:

AND  conjunction
OR  disjunction
XOR  exclusive or
NOT  negation

They follow the standard three-valued logic, in which an operand may be true, false, or NULL (unknown).

OR, AND, and XOR are binary operators. The following table summarizes their results if either of the two operands is NULL:

Left Operand	Right Operand	`OR`	`AND`	`XOR`
`TRUE`	`NULL`	`TRUE`	`NULL`	`NULL`
`FALSE`	`NULL`	`NULL`	`FALSE`	`NULL`
`NULL`	`TRUE`	`TRUE`	`NULL`	`NULL`
`NULL`	`FALSE`	`NULL`	`FALSE`	`NULL`
`NULL`	`NULL`	`NULL`	`NULL`	`NULL`

The unary NOT operator inverts its operand:

Operand	`NOT`
`TRUE`	`FALSE`
`FALSE`	`TRUE`
`NULL`	`NULL`

Other expressions¶

Several other expression forms are supported:

CASE, which returns a value based on the first of several conditions that evaluates to true.
CAST, which converts a value from one data type to another.
EXISTS, which tests whether a subquery returns any rows.

Detailed operator reference¶

The following pages provide detailed reference for some of the operators summarized above: