Oracle Boolean Literals and Expressions in SQL

For decades Oracle SQL had no BOOLEAN type: flags were stored as 'Y' and 'N' or 1 and 0, and conditions could appear only in WHERE, CASE, and CHECK. Oracle AI Database 26ai has BOOLEAN in SQL. Tables can have BOOLEAN columns, TRUE and FALSE are literals, and any condition is an expression of type BOOLEAN that can be selected, stored, and combined.

Code for This Guide

The main examples are in the examples/data-types and examples/basic-elements folders 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.

Literals and Values

A BOOLEAN Column

The example creates a table with a BOOLEAN column, inserts TRUE, FALSE, NULL, a string, and a number, and queries them. The column can be used directly as a condition, as in WHERE active.

Example:

create table flags (name varchar2(20), active boolean);
insert into flags values ('upgrade offer', true), ('wifi promo', false),
                         ('unknown', null), ('from text', 'yes'), ('from number', 0);

select name, active, not active as negated,
       case when active then 'on' when not active then 'off' else '?' end as state
from   flags;

select count(*) as active_flags from flags where active;

Output:

Table FLAGS created.

5 rows inserted.

NAME             ACTIVE    NEGATED    STATE
________________ _________ __________ ________
upgrade offer    true      false      on
wifi promo       false     true       off
unknown                               ?
from text        true      false      on
from number      false     true       off

   ACTIVE_FLAGS
_______________
              2

The string 'yes' became true and the number 0 false. NOT active negates the value, and a NULL stays NULL. WHERE active keeps only the two true rows. Drop the table afterward with drop table flags purge.

Conditions as Values

Any condition can now appear in the select list, with AND, OR, NOT, and IS TRUE.

Example:

select airport_code, elevation_ft,
       elevation_ft > 1000                       as is_high,
       is_hub or country_code = 'IN'             as hub_or_india,
       (elevation_ft > 1000) is true             as is_true_test
from   airports
where  airport_code in ('DXB', 'BLR', 'LHR');

Output:

AIRPORT_CODE       ELEVATION_FT IS_HIGH    HUB_OR_INDIA    IS_TRUE_TEST
_______________ _______________ __________ _______________ _______________
BLR                        3002 true       true            true
DXB                          62 false      true            false
LHR                          83 false      false           false

is_hub is a BOOLEAN column of AIRPORTS, so is_hub OR country_code = 'IN' combines a column and a comparison. The conditions return true or false for every row here because none of the values is NULL.

Grouping, Sorting, and IS TRUE

BOOLEAN values group and sort like other values, with false before true. The IS TRUE, IS FALSE, and IS NULL tests always return true or false, never NULL, which makes them safe in CASE and WHERE.

Example:

-- BOOLEAN columns group and sort like any other: false sorts before true
select is_hub, count(*) as airports
from   airports
group  by is_hub
order  by is_hub;

-- IS TRUE, IS FALSE, and IS NULL never return NULL themselves
select v, v is true as is_true, v is false as is_false, v is null as is_null, not v as not_v
from   (values (true), (false), (null)) t (v);

Output:

IS_HUB       AIRPORTS
_________ ___________
false              21
true                1

V        IS_TRUE    IS_FALSE    IS_NULL    NOT_V
________ __________ ___________ __________ ________
true     true       false       false      false
false    false      true        false      true
         false      false       true

NOT NULL is still NULL, but NULL IS TRUE is false: use IS TRUE when an unknown value should count as no.

Things to Know

• TO_BOOLEAN converts strings and numbers explicitly, with DEFAULT ON CONVERSION ERROR for bad values.
• BOOLEAN_AND_AGG and BOOLEAN_OR_AGG aggregate BOOLEAN values across rows.
• Client drivers and tools need recent versions to fetch BOOLEAN columns; older ones may need TO_CHAR or CASE.

Related Guides

• Oracle TO_BOOLEAN Function
• How NULL Works in Oracle Conditions

Conclusion

Oracle AI Database 26ai brings BOOLEAN to SQL: TRUE and FALSE literals, BOOLEAN columns that accept common string and number forms, and conditions that are values you can select, store, group, and sort. Use IS TRUE when NULL should count as false, and TO_BOOLEAN for explicit conversions.