When you enter your query in the SQL Editor or create a calculation, you use Presto SQL syntax. To compare values, you may find these common functions and operators useful.
Comparison operators
| Operator | Description |
|---|---|
< |
Less than |
> |
Greater than |
<= |
Less than or equal to |
>= |
Greater than or equal to |
= |
Equal |
<> or != |
Not equal |
ALL, ANY, or SOME
You can use the ALL, ANY, and SOME quantifiers together with comparison operators, such as:
expression operator quantifier (subquery)
For example:
| Expression | Evaluates to true when |
|---|---|
A = ALL (...) |
A is equal to all values |
A <> ALL (...) |
A doesn't match any value |
A < ALL (...) |
A is smaller than the minimum value |
A = ANY (...) |
A is equal to any value. This is equivalent to A IN (...). |
A <> ANY (...) |
A doesn't match one or more values |
A < ANY (...) |
A is smaller than the maximum value |
Note: ANY and SOME mean the same thing and are interchangeable.
IS NULL and IS NOT NULL
To test whether a value is undefined, or null, use IS NULL or IS NOT
NULL with any data type.
Using NULL with IS NULL evaluates to true, while any other constant does not:
SELECT NULL IS NULL; -- true
SELECT 3 IS NULL; -- false
LIKE
To match a specified character pattern in a string, use the LIKE operator. Patterns can contain both regular and wildcard characters.
| Wildcard | Representation |
|---|---|
Percent sign % |
Zero, one, or multiple characters |
Underscore _ |
A single character |
To escape a wildcard character, use the character specified for the ESCAPE parameter, such as:
expression LIKE pattern [ ESCAPE 'escape_character' ]
If pattern or escape_character are NULL, the expression evaluates to null.
For example:
| Statement | Returns |
|---|---|
SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
|
'abc' and 'bcd' |
SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
|
'abc' |
SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
|
'bcd' |
SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
|
Nothing |
SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
|
'a_c' and '_cd' |
SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
|
'a%c' and '%cd' |