PostgreSQL ALL operator

Sometimes you need to compare a value with all the values ​​in a list, like:

• Given a salary value. You need to confirm that this value is higher than the salaries of all employees.
• Given a state value. You need to determine if this state value is not equal to all state values.

You can use PostgreSQL ALL operators to solve these problems.

PostgreSQL ALL operator is used to compare a value with a list of values, and the expression returns true if all values ​​in the list ​​satisfy the specified condition, otherwise the expression returns false.

PostgreSQL ALL syntax

To compare a value with all the values ​​returned by a subquery, use the ALL operator:

comparison_operator ALL (array)

Here:

• The comparison_operator is a comparison operator such as =, !=, >, >=, <, <= etc.
• The array following the ALL operator is an array or subquery. Subqueries must be enclosed in parentheses.
• The expression returns true if all values ​​in array satisfies the specified condition, otherwise the expression returns false.

For examples:

• value = ALL (array): This expression returns true if all values ​​in the list equal value, otherwise returns false.
• value < ALL (array): This expression returns true if all the values ​​in the list are greater than value, otherwise returns false.
• value <> ALL (array): This expression returns true if all values ​​in the list do not equal value, otherwise returns false.

You can use ALL the operator in the WHERE clause as follows:

WHERE expr1 > ALL (subquery)

The ALL operator always evaluates to true if the subquery returns no rows.

PostgreSQL ALL operator Examples

PostgreSQL ALL and arrays

We often need to check whether all elements in an array meet specified conditions, such as:

• To check if all elements in the array [1, 2, 3] equal 2, use the following statement:

  SELECT 2 = ALL (ARRAY[1, 2, 3]);
  

   ?column?
  ----------
   f

• To check if all values in the array [1, 2, 3] are greater than or equal to 1, use the following statement:

  SELECT 1 <= ALL(ARRAY[1, 2, 3]);
  

  ?column?
  ----------
  t

• To check if all elements in the array [1, 2, 3] are not equal 0, use the following statement:

  SELECT 0 <> ALL(ARRAY[1, 2, 3]);
  

  ?column?
  ----------
  t

PostgreSQL ALL and subqueries

Here we provide some practical examples to demonstrate the power of the ALL operator.

Let’s use the film table from the PostgreSQL Sakila sample database .

To check if 99 is higher than the rent for all movies, use the following statement:

SELECT 99 > ALL (SELECT rental_rate FROM film);

 ?column?
----------
 t

To check if 66 is less than the rent for all movies, use the following statement:

SELECT 66 < ALL (SELECT rental_rate FROM film);

 ?column?
----------
 f

To find the number of films greater than or equal to all rentals, use the following statement:

SELECT count(*)
FROM film
WHERE rental_rate >= ALL (
    SELECT rental_rate
    FROM film
  );

 count
-------
   336

Conclusion

The PostgreSQL ALL operator is used to compare a value with all the values ​​returned by a subquery and return a boolean value. The ALL operator can be used in the WHERE clause to filter rows with specified conditions.