Predicate Functions ¶

Predicate functions are used to evaluate conditions over collections or sequences. These functions return a boolean value based on whether elements in a collection satisfy a given predicate.

ALL ¶

ALL(variable IN collection WHERE condition)¶

The ALL function returns TRUE if all elements in the collection satisfy the specified condition. If any element does not satisfy the condition, it returns FALSE. If the collection is empty, it returns TRUE (vacuous truth).

Parameters:

variable – Loop variable that represents each element in the collection
collection – A sequence, set, or collection expression to iterate over
condition – Boolean condition that is evaluated for each element

Return type:

BOOLEAN

-- Example with literal sequence: returns FALSE (not all elements > 2)
SELECT ALL(x IN {1,2,3,4} WHERE x > 2);

  all(x in {1, 2, 3, 4} where x>2)
===================================
                              false

-- Example with literal sequence: returns TRUE (all elements > 2)
SELECT ALL(x IN {3,4,5,6} WHERE x > 2);

  all(x in {3, 4, 5, 6} where x>2)
===================================
                               true

The ALL function can be used with table columns that contain sequences:

CREATE TABLE Person (id INT PRIMARY KEY, firstName STRING, lastName STRING, age INT, col1 SEQUENCE(integer));

INSERT INTO Person (id, firstName, lastName, age, col1) VALUES (0, 'Jerry', 'Madonna', 10, {1, 2, 3});
INSERT INTO Person (id, firstName, lastName, age, col1) VALUES (1, 'John', 'Doe', 20, {4, 5, 6});
INSERT INTO Person (id, firstName, lastName, age, col1) VALUES (2, 'Alice', 'Johnson', 40, {7, 8, 9});
INSERT INTO Person (id, firstName, lastName, age, col1) VALUES (3, NULL, NULL, NULL, {null, null, null});

-- Check if all elements in col1 are greater than 2
SELECT ALL(x IN col1 WHERE x > 2) FROM person;

  all(x in col1 where x>2)
===========================
                      false
                       true
                       true
                      false

-- Check if all elements in col1 are greater than 0
SELECT ALL(x IN col1 WHERE x > 0) FROM person;

  all(x in col1 where x>0)
===========================
                       true
                       true
                       true
                      false

-- Use ALL in WHERE clause to filter rows
SELECT * FROM person WHERE ALL(x IN col1 WHERE x > 0);

           id  firstName             lastName                      age  col1
==================================================================================
            0  'Jerry'               'Madonna'                      10  {1, 2, 3}
            1  'John'                'Doe'                          20  {4, 5, 6}
            2  'Alice'               'Johnson'                      40  {7, 8, 9}

ANY ¶

ANY(variable IN collection WHERE condition)¶

The ANY function returns TRUE if at least one element in the collection satisfies the specified condition. If no elements satisfy the condition, it returns FALSE. If the collection is empty, it returns FALSE.

Parameters:

variable – Loop variable that represents each element in the collection
collection – A sequence, set, or collection expression to iterate over
condition – Boolean condition that is evaluated for each element

Return type:

BOOLEAN

-- Example with literal sequence: returns FALSE (no elements > 2)
SELECT ANY(x IN {-1,0,1,2} WHERE x > 2);

  any(x in {-1, 0, 1, 2} where x>2)
===================================
                              false

-- Example with literal sequence: returns TRUE (at least one element > 2)
SELECT ANY(x IN {3,4,5,6} WHERE x > 2);

  any(x in {3, 4, 5, 6} where x>2)
===================================
                               true

The ANY function can be used with table columns that contain sequences:

-- Check if any element in col1 is greater than 2
SELECT ANY(x IN col1 WHERE x > 2) FROM person;

  any(x in col1 where x>2)
===========================
                       true
                       true
                       true
                      false

-- Check if any element in col1 is greater than 5
SELECT ANY(x IN col1 WHERE x > 5) FROM person;

  any(x in col1 where x>5)
===========================
                      false
                       true
                       true
                      false

