BOOL_AND and BOOL_OR in PostgreSQL: Boolean Aggregates per Group

BOOL_AND and BOOL_OR are aggregate functions over a boolean column. The first answers "is every row in the group true?", the second "is at least one row true?". They are the direct way to ask questions like "does every order in this group qualify?" or "is any user an admin?", without the COUNT(*) = COUNT(...) gymnastics.

What BOOL_AND and BOOL_OR return

Both take a boolean expression and fold a group into a single value:

• BOOL_AND(expr) is true only when expr is true in every row of the group.
• BOOL_OR(expr) is true when expr is true in at least one row.

SELECT
    BOOL_AND(active)   AS all_active,
    BOOL_OR(is_admin)  AS has_admin
FROM users;

Most often they sit next to a GROUP BY. Per country, you can see at a glance where every account is active and where there is at least one admin:

SELECT
    country,
    BOOL_AND(active)  AS everyone_active,
    BOOL_OR(is_admin) AS any_admin
FROM users
GROUP BY country;

The expression need not be a ready-made boolean column; any comparison works. This checks that all of a user's orders are large:

SELECT
    user_id,
    BOOL_AND(amount >= 100) AS all_big_orders
FROM orders
GROUP BY user_id;

NULLs are ignored (and an empty group yields NULL)

Like other aggregates, BOOL_AND and BOOL_OR skip rows where the expression is NULL. The result is computed over the "known" boolean values only:

• BOOL_AND looks at non-NULL rows only; a lone NULL will not force the result to false.
• BOOL_OR likewise relies on the non-NULL rows.
• If every value in the group is NULL (or there are no rows at all), the result is NULL, not true/false.

-- a NULL status is ignored, not treated as 'not shipped'
SELECT
    user_id,
    BOOL_AND(status = 'shipped') AS all_shipped
FROM orders
GROUP BY user_id;

Gotcha: the "all NULLs yield NULL" rule is easy to miss. A WHERE all_shipped = true filter silently drops groups whose status is never populated, because the predicate is NULL, not false, for them. If those groups should count as "not met", wrap the result: COALESCE(BOOL_AND(status = 'shipped'), false).

BOOL_AND versus EVERY

In PostgreSQL, EVERY is the standard SQL synonym for BOOL_AND, identical in behavior. There is no symmetric ANY aggregate in the standard (ANY is reserved for subqueries), so BOOL_OR covers the "at least one" case.

-- EVERY is exactly BOOL_AND
SELECT
    dept,
    EVERY(salary >= 50000) AS all_well_paid,
    BOOL_OR(salary >= 200000) AS any_top_earner
FROM employees
GROUP BY dept;

Choosing between EVERY and BOOL_AND is style and portability: EVERY is closer to the standard, while BOOL_AND reads better paired with BOOL_OR. A handy pattern is a whole-group gate in HAVING, for example departments where every employee earns at least a threshold:

SELECT dept
FROM employees
GROUP BY dept
HAVING BOOL_AND(salary >= 50000);

Emulating it in MySQL and ClickHouse

MySQL has no built-in BOOL_AND/BOOL_OR, but a boolean there is 0/1, so MIN and MAX give the same result: the minimum of the flags is 1 only when all are 1 (that is AND), and the maximum is 1 when any is 1 (that is OR).

-- MySQL: MIN/MAX over a boolean expression cast to 0/1
SELECT
    user_id,
    MIN(amount >= 100) AS all_big_orders,  -- like BOOL_AND
    MAX(status = 'paid') AS any_paid        -- like BOOL_OR
FROM orders
GROUP BY user_id;

The same trick works in PostgreSQL if you cast the boolean to an integer first: MIN((amount >= 100)::int). A few differences to keep in mind:

• In MySQL the comparison amount >= 100 already returns 1/0, so no explicit cast is needed. In PostgreSQL add ::int, because MIN/MAX do not accept a boolean directly.
• MySQL's MIN/MAX also ignore NULLs, so the empty-group behavior (NULL output) matches.
• ClickHouse has native min/max over UInt8, plus minOrNull/maxOrNull; there is no direct bool_and, so people write min(amount >= 100) and max(status = 'paid').

Bottom line: for "the whole group satisfies a condition" and "at least one does" checks, reach for BOOL_AND/BOOL_OR (or EVERY) in PostgreSQL, mind the NULL-skipping and the NULL on a fully empty group; in other engines the same meaning is built from MIN/MAX of a boolean flag.