Boolean Hell – Missed rows when grouping in SQL

I was recently asked about the concept of Boolean hell and thought it would be a good post to discuss. Most database professionals should be familiar with the concept even if they don’t know the exact name of it. I think the best way to explain this will be with an example so let’s have a look at the below query.

declare @table table ( maingroup varchar(100) ,  number int, number2 int)

insert into @table
select 'A' , 1 , 1
union all
select 'A' , 1 ,1
union all
select 'A' , 1,1
union all
select 'A' , 1,1
union all
select 'B' , 1 , 0
union all
select 'B' ,0, 1
union all
select 'C' , 1 , 0
union all 
select 'C' , 0, 1
union all
select 'C'  ,0, 0
union all
select 'D' , NULL, 0
union all
select 'D' , 1, NULL
union all
select 'E', 0 , 0

Let’s say in the above query we want to summarize the data by MainGroup such that we only fetch rows where both Number and Number2 columns are not equal to 0.

When thinking about it in a non-Boolean way the query often ends up as below, this is a very literal way of writing it. Simply put make sure that both Number and Number2 column values are not 0.

select maingroup , sum(number) , sum(number2)
from @table
where NOT  (number =0 AND number2=0 )
group by maingroup

In a very literal sense this should work, except that it doesn’t. If you run the above query you get the below result.

Just looking at the first posting we know that the only group where both Number and Number2 columns have 0 is the Group E. However we see that any Group with even one column having 0 gets ignored. This is because the AND in the where condition is a Logical AND not a literal one. The below table describes the Boolean output for a Logical AND operation.

As you can see from the above the row gets considered only if both Columns have non-zero values. In order to get the correct value we could rewrite the query as below

select maingroup , sum(number) , sum(number2)
from @table
where   (number !=0 AND number2 !=0 )
group by maingroup

The above query returns the below result. Here we find all the rows where Number and Number 2 have 0 and then find the compliment.

Those familiar with Transistor logic (Boolean logic) will immediate see that the Truth Table we want is basically the one for the OR logical operand.

The query can written as below

select maingroup , sum(number) , sum(number2)
from @table
where   (number !=0 OR number2 !=0 )
group by maingroup

The result will look as below

So keep in mind the AND, OR and NOT operators are logical and they work based on the truth table and not the actual values. Every input in the above operators are evaluated against a true or false condition not the actual value of 0.

Please Consider Subscribing

Subscribe