BOOL_AND and BOOL_OR
intermediateaggregation
2 min read
What are BOOL_AND and BOOL_OR?
BOOL_AND returns true if all input values are true. BOOL_OR returns true if at least one input value is true. They are aggregate functions that work on Boolean columns or expressions.
Syntax
SELECT BOOL_AND(condition) FROM table_name;
SELECT BOOL_OR(condition) FROM table_name;When to Use
- Checking if all students in a group passed an exam
- Determining if any product in a category is out of stock
- Aggregating Boolean flags across groups
Key Points
- BOOL_AND — Returns TRUE only if every row's value is TRUE.
- BOOL_OR — Returns TRUE if any row's value is TRUE.
- NULL Handling — NULLs are ignored. If all values are NULL, the result is NULL.
- Boolean Expressions — You can use expressions like
BOOL_AND(price > 0). - With GROUP BY — Commonly used with GROUP BY to check conditions per group.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
BOOL_AND and BOOL_OR Challenge
Write a query that solve this task: determine if every student has been assigned a grade.
Expected result
TRUE if every student has a non-NULL grade, FALSE otherwise.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are BOOL_AND and BOOL_OR?
Initializing database...Each run starts from fresh sample data.
More Examples
Check if any student is under 18
Is there at least one minor in the students table?
Initializing database...Each run starts from fresh sample data.
Per-course check
For each course, check if all enrolled students have a grade.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What happens if all values are NULL?
Both BOOL_AND and BOOL_OR return NULL if there are no non-NULL input values.
Can I use BOOL_AND on non-Boolean columns?
You need a Boolean expression. Use BOOL_AND(col > 0) or BOOL_AND(col IS NOT NULL) to convert to Boolean.