BOOLEAN_AND()

This aggregate function returns a logical AND of Boolean argument values for each row of the result set.

Syntax

BOOLEAN_AND([ALL | DISTINCT] expression)

Returns: Boolean

Usage Notes and Restrictions

  • By default, returns a single Boolean for the entire result set. To include other columns or values in the result set, or to produce multiple Boolean values for subsets of rows, include a GROUP BY clause in the query.
  • The function adheres to PostgreSQL-style semantics, where NULLs or unknowns are ignored, unless only values in the group or table are NULLs. See Example 1.
  • The boolean_and() function does not support the OVER clause, and therefore it cannot be used as an analytic function.

Example: BOOLEAN_AND() with GROUP BY Clause

Here the explanation for the results in this example:

  • The group that corresponds to x=2 has all values of b set to true; therefore, BOOLEAN_AND() of column b evaluates to true.
  • The groups that correspond to x=1 and x=3 have at least one value of b that is false; therefore, BOOLEAN_AND() for these groups evaluates to false.
  • The group with x=4 returns false, and the group with x=5 returns true, because BOOLEAN_AND() ignores the null values in these groups.
  • The group with x=6 returns null because the only values of b in that group are null.
create table t2 (x int, b boolean);
insert into t2 values (1, false), (1, false), (2, true), (2, true), (3, false), (3, true), (4, false), (4, null), (5, true), (5, null), (6, null), (6, null);
[localhost:21000] > select x, boolean_and(all b) from t2 group by x;
Query: select x, boolean_and(b) from t2 group by x
+---+----------------+
| x | boolean_and(b) |
+---+----------------+
| 4 | false          |
| 2 | true           |
| 6 | NULL           |
| 1 | false          |
| 5 | true           |
| 3 | false          |
+---+----------------+