EVERY

MySQL

EVERY(subquery)

• subquery: This is an SQL statement that yields a result set, to be used by the EVERY function. It should output a single column of Boolean type or a column that can be implicitly converted to Booleans. Depending on the values in this column - TRUE, FALSE or NULL - the EVERY function will return a result accordingly.

Example

SELECT EVERY(age > 20) AS EVERY_Age
FROM Employees;

Output

| EVERY_Age |
|-----------|
|     0     |

Explanation

In the example provided, the EVERY function is used to check if the age of every employee in the Employees table is greater than 20. The function returns 0, indicating that not every employee’s age is greater than 20.

PostgreSQL

EVERY(expression) FILTER (WHERE condition)

• expression: This refers to the specific condition or comparison result that the EVERY function will evaluate across all rows in the group. It represents the field in which the condition will be checked.
• filter (where condition): This is an optional clause that allows you to specify a condition to filter the rows used in the aggregation process. The EVERY function will only consider rows where this condition is true.

Example

CREATE TEMPORARY TABLE temp_table (
    is_enabled BOOLEAN NOT NULL
);

INSERT INTO temp_table (is_enabled)
VALUES (TRUE), (TRUE), (TRUE), (FALSE);

SELECT
    EVERY(is_enabled)
FROM
    temp_table;

Output

f

Explanation

The “EVERY” function in PostgreSQL returns ‘true’ if all input values are ‘true’. In the provided example, the function checks the ‘is_enabled’ column in ‘temp_table’. Since one of the rows contains ‘false’, the ‘EVERY’ function returns ‘false’.

Oracle

EVERY(expression) OVER ([query_partition_clause] [order_by_clause] [windowing_clause])

• expression: The expression is a logical or computational statement that can either be a subquery, column, or a set of columns upon which the function is to be applied.
• query_partition_clause: The partition by clause is optional. It separates the input into different partitions or groups which share the same values in a specified set of columns. If this clause is not included in the function, SQL considers the whole result set as a single group.
• order_by_clause: The optional order by clause determines the order of data in a partition. It orders rows based on column values.
• windowing_clause: This is a also an optional parameter. The windowing clause further breaks down the sorted rows into sliding or anchored windows within each partition. Default windows restrict operations to the current row if the clause is not defined.

Example

SELECT
EVERY(COLA > 0) AS "All_Greater_Than_Zero"
FROM (
    SELECT
    1 AS COLA
    FROM DUAL
    UNION ALL
    SELECT
    2 AS COLA
    FROM DUAL
    UNION ALL
    SELECT
    -1 AS COLA
    FROM DUAL
)

Output

All_Greater_Than_Zero
----------------------
FALSE

Explanation

In this SQL snippet, the EVERY function checks whether all the rows in ‘COLA’ are greater than zero. If all values satisfy the criteria, it returns TRUE. Otherwise, it returns FALSE. In this case, since ‘-1’ is part of ‘COLA’ and is not greater than zero, the function returns FALSE.