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 ðŸ˜‰

with truth_table as

( select 0 left, 0 right from dual

union all select 0, 1 from dual

union all select 1, 0 from dual

union all select 1, 1 from dual

)

select left, right, utl_raw.cast_to_binary_integer( utl_raw.bit_xor( utl_raw.cast_from_binary_integer( left ), utl_raw.cast_from_binary_integer( right ) ) ) xor

from truth_table

Anton

Hi Patrick,

Thanks for the explanation. I’ve grown quite fond of case constructs. So here is another go at it ðŸ˜‰

with truth_table as

( select 0 left, 0 right from dual

union all select 0, 1 from dual

union all select 1, 0 from dual

union all select 1, 1 from dual

)

select left, right,

case when

( case when left = 1 then 1 else 0 end

+ case when right = 1 then 1 else 0 end

) = 1 then 1

else 0

end case

from truth_table;

cheers, Jan

Or something like

with truth_table as

( select 0 left, 0 right from dual

union all select 0, 1 from dual

union all select 1, 0 from dual

union all select 1, 1 from dual

)

select left, right

, left + right – 2 * bitand( left, right ) xor

from truth_table

with truth_table as

( select 0 left, 0 right from dual

union all select 0, 1 from dual

union all select 1, 0 from dual

union all select 1, 1 from dual

)

select t.*, nvl2(nullif(left,right),1,0)

from truth_table t