AggregatePostgreSQL only

BOOL_OR

Returns true if a condition is true for at least one row.

Syntax

BOOL_OR(<condition>)

The “is there at least one?” aggregate function. Together with GROUP BY it answers that for each group: is there anyone in the department without a phone number. It skips NULL. Its mirror function is BOOL_AND: “all of them?”.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

Does anyone earn over 1300, and over 2000

SELECT BOOL_OR(salary > 1300) AS someone_above_1300,

BOOL_OR(salary > 2000) AS someone_above_2000

FROM employees;

someone_above_1300	someone_above_2000
true	false

Is there anyone in the department without a phone number

SELECT department,

BOOL_OR(phone IS NULL) AS someone_without_phone

FROM employees

GROUP BY department

ORDER BY department;

department	someone_without_phone
backend	true
design	true
frontend	false

Departments with someone who has no phone number

SELECT department

FROM employees

GROUP BY department

HAVING BOOL_OR(phone IS NULL)

ORDER BY department;

department
backend
design

Watch out

NULL values are skipped, and if the condition gives NULL for every row, the result is NULL too, not false: BOOL_OR(bonus > 100) in a group where nobody has a bonus.

If there are no rows, the result is NULL.

To simply check whether there's at least one row, you don't need a condition: EXISTS (…) or COUNT(*) > 0 is simpler.

Related functions

BOOL_AND COUNT CASE