For all the great new functionality introduced in a new database release, sometimes it’s the simple and small things that generate the most buzz. The 23ai introduction of Boolean data types in SQL may be one of the biggest.
For many years in prior releases the argument against a Boolean SQL type is that it wasn’t needed; and, to be honest, it wasn’t a technical necessity. Developers all over the world, myself included, used various flags of other data types like Y/N, 1/0, or T/F and our applications worked.
However, anyone familiar with other programming languages would often find the code awkward and artificial that something that was simply true or false had to be evaluated with text or numeric types instead of a native Boolean. 23ai follows the SQL standard though and brings Booleans to the Oracle SQL language.
Boolean Columns
To begin, we’ll create a table declaring two Booleans, one of which is constrained to be NOT NULL.
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> create table testtable(b boolean, bnn boolean not null);
Table created.
SQL> insert into testtable(b,bnn) values (true,true);
1 row created.
SQL> insert into testtable(b,bnn) values (false,false);
1 row created.
SQL> insert into testtable(b,bnn) values (null,true);
1 row created.
SQL> insert into testtable(b,bnn) values (null,false);
1 row created.
SQL> insert into testtable(b,bnn) values (null,null);
insert into testtable(b,bnn) values (null,null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SDS"."TESTTABLE"."BNN")
The columns behave much as we would expect. 4 rows populated and the 5th insert generating an error because we tried to insert a NULL into a column constrained to not allow them.
SQL> select * from testtable;
B BNN
----------- -----------
TRUE TRUE
FALSE FALSE
TRUE
FALSE
4 rows selected.
Note, the Boolean literals TRUE and FALSE are not case sensitive. In the examples above I used lower-case, but upper- and mixed-case are equally valid.
SQL> insert into testtable(b,bnn) values (TRUE,True);
1 row created.
SQL> insert into testtable(b,bnn) values (FALSE,False);
1 row created.
SQL> insert into testtable(b,bnn) values (TRUE,TruE);
1 row created.
SQL> insert into testtable(b,bnn) values (FALSE,FaLSe);
1 row created.
SQL> select * from testtable;
B BNN
----------- -----------
TRUE TRUE
FALSE FALSE
TRUE
FALSE
TRUE TRUE
FALSE FALSE
TRUE TRUE
FALSE FALSE
8 rows selected.
Datatype Conversion
23ai supports many common literals for implicit conversion to Boolean values.
‘true’, ‘false’, ‘yes’, ‘no’, ‘on’, ‘off’, ‘1’, ‘0’, ‘t’, ‘f’, ‘y’, ‘n’ can be converted to Boolean True/False values. All of the text literals are case-insensitive and based on English words. There are no equivalents for other languages such as French ‘oui’,’non’.
Also note, numeric values may also be implicitly converted to Boolean values. 0 converts to FALSE and non-zero values are TRUE.
SQL> truncate table testtable;
Table truncated.
SQL> insert into testtable(b,bnn) values ('true','False');
1 row created.
SQL> insert into testtable(b,bnn) values ('YES','NO');
1 row created.
SQL> insert into testtable(b,bnn) values ('on','off');
1 row created.
SQL> insert into testtable(b,bnn) values ('1','0');
1 row created.
SQL> insert into testtable(b,bnn) values ('t','f');
1 row created.
SQL> insert into testtable(b,bnn) values ('y','n');
1 row created.
SQL> insert into testtable(b,bnn) values (12345,0);
1 row created.
SQL> insert into testtable(b,bnn) values (-0.234,-0.0000);
1 row created.
SQL> select * from testtable;
B BNN
----------- -----------
TRUE FALSE
TRUE FALSE
TRUE FALSE
TRUE FALSE
TRUE FALSE
TRUE FALSE
TRUE FALSE
TRUE FALSE
Rather than using implicit conversion, you can be explicit with the literals using the TO_BOOLEAN function.
SQL> truncate table testtable;
Table truncated.
SQL> insert into testtable(b,bnn) values (to_boolean('true'),to_boolean('False'));
1 row created.
SQL> insert into testtable(b,bnn) values (to_boolean('YES'),to_boolean('NO'));
1 row created.
SQL> insert into testtable(b,bnn) values (to_boolean('on'),to_boolean('off'));
1 row created.
SQL> insert into testtable(b,bnn) values (to_boolean('1'),to_boolean('0'));
1 row created.
SQL> insert into testtable(b,bnn) values (to_boolean('t'),to_boolean('f'));
1 row created.
SQL> insert into testtable(b,bnn) values (to_boolean('y'),to_boolean('n'));
1 row created.
SQL> insert into testtable(b,bnn) values (to_boolean(12345),to_boolean(0));
1 row created.
SQL> insert into testtable(b,bnn) values (to_boolean(-0.234),to_boolean(-0.0000));
1 row created.
SQL> select * from testtable;
B BNN
----------- -----------
TRUE FALSE
TRUE FALSE
TRUE FALSE
TRUE FALSE
TRUE FALSE
TRUE FALSE
TRUE FALSE
TRUE FALSE
The TO_BOOLEAN function only supports the literals above. You’ll get an invalid boolean error if you try to convert an illegal value.
SQL> insert into testtable(b,bnn) values (to_boolean('oui'),to_boolean('non'));
insert into testtable(b,bnn) values (to_boolean('oui'),to_boolean('non'))
*
ERROR at line 1:
ORA-61800: invalid boolean literal: oui
Boolean Logic
Boolean logic has always been supported in the where clauses of a query and this extends to the new data type. Furthermore, logical results can also be returned as a SQL expression.
SQL> select * from testtable where b and bnn;
B BNN
----------- -----------
TRUE TRUE
TRUE TRUE
TRUE TRUE
SQL> select * from testtable where b or bnn;
B BNN
----------- -----------
TRUE TRUE
TRUE
TRUE TRUE
TRUE TRUE
SQL> select b, bnn, b and bnn, b or bnn from testtable;
B BNN BANDBNN BORBNN
----------- ----------- ----------- -----------
TRUE TRUE TRUE TRUE
FALSE FALSE FALSE FALSE
TRUE TRUE
FALSE FALSE
TRUE TRUE TRUE TRUE
FALSE FALSE FALSE FALSE
TRUE TRUE TRUE TRUE
FALSE FALSE FALSE FALSE
At this point it is important to note how NULL values are evaluated with the operators. As we can see above, (NULL and TRUE) returns NULL, but (NULL and FALSE) returns FALSE. This might seem strange when compared with NULL operators used with other data types. For instance, adding, multipling, subtracting, dividing NULL values with a non-NULL value will result in a NULL regardless of the non-NULL value. But with a Boolean, the FALSE expression survives. Similarly, (NULL or TRUE) resolves to TRUE, not NULL. So again, the null-propagation behaves differently than one might expect compared to operators with other data types.
If you think of NULL as Unknown, then the Boolean logic does make sense and the 23ai SQL Reference shows the operator results in truth tables for all combinations of TRUE, FALSE, and UNKNOWN with AND and OR.
Unfortunately, there are some gaps in the evaluations. While you can evaluate two Boolean columns with AND or OR as shown above, or two Boolean literals, or a column with a literal, you can’t use an implicitly or explicitly converted literal in a Boolean AND/OR expression. Even trying to enclose the expression in parentheses to ensure it is evaluated before other expressions or conditions doesn’t work.
SQL> truncate table testtable; Table truncated. SQL> insert into testtable(b,bnn) values (true,true); 1 row created. SQL> insert into testtable(b,bnn) values (false,false); 1 row created. SQL> insert into testtable(b,bnn) values (null,true); 1 row created. SQL> select b and true from testtable; BANDTRUE ----------- TRUE FALSE SQL> select b or true from testtable; BORTRUE ----------- TRUE TRUE TRUE SQL> select true and false from dual; TRUEANDFALS ----------- FALSE
These expressions fail with converted literals, implicit or explicit.
SQL> select true and 'true' from dual; -- expression on implicit conversion fails
select true and 'true' from dual
*
ERROR at line 1:
ORA-00920: invalid relational operator
SQL> select true and to_boolean('true') from dual; -- expression on explicit conversion also fails
select true and to_boolean('true') from dual
*
ERROR at line 1:
ORA-00920: invalid relational operator
SQL> select (true and to_boolean('true')) from dual; -- enclosing expression still fails
select (true and to_boolean('true')) from dual
*
ERROR at line 1:
ORA-00907: missing right parenthesis
This restriction applies to your own pl/sql functions that return Boolean results.
SQL> create or replace function are_equal(a in number, b in number) return boolean
2 is
3 begin
4 return a=b;
5 end;
6 /
Function created.
SQL> select are_equal(1,2) f ,are_equal(1,1) t from dual;
F T
----------- -----------
FALSE TRUE
SQL> select b and are_equal(1,1) from testtable;
select b and are_equal(1,1) from testtable
*
ERROR at line 1:
ORA-00920: invalid relational operator
Boolean Evaluation
While you can, in many cases, simply use a Boolean value as its own condition, you can be explicit and check if a value is TRUE or FALSE with the IS condition. So if B is TRUE, then “B” and “B IS TRUE” should be equivalent expressions. Similarly, if B is FALSE then “B” and “B IS TRUE” should be equivalent expressions.
SQL> truncate table testtable; Table truncated. SQL> insert into testtable(b,bnn) values (true,true); 1 row created. SQL> insert into testtable(b,bnn) values (false,false); 1 row created. SQL> insert into testtable(b,bnn) values (null,true); 1 row created. SQL> select b from testtable where b; B ----------- TRUE SQL> select b from testtable where b is true; B ----------- TRUE SQL> select b from testtable where not b; B ----------- FALSE SQL> select b from testtable where b is false; B ----------- FALSE
Do note though, IS NOT, works with NULL values differently.
SQL> select b, nvl2(b,to_char(b),'~null~') b_text from testtable where b is not true;
B B_TEXT
----------- ------
FALSE FALSE
~null~
SQL> select b, nvl2(b,to_char(b),'~null~') b_text from testtable where b is not false;
B B_TEXT
----------- ------
TRUE TRUE
~null~
These operators work with PL/SQL functions as well as explicit conversion using TO_BOOLEAN.
SQL> SELECT are_equal(1, 1) is true,
2 to_boolean('yes') is true,
3 are_equal(1, 2) is true,
4 to_boolean('no') is true
5 FROM DUAL;
ARE_EQUAL(1 TO_BOOLEAN( ARE_EQUAL(1 TO_BOOLEAN(
----------- ----------- ----------- -----------
TRUE TRUE FALSE FALSE
They do not work with literals directly though, as they aren’t implicitly converted.
SQL> select 'yes' is true from dual;
select 'yes' is true from dual
*
ERROR at line 1:
ORA-61803: argument of IS TRUE must be a column or an expression of boolean data type
I hope this helps you get introduced to working with Boolean values in SQL with 23ai and beyond. Questions and comments, as always, are welcome.