If you have ever tried to implement a requirement that implies that either one field is filled, or the other one but not both and not both not then you might have thought about using an XOR function. Unfortunately this is not implemented in Oracle SQL.

First of all, let’s review some of the truth tables that are implemented in SQL.

AND	left	right	result
	false	false	false
	false	true	false
	true	false	false
	true	true	true

OR	left	right	result
	false	false	false
	false	true	true
	true	false	true
	true	true	true

NOT	left	result
	false	true
	true	false

The way XOR should be implemented is:

XOR	left	right	result
	false	false	false
	false	true	true
	true	false	true
	true	true	false

In PL/SQL there is a function available that implements this truthtable. It’s in the standard package and looks like this:

desc sys.standard.XOR;
Parameter Type    Mode Default? 
--------- ------- ---- -------- 
(RESULT)  BOOLEAN               
LEFT      BOOLEAN IN            
RIGHT     BOOLEAN IN            

Let’s create a simple table and fill it up for testing:

create table t

(left  number

,right number);

insert into t (left, right) values (0,0);

insert into t (left, right) values (0,1);

insert into t (left, right) values (1,0);

insert into t (left, right) values (1,1);

commit;

An idea would be to just call this function in a query like this:

select * from t

where sys.standard.xor(((left <> 0)), ((right <> 0)))

But, unfortunately the boolean datatype has not been implemented in SQL, so this doesn’t work.

How to solve this issue then? We can of course write down all the rules in a where clause and make sure it return the correct results:

select * from t

where (

        (    ((left is not null) and (left <> 0))

         and ((right is null) or (right = 0))

        )

        or

        (    ((left is null) or (left = 0))

         and ((right is not null) and (right <> 0))

        )

       )

That is one ugly query. How can we rewrite the XOR function using just AND and OR function. The OR function gives almost everything we need, but it gives one result to many. And that is exactly the outcome of applying the AND function to our dataset. So:

XOR = a OR b minus (a AND b)

select * from t

where (   ((left is not null) and (left <> 0))

        or ((right is not null) and (right <> 0))

       )

minus

select * from t

where (    ((left is not null) and (left <> 0))

        and ((right is not null) and (right <> 0))

       )

or

XOR = a OR b and NOT (a AND b)

select * from t

where (   ((left is not null) and (left <> 0))

        or ((right is not null) and (right <> 0))

       )

       and not

       (    ((left is not null) and (left <> 0))

        and ((right is not null) and (right <> 0))

       )

I think these last two queries are more descriptive to what is being done. Which query to choose depends on how much data is in your table. In my example it doesn’t make any difference in time. I tested with a table with over 29500 rows and it appears the minus operation is a bit faster than the version with the extra predicate.

Rather simple code, but I hope it will help you a bit (it certainly helped me). I wrote this to make sure I don’t forget it 😉