BOOL_OR

7min
Overview
The BOOL_OR() function calculates all the boolean values in the aggregated group, which will have these results:
false if all the values are false for every row.
true if at least one row in the group is true.
The input and the return type must be in BOOL.
💡NULL values are not aggregated, so it will return NULL if there are zero input rows.
Examples
In this example, we will use a payment table that stores details of the orders, whether the order has been paid or unpaid by the customer:
Create a Table
CREATE TABLE payment (
    orderid int,
    custname text,
    orderproduct text,
    ordertotal real,
    paid boolean
);
INSERT INTO payment (orderid, custname, orderproduct, ordertotal, paid)
VALUES 
(9557411, 'Maya', 'Jeans', 10.5, false),
(9557421, 'Aaron', 'T-Shirt', 9.2, false),
(9557451, 'Alex', 'Hat', 10.8, false),
(9557311, 'Will', 'Hat', 8.5, true),
(9557321, 'Will', 'T-Shirt', 12.15, false),
(9557351, 'Maya', 'T-Shirt', 9.5, true),
(9557221, 'Maya', 'Jeans', 11.02, false),
(9557251, 'Alex', 'Jeans', 11.09, false),
(9557231, 'Aaron', 'Hat', 14.56, false),
(9557281, 'Aaron', 'Hat', 12.15, false),
(9557291, 'Will', 'T-Shirt', 13.1, false);
CREATE TABLE payment (
    orderid int,
    custname text,
    orderproduct text,
    ordertotal real,
    paid boolean
);
INSERT INTO payment (orderid, custname, orderproduct, ordertotal, paid)
VALUES 
(9557411, 'Maya', 'Jeans', 10.5, false),
(9557421, 'Aaron', 'T-Shirt', 9.2, false),
(9557451, 'Alex', 'Hat', 10.8, false),
(9557311, 'Will', 'Hat', 8.5, true),
(9557321, 'Will', 'T-Shirt', 12.15, false),
(9557351, 'Maya', 'T-Shirt', 9.5, true),
(9557221, 'Maya', 'Jeans', 11.02, false),
(9557251, 'Alex', 'Jeans', 11.09, false),
(9557231, 'Aaron', 'Hat', 14.56, false),
(9557281, 'Aaron', 'Hat', 12.15, false),
(9557291, 'Will', 'T-Shirt', 13.1, false);
﻿
Display the Table
SELECT * FROM payment;
SELECT * FROM payment;
﻿
The above query will show the following table:
Output
+----------+-----------+---------------+-------------+--------+
| orderid  | custname  | orderproduct  | ordertotal  | paid   |
+----------+-----------+---------------+-------------+--------+
| 9557411  | Maya      | Jeans         | 10.5        | f      |
| 9557421  | Aaron     | T-Shirt       | 9.2         | f      |
| 9557451  | Alex      | Hat           | 10.8        | f      |
| 9557311  | Will      | Hat           | 8.5         | t      |
| 9557321  | Will      | T-Shirt       | 12.15       | f      |
| 9557351  | Maya      | T-Shirt       | 9.5         | t      |
| 9557221  | Maya      | Jeans         | 11.02       | f      |
| 9557251  | Alex      | Jeans         | 11.09       | f      |
| 9557231  | Aaron     | Hat           | 14.56       | f      |
| 9557281  | Aaron     | Hat           | 12.15       | f      |
| 9557291  | Will      | T-Shirt       | 13.1        | f      |
+----------+-----------+---------------+-------------+--------+
+----------+-----------+---------------+-------------+--------+
| orderid  | custname  | orderproduct  | ordertotal  | paid   |
+----------+-----------+---------------+-------------+--------+
| 9557411  | Maya      | Jeans         | 10.5        | f      |
| 9557421  | Aaron     | T-Shirt       | 9.2         | f      |
| 9557451  | Alex      | Hat           | 10.8        | f      |
| 9557311  | Will      | Hat           | 8.5         | t      |
| 9557321  | Will      | T-Shirt       | 12.15       | f      |
| 9557351  | Maya      | T-Shirt       | 9.5         | t      |
| 9557221  | Maya      | Jeans         | 11.02       | f      |
| 9557251  | Alex      | Jeans         | 11.09       | f      |
| 9557231  | Aaron     | Hat           | 14.56       | f      |
| 9557281  | Aaron     | Hat           | 12.15       | f      |
| 9557291  | Will      | T-Shirt       | 13.1        | f      |
+----------+-----------+---------------+-------------+--------+
﻿
#Case 1: BOOL_OR with a true result
We will find out if all customers have paid for their orders using the query below:
Select bool_or
SELECT BOOL_OR(paid) AS "final_result" FROM payment;
SELECT BOOL_OR(paid) AS "final_result" FROM payment;
﻿
If there is at least one TRUE value, the overall result will be TRUE. The final output shows that some order has been paid regardless of the other unpaid orders.
Output
+--------------+
| final_result |
+--------------+
| t            |
+--------------+
+--------------+
| final_result |
+--------------+
| t            |
+--------------+
﻿
#Case 2: BOOL_OR with a false result
We will find out if Aaron has paid for his orders using the query below:
Select bool_or
SELECT BOOL_OR(paid) AS aaron_paid
FROM payment
WHERE custname ='Aaron';
SELECT BOOL_OR(paid) AS aaron_paid
FROM payment
WHERE custname ='Aaron';
﻿
If all values are FALSE, then the overall result will be FALSE. The final output shows that Aaron hasn’t paid for all his orders.
Output
+-------------+
| aaron_paid  |
+-------------+
| f           |
+-------------+
+-------------+
| aaron_paid  |
+-------------+
| f           |
+-------------+
﻿
﻿
Updated 09 Oct 2023
Did this page help you?
BOOL_AND
String Functions
Docs powered by Archbee
TABLE OF CONTENTS
Overview
Examples
#Case 1: BOOL_OR with a true result
#Case 2: BOOL_OR with a false result