In DuckDB, bool_and() is an aggregate function that returns true if every input value is true, otherwise it returns false.

Here are some basic examples that demonstrate how it works.

Syntax

The syntax goes like this:

bool_and(arg)

Where arg is the target boolean column or expression that the function operates on.

Basic Example

Suppose we create the following table:

-- Create a simple table
CREATE TABLE simple_bool (value BOOLEAN);

-- Insert some boolean values
INSERT INTO simple_bool VALUES (TRUE), (FALSE), (TRUE);

Now let’s use bool_and() to check if all values are true:

SELECT 
    bool_and(value) AS all_true 
FROM simple_bool;

Output:

+----------+
| all_true |
+----------+
| false    |
+----------+

It returned false because not all values are true. Even though most values are true, the table contains one value that’s false, and that’s all the function needs in order to return false.

Let’s update the table so that all values are true and run the query again:

-- Set all values to true
UPDATE simple_bool SET value = TRUE;

-- Run the query again
SELECT 
    bool_and(value) AS all_true 
FROM simple_bool;

Output:

+----------+
| all_true |
+----------+
| true     |
+----------+

Grouped Data

We can use the GROUP BY clause and apply bool_and() to each group.

Let’s create another table:

-- Create a table with categories and boolean values
CREATE TABLE grouped_bool (category TEXT, value BOOLEAN);

-- Insert values into the table
INSERT INTO grouped_bool VALUES
('A', TRUE), 
('A', TRUE), 
('B', TRUE), 
('B', FALSE), 
('C', TRUE);

Now let’s use bool_and() against each group:

SELECT 
    category, 
    bool_and(value) AS all_true_per_group
FROM grouped_bool
GROUP BY category;

Result:

+----------+--------------------+
| category | all_true_per_group |
+----------+--------------------+
| A        | true               |
| B        | false              |
| C        | true               |
+----------+--------------------+

This query groups the data by category and checks if all boolean values in each group are TRUE. For category A, the result is TRUE, but for category B, it is FALSE because it contains a FALSE.

Combining with Conditions

Let’s create another table:

-- Create a more complex table with conditions
CREATE TABLE conditional_bool (id INTEGER, status BOOLEAN, active BOOLEAN);

-- Insert values into the table
INSERT INTO conditional_bool VALUES
(1, TRUE, TRUE),
(2, TRUE, TRUE),
(3, FALSE, TRUE),
(4, TRUE, FALSE);

Now let’s use bool_and() in combination with a conditional query to filter rows:

SELECT bool_and(status) AS all_active_true
FROM conditional_bool
WHERE active = TRUE;

Result:

+-----------------+
| all_active_true |
+-----------------+
| false           |
+-----------------+

This query filters the rows where active is TRUE and then applies bool_and() to check if all the status values in these filtered rows are TRUE. The result is FALSE because there is a row with status = FALSE among the active rows.

Let’s update that row to TRUE and run the query again:

UPDATE conditional_bool SET status = TRUE WHERE id = 3;

SELECT bool_and(status) AS all_active_true
FROM conditional_bool
WHERE active = TRUE;

Result:

+-----------------+
| all_active_true |
+-----------------+
| true            |
+-----------------+

This time bool_and() returned true as expected.