-- Use ANY in WHERE clause to filter rows
SELECT * FROM person WHERE ANY(x IN col1 WHERE x > 5);

           id  firstName             lastName                      age  col1
==================================================================================
            1  'John'                'Doe'                          20  {4, 5, 6}
            2  'Alice'               'Johnson'                      40  {7, 8, 9}

NONE ¶

NONE(variable IN collection WHERE condition)¶

The NONE function returns TRUE if no elements in the collection satisfy the specified condition. If any element satisfies the condition, it returns FALSE. If the collection is empty, it returns TRUE.

Parameters:

variable – Loop variable that represents each element in the collection
collection – A sequence, set, or collection expression to iterate over
condition – Boolean condition that is evaluated for each element

Return type:

BOOLEAN

-- Example with literal sequence: returns FALSE (some elements > 2)
SELECT NONE(x IN {1,2,3,4} WHERE x > 2);

  none(x in {1, 2, 3, 4} where x>2)
====================================
                               false

-- Example with literal sequence: returns TRUE (no elements > 2)
SELECT NONE(x IN {-1,0,1,2} WHERE x > 2);

  none(x in {-1, 0, 1, 2} where x>2)
====================================
                                true

The NONE function can be used with table columns that contain sequences:

-- Check if no elements in col1 are greater than 2
SELECT NONE(x IN col1 WHERE x > 2) FROM person;

  none(x in col1 where x>2)
============================
                       false
                       false
                       false
                        true

-- Check if no elements in col1 are greater than 5
SELECT NONE(x IN col1 WHERE x > 5) FROM person;

  none(x in col1 where x>5)
============================
                        true
                       false
                       false
                        true

-- Use NONE in WHERE clause to filter rows
SELECT * FROM person WHERE NONE(x IN col1 WHERE x > 2);

There are no results.

SINGLE ¶

SINGLE(variable IN collection WHERE condition)¶

The SINGLE function returns TRUE if exactly one element in the collection satisfies the specified condition. If zero elements or more than one element satisfy the condition, it returns FALSE.

Parameters:

variable – Loop variable that represents each element in the collection
collection – A sequence, set, or collection expression to iterate over
condition – Boolean condition that is evaluated for each element

Return type:

BOOLEAN

-- Example with literal sequence: returns FALSE (two elements > 2)
SELECT SINGLE(x IN {1,2,3,4} WHERE x > 2);

  single(x in {1, 2, 3, 4} where x>2)
======================================
                                 false

-- Example with literal sequence: returns TRUE (exactly one element > 2)
SELECT SINGLE(x IN {0,1,2,3,-1} WHERE x > 2);

  single(x in {0, 1, 2, 3, -1} where x>2)
==========================================
                                      true

The SINGLE function can be used with table columns that contain sequences:

-- Check if exactly one element in col1 is greater than 0
SELECT SINGLE(x IN col1 WHERE x > 0) FROM person;

  single(x in col1 where x>0)
==============================
                         false
                         false
                         false
                         false

-- Check if exactly one element in col1 is greater than 2
SELECT SINGLE(x IN col1 WHERE x > 2) FROM person;

  single(x in col1 where x>2)
==============================
                          true
                         false
                         false
                         false

-- Use SINGLE in WHERE clause to filter rows
SELECT * FROM person WHERE SINGLE(x IN col1 WHERE x > 2);

           id  firstName             lastName                      age  col1
==================================================================================
            0  'Jerry'               'Madonna'                      10  {1, 2, 3}

Note

All predicate functions work with sequences and collections of any comparable type.
The variable name used in the predicate expression is scoped to that expression only.
Predicate functions can be used in SELECT clauses, WHERE clauses, and any place where a boolean expression is valid.
When working with NULL values in collections, the behavior follows standard three-valued logic.

Predicate Functions¶

ALL¶

ANY¶

NONE¶

SINGLE¶

Predicate Functions ¶

ALL ¶

ANY ¶

NONE ¶

SINGLE ¶