By now I’m sure you have seen the excitement due to the arrival (finally) of the new Boolean datatype in the Oracle database. If not, check out my video at the bottom of this post for a rapid fire introduction. But there is more to having a datatype to just storing it. We also want to be able to query it and use all the power of SQL to get the results we need. And with new datatypes, comes some cool new functions as well.
Let’s start with a copy of SCOTT.EMP
SQL> create table t as
2 select
3 empno,
4 ename,
5 deptno,
6 hiredate,
7 job
8 from scott.emp;
Table created.
I’m going to tweak a few values just so my examples cover all of the scenarios I want to cover.
SQL> update t
2 set hiredate = hiredate + 365
3 where empno in (7782,7839);
2 rows updated.
Let’s say I want a new Boolean column called LONGTERM which indicates if you have been a long and valued employee of this organisation. I’ll add the column and populate it based on the HIREDATE
SQL> alter table t add longterm boolean;
Table altered.
SQL> update t
2 set longterm = hiredate < date '1982-01-01';
14 rows updated.
SQL> select * from t
2 order by deptno, hiredate;
EMPNO ENAME DEPTNO HIREDATE JOB LONGTERM
---------- ---------- ---------- --------- --------- -----------
7934 MILLER 10 23-JAN-82 CLERK FALSE
7782 CLARK 10 09-JUN-82 MANAGER FALSE
7839 KING 10 17-NOV-82 PRESIDENT FALSE
7369 SMITH 20 17-DEC-80 CLERK TRUE
7566 JONES 20 02-APR-81 MANAGER TRUE
7902 FORD 20 03-DEC-81 ANALYST TRUE
7788 SCOTT 20 09-DEC-82 ANALYST FALSE
7876 ADAMS 20 12-JAN-83 CLERK FALSE
7499 ALLEN 30 20-FEB-81 SALESMAN TRUE
7521 WARD 30 22-FEB-81 SALESMAN TRUE
7698 BLAKE 30 01-MAY-81 MANAGER TRUE
7844 TURNER 30 08-SEP-81 SALESMAN TRUE
7654 MARTIN 30 28-SEP-81 SALESMAN TRUE
7900 JAMES 30 03-DEC-81 CLERK TRUE
14 rows selected.
Whilst all of the standard “simple” SQL predicates will work fine on a Boolean like any other column, what about questions that are specific to the Boolean nature of this column. For example, what if I wanted to know if all of the employees per department were long-termers? It’s nice to discover that the standard aggregation functions work fine with a Boolean.
SQL> select
2 deptno,
3 max(longterm)
4 from t
5 group by deptno
6 order by 1;
DEPTNO MAX(LONGTER
---------- -----------
10 FALSE
20 TRUE
30 TRUE
But that doesn’t really answer my question. I can say from the above results that definitely department 10 has no long term employees (because FALSE is sorted lower than TRUE, hence the MAX aggregation means that all values are FALSE for department 10), but I need more information for departments 20 and 30, because some of the values are FALSE and some are TRUE. So I need to look at the MIN as well.
SQL> select
2 deptno,
3 max(longterm),
4 min(longterm)
5 from t
6 group by deptno
7 order by 1;
DEPTNO MAX(LONGTER MIN(LONGTER
---------- ----------- -----------
10 FALSE FALSE
20 TRUE FALSE
30 TRUE TRUE
I have all the information I need now, but it still takes more tinkering to convert into this to become the answer of “Are all employees long term?”
SQL> select
2 deptno,
3 case
4 when max(longterm) = min(longterm) and min(longterm) = false then 'ALL FALSE'
5 when max(longterm) = min(longterm) and max(longterm) = true then 'ALL TRUE'
6 else 'MIXED'
7 end result
8 from t
9 group by deptno
10 order by 1;
DEPTNO RESULT
---------- ---------
10 ALL FALSE
20 MIXED
30 ALL TRUE
Luckily, we can avoid having to resort to this kind of jiggery-pokery because we have a couple of brand new Boolean-specific aggregation functions in 23ai. The BOOLEAN_OR_AGG function will aggregate Boolean values in a logical “OR” fashion, thus returning TRUE if any row value is true, and BOOLEAN_AND_AGG will aggregate Boolean values in a logical “AND” fashion, returning TRUE only if all row values are true.
SQL> select
2 deptno,
3 boolean_or_agg(longterm) b_or,
4 boolean_and_agg(longterm) b_and
5 from t
6 group by deptno
7 order by 1;
DEPTNO B_OR B_AND
---------- ----------- -----------
10 FALSE FALSE
20 TRUE FALSE
30 TRUE TRUE
It becomes much easier and more intuitive now once we fold those functions into our query to get the results we wanted.
SQL> select
2 deptno,
3 case
4 when boolean_or_agg(longterm) = false then 'ALL FALSE'
5 when boolean_and_agg(longterm) = true then 'ALL TRUE'
6 else 'MIXED'
7 end result
8 from t
9 group by deptno
10 order by 1;
DEPTNO RESULT
---------- ---------
10 ALL FALSE
20 MIXED
30 ALL TRUE
SQL>
In case you were wondering, the thumbnail for this post it George Boole, the man we can thank for all of this 🙂
Learning is not a spectator sport 
Got some thoughts? Leave a comment