XOR in SQL

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 😉

4 thoughts on “XOR in SQL

  1. 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


  2. 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


  3. 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


  4. 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


Leave a Reply

Your email address will not be published. Required fields are marked *