SQL Boolean Aggregates (BOOL_AND / BOOL_OR / EVERY)

Module 9 already established that only PostgreSQL has a genuinely native, enforced BOOLEAN type, everyone else fakes it with an integer, a bit, or a constrained character column. That same split carries straight into aggregation: only PostgreSQL has real boolean aggregate functions.

Learning Objectives

• Use PostgreSQL's bool_and(), bool_or(), and every(), the one database here with native boolean aggregates.
• Explain why every() and bool_and() are the exact same function under two different names.
• Build the honest MIN/MAX-over-0-and-1 equivalent for the five databases with no native boolean type to aggregate.

PostgreSQL's Native Boolean Aggregates

bool_and() returns true only if every value in the group is true, bool_or() returns true if at least one value in the group is true. every() is the exact SQL-standard name for the same function as bool_and(), both exist on PostgreSQL and behave identically, a naming choice, not two different functions.

SELECT project_id,
       BOOL_AND(completed_on_time) AS every_task_on_time,
       BOOL_OR(completed_on_time) AS at_least_one_task_on_time
FROM project_tasks
GROUP BY project_id;

Everyone Else: MIN/MAX Over 0 and 1

With a flag stored as 0/1 (or a faked boolean type that's really one underneath), MIN() across the group is 0 unless every row is 1, exactly BOOL_AND's logic, and MAX() is 1 if any row is 1, exactly BOOL_OR's logic. The same arithmetic truth table, expressed through plain numeric aggregates instead of a dedicated boolean one.

SELECT project_id,
       MIN(completed_on_time) AS every_task_on_time,
       MAX(completed_on_time) AS at_least_one_task_on_time
FROM project_tasks
GROUP BY project_id;

Try It Yourself

Find whether every task in project id 4 finished on time, on MySQL, using the MIN/MAX pattern.

SELECT MIN(completed_on_time) AS every_task_on_time
FROM project_tasks
WHERE project_id = 4;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• On PostgreSQL, use bool_and()/bool_or() directly on a real BOOLEAN column rather than faking it with 0/1 the way the other five databases have to.
• Everywhere else, be explicit that MIN()/MAX() is standing in for a boolean aggregate, a comment or a clearly-named column alias keeps the intent obvious to a future reader.
• Confirm the flag column genuinely only ever holds 0, 1, or NULL before relying on the MIN/MAX equivalence, any other value breaks the logic.

Interview Questions

Summary

bool_and(), bool_or(), and the standard-named every() are PostgreSQL's native boolean aggregates, possible because PostgreSQL alone has a real, enforced BOOLEAN type. Every other database here fakes the identical logic with MIN() (all true) and MAX() (at least one true) over a 0/1-valued column.

What's Next?

APPROX_COUNT_DISTINCT, a fast, statistically approximate alternative to COUNT(DISTINCT ...) on a genuinely huge table, with real, specific version floors on the two databases that actually have it.