This aggregate function returns a logical AND of Boolean
argument values for each row of the result set.
BOOLEAN_AND([ALL | DISTINCT] expression)
Returns: Boolean
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.NULL
s or unknowns are ignored, unless only values in the group or table are NULL
s. See Example 1.boolean_and()
function does not support the OVER
clause, and therefore it cannot be used as an analytic function.Here the explanation for the results in this example:
x=2
has all values of b
set to true
; therefore, BOOLEAN_AND()
of column b
evaluates to true
. x=1
and x=3
have at least one value of b
that is false
; therefore, BOOLEAN_AND()
for these groups evaluates to false
.x=4
returns false
, and the group with x=5
returns true
, because BOOLEAN_AND()
ignores the null
values in these groups.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 | +---+----------------+