Query Expressions
Below is a list of expressions in the query builder, a description and example.
Aliased Schema Column
A special variation of a Datasource Schema Column that can be used when the source is an Aliased Schema.
Arithmetic Binary Expression
An expression composed of two numeric operands and an arithmetic operator (add +, subtract -, multiply *, divide /, modulo %).
1 + 2
Arithmetic Unary Expression
Operates on a numeric value to flip its sign.
+ | -
Array Constructor
Creates a variable-length sequence of values.
ARRAY[1, 2, 3]
At Time Zone
Converts time stamp without time zone to/from time stamp with time zone, and time with time zone values to different time zones.
TIMESTAMP 'YYYY-MM-DD HH:MM:SS TIME_ZONE' AT TIME ZONE 'TIME_ZONE'
Between Predicate
Tests if a value is within a specified range.
value BETWEEN min AND max
Binary Literal
A sequence of hexadecimal characters.
X'abcf0129'
Boolean Literal
Literal values true and false.
true | false
Cast
Explicitly cast a value as a type. For example, it can be used to cast a varchar to a numeric value type and vice versa. try_cast returns null if the cast fails.
CAST(value AS type) | TRY_CAST(value AS type)
Char Literal
Fixed length character data.
CHAR 'cat' | CHAR(5)
Coalesce Expression
Returns the first non-null value in the argument list.
COALESCE(value1, value2[, ...])
Comparison Expression
Compares two values and returns true or false.
(value comparison_operator value)
Current Time
Returns the current time as of the start of the query.
current_time
Custom Function Argument
The index (starting at zero) of the argument provided to the Custom Function's function call.
Decimal Literal
A fixed-precision decimal number. Precision up to 38 digits is supported but performance is best up to 18 digits.
DECIMAL '10.3'
Dereference Expression
Accesses the value of a column from the specified location. Each part must be in double quotation marks.
"parent"."field"
Double Literal
A double is a 64-bit inexact, variable-precision implementing the IEEE Standard 754 for Binary Floating-Point Arithmetic.
10.3e0
Exists Predicate
Determines if a subquery returns any rows.
EXISTS(SELECT column FROM table WHERE condition)
Extract
Returns field from x. For example, EXTRACT(YEAR FROM TIMESTAMP '2022-10-20 05:10:00') returns the year.
EXTRACT(date_time_unit FROM TIMESTAMP 'YYYY-MM-DD HH:MM:SS')
Function Call
Built-in SQL function supported by Trino. Syntax varies by function. Search function name in Trino documentation.
Examples: concat('a', ' to ', 'z') | lower('ABC')
Identifier
Tokens that identify names of catalogs, schemas, tables, columns, functions, or other objects.
A reliable value used to identify and reference a specific object or record across systems.
firstName, lastName, accountNumber, customerID
If Expression
Evaluates and returns true_value if condition is true, otherwise evaluates and returns false_value.
if(condition, true_value, false_value)
In List Expression
Typically used within an In Predicate to determine if a value exists within a set of values.
value IN (value1, value2[, ...])
In Predicate
Determines if any values produced by the subquery are equal to the provided expression.
value IN (value1, value2[, ...]) | value IN (SELECT column FROM table)
Interval Literal
Specifies a fixed period of time.
INTERVAL [+/-] 'integer' date_time_unit TO date_time_unit
Is Not Null Predicate
Tests whether a value is not null.
value IS NOT NULL
Is Null Predicate
Tests whether a value is null.
value IS NULL
JSON Array
Creates a JSON array containing given elements.
JSON_ARRAY(
[ array_element [, ...] [ { NULL ON NULL | ABSENT ON NULL } ] ],
[ RETURNING type [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ] ]
)
JSON Exists
Determines whether a JSON value satisfies a JSON path specification.
JSON_EXISTS(
json_input [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ],
json_path
[ PASSING json_argument [, ...] ]
[ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]
)
JSON Object
Creates a JSON object containing given key-value pairs.
JSON_OBJECT(
[ key_value [, ...] [ { NULL ON NULL | ABSENT ON NULL } ] ],
[ { WITH UNIQUE [ KEYS ] | WITHOUT UNIQUE [ KEYS ] } ]
[ RETURNING type [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ] ]
)
JSON Query
Extracts a JSON value from a JSON value.
JSON_QUERY(
json_input [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ],
json_path
[ PASSING json_argument [, ...] ]
[ RETURNING type [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ] ]
[ WITHOUT [ ARRAY ] WRAPPER | WITH [ { CONDITIONAL | UNCONDITIONAL } ] [ ARRAY ] WRAPPER ]
[ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
[ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON EMPTY ]
[ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON ERROR ]
)
JSON Value
Extracts a scalar SQL value from a JSON value.
JSON_VALUE(
json_input [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ],
json_path
[ PASSING json_argument [, ...] ]
[ RETURNING type ]
[ { ERROR | NULL | DEFAULT expression } ON EMPTY ]
[ { ERROR | NULL | DEFAULT expression } ON ERROR ]
)
Junction
Used to logically combine multiple predicates.
predicate AND predicate | predicate OR predicate
Lambda Expression
Block of code which takes in parameters and returns a value. Lambda expressions are written with ->
Example: (x, y) -> x + y
Like Predicate
Used to match a specified character pattern in a string.
expression LIKE pattern [ ESCAPE 'escape_character' ]
Logical Binary Expression
Used to logically combine multiple predicates.
predicate AND predicate | predicate OR predicate
Integer Literal
Fixed numeric value. Integers do not have a decimal point or an exponential part.
0 | 1 | 42 | 100
Not Expression
Used to reverse the result of any given expression or condition. Typically used with boolean values.
NOT expression | Examples: NOT true | NOT x > y
Null If Expression
Returns null if value1 equals value2, otherwise returns value1.
NULLIF(value1, value2)
Null Literal
Signifies an unknown value.
null
Quantified Comparison Expression
Compares a value with a set of values.
value comparison_operator ALL/ANY/SOME (SELECT column1, column2[,...])
Searched Case Expression
Evaluates each boolean condition from left to right until one is true and returns the matching result. If no conditions are true, the result from the ELSE clause is returned if it exists, otherwise null is returned.
CASE
WHEN condition THEN result
[ WHEN ... ]
[ ELSE result ]
END
Simple Case Expression
Searches each value expression from left to right until it finds one that equals expression. The result for the matching value is returned. If no match is found, the result from the ELSE clause is returned if it exists, otherwise null is returned.
CASE expression
WHEN value THEN result
[ WHEN ... ]
[ ELSE result ]
END
String Literal
Fixed character data composed of a sequence of characters enclosed in single quotes.
'example string literal'
Subquery Expression
A subquery is an expression which is composed of a query.
(SELECT column FROM table)
Subscript Expression
Used to access an element of an array and is indexed starting from one.
my_array[1]
Symbol Reference
Reference to the unique symbol in a set of symbols.
Time Literal
Defines a time of day.
TIME 'HH:MM:SS'
Timestamp Literal
Defines a date and time of day.
TIMESTAMP 'YYYY-MM-DD HH:MM:SS'
When Clause
Used within CASE expressions to specify conditions.
WHEN condition THEN result | WHEN value THEN result