With the BOOLEAN data type in Oracle AI Database 26ai come aggregates for it. Are all passengers of a booking checked in? Is at least one? BOOLEAN_AND_AGG is true when every value in the group is true, BOOLEAN_OR_AGG when at least one is, and EVERY is the SQL-standard name that takes a condition directly.
Code for This Guide
The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
boolean_and_agg(expr) boolean_or_agg(expr) every(condition)
expr is a BOOLEAN value, such as a BOOLEAN column; EVERY accepts any condition, such as t.cabin = 'BUSINESS'.
Check-in Status per Booking
Example:
select b.booking_ref, count(*) as tickets,
boolean_and_agg(t.checked_in) as all_checked_in,
boolean_or_agg(t.checked_in) as any_checked_in,
every(t.cabin = 'BUSINESS') as all_business
from bookings b join tickets t on t.booking_id = b.booking_id
where b.booking_id in (1, 2, 699, 700)
group by b.booking_ref;Output:
BOOKING_REF TICKETS ALL_CHECKED_IN ANY_CHECKED_IN ALL_BUSINESS ______________ __________ _________________ _________________ _______________ N3N8PB 1 true true false 5HDV2D 2 true true false HQ89SX 2 false false true QVCKBC 1 false false true
checked_in is a BOOLEAN column of TICKETS. Bookings N3N8PB and 5HDV2D are fully checked in; the other two have no one checked in, and every ticket in them is in business class.
NULL Values
Example:
-- NULLs are ignored, like in other aggregates select boolean_and_agg(v) as and_agg, boolean_or_agg(v) as or_agg, count(v) as non_null from (values (true), (null), (true)) t (v);
Output:
AND_AGG OR_AGG NON_NULL __________ _________ ___________ true true 2
Like other aggregates, the boolean ones ignore NULLs: two true values and a NULL give true for both.
Before BOOLEAN
Without these functions, the same questions were written as MIN and MAX over 'Y' and 'N' flags, or as count(case when ... then 1 end) = count(*). The BOOLEAN aggregates say what they mean, and work directly with BOOLEAN columns and conditions.
Things to Know
- Over an empty group, the result is NULL.
- EVERY(x = y) is the same as BOOLEAN_AND_AGG(x = y).
- Use them in HAVING to keep groups where all or any rows meet a condition, for example having every(t.checked_in).
Related Guides
Conclusion
BOOLEAN_AND_AGG returns true when all values of a group are true, BOOLEAN_OR_AGG when any is, and EVERY applies the "all" test to a condition. They ignore NULLs and make all-or-any questions about groups direct to write.
