:meta-keywords: coradb all, coradb any, coradb none, coradb single

:tocdepth: 3

*******************
Predicate Functions
*******************

.. contents::

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
===

.. function:: 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).

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

.. code-block:: sql

    -- 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

.. code-block:: sql

    -- 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:

.. code-block:: sql

    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});

.. code-block:: sql

    -- 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

.. code-block:: sql

    -- 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

.. code-block:: sql

    -- 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
===

.. function:: 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**.

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

.. code-block:: sql

    -- 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

.. code-block:: sql

    -- 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:

.. code-block:: sql

    -- 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

.. code-block:: sql

    -- 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

.. code-block:: sql

    -- 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
====

.. function:: 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**.

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

.. code-block:: sql

    -- 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

.. code-block:: sql

    -- 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:

.. code-block:: sql

    -- 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

.. code-block:: sql

    -- 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

.. code-block:: sql

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

::

    There are no results.

SINGLE
======

.. function:: 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**.

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

.. code-block:: sql

    -- 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

.. code-block:: sql

    -- 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:

.. code-block:: sql

    -- 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

.. code-block:: sql

    -- 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

.. code-block:: sql

    -- 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.