{"id":506,"date":"2011-03-16T08:42:03","date_gmt":"2011-03-16T08:42:03","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=506"},"modified":"2011-03-16T08:42:03","modified_gmt":"2011-03-16T07:42:03","slug":"","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=506","title":{"rendered":"XOR in SQL"},"content":{"rendered":"

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<\/a> SQL.<\/p>\n

<\/p>\n

First of all, let’s review some of the truth tables that are implemented in SQL.
\n <\/p>\n\n\n\n\n\n\n\n
AND<\/strong><\/td>\nleft<\/em><\/td>\nright<\/em><\/td>\nresult<\/em><\/td>\n<\/tr>\n
 <\/td>\nfalse<\/td>\nfalse<\/td>\nfalse<\/td>\n<\/tr>\n
 <\/td>\nfalse<\/td>\ntrue<\/td>\nfalse<\/td>\n<\/tr>\n
 <\/td>\ntrue<\/td>\nfalse<\/td>\nfalse<\/td>\n<\/tr>\n
 <\/td>\ntrue<\/td>\ntrue<\/td>\ntrue<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n\n\n\n\n\n\n\n
OR<\/strong><\/td>\nleft<\/em><\/td>\nright<\/em><\/td>\nresult<\/em><\/td>\n<\/tr>\n
 <\/td>\nfalse<\/td>\nfalse<\/td>\nfalse<\/td>\n<\/tr>\n
 <\/td>\nfalse<\/td>\ntrue<\/td>\ntrue<\/td>\n<\/tr>\n
 <\/td>\ntrue<\/td>\nfalse<\/td>\ntrue<\/td>\n<\/tr>\n
 <\/td>\ntrue<\/td>\ntrue<\/td>\ntrue<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n\n\n\n\n\n
NOT<\/strong><\/td>\nleft<\/em><\/td>\nresult<\/em><\/td>\n<\/tr>\n
 <\/td>\nfalse<\/td>\ntrue<\/td>\n<\/tr>\n
 <\/td>\ntrue<\/td>\nfalse<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

The way XOR should be implemented is: <\/p>\n\n\n\n\n\n\n\n
XOR<\/strong><\/td>\nleft<\/em><\/td>\nright<\/em><\/td>\nresult<\/em><\/td>\n<\/tr>\n
 <\/td>\nfalse<\/td>\nfalse<\/td>\nfalse<\/td>\n<\/tr>\n
 <\/td>\nfalse<\/td>\ntrue<\/td>\ntrue<\/td>\n<\/tr>\n
 <\/td>\ntrue<\/td>\nfalse<\/td>\ntrue<\/td>\n<\/tr>\n
 <\/td>\ntrue<\/td>\ntrue<\/td>\nfalse<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

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

desc sys.standard.XOR;\r\nParameter Type    Mode Default? \r\n--------- ------- ---- -------- <\/span>\r\n(RESULT)  BOOLEAN               \r\nLEFT      BOOLEAN IN            \r\nRIGHT     BOOLEAN IN            <\/pre>\n
Let’s create a simple table and fill it up for testing:
\n  
create table t
\n    
(left  number <\/p>\n
,right number); <\/p>\n
insert into t (left, right) values (0,0); <\/p>\n
insert into t (left, right) values (0,1); <\/p>\n
insert into t (left, right) values (1,0); <\/p>\n
insert into t (left, right) values (1,1); <\/p>\n
commit;<\/font><\/p>\n
An idea would be to just call this function in a query like this:
\n  
select * from t
\n    
where sys.standard.xor(((left <> 0)), ((right <> 0)))<\/font><\/p>\n
But, unfortunately the boolean datatype has not been implemented in SQL, so this doesn’t work.
\n  
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: <\/p>\n
select * from t
\n    
where ( <\/p>\n
        (    ((left is not null) and (left <> 0)) <\/p>\n
         and ((right is null) or (right = 0)) <\/p>\n
        ) <\/p>\n
        or <\/p>\n
        (    ((left is null) or (left = 0)) <\/p>\n
         and ((right is not null) and (right <> 0)) <\/p>\n
        ) <\/p>\n
       )<\/font><\/p>\n
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:
\n  
XOR = a OR b minus (a AND b) <\/p>\n
select * from t
\n    
where (   ((left is not null) and (left <> 0)) <\/p>\n
        or ((right is not null) and (right <> 0)) <\/p>\n
       ) <\/p>\n
minus <\/p>\n
select * from t <\/p>\n
where (    ((left is not null) and (left <> 0)) <\/p>\n
        and ((right is not null) and (right <> 0)) <\/p>\n
       )<\/font><\/p>\n
or
\n  
XOR = a OR b and NOT (a AND b) <\/p>\n
select * from t
\n    
where (   ((left is not null) and (left <> 0)) <\/p>\n
        or ((right is not null) and (right <> 0)) <\/p>\n
       ) <\/p>\n
       and not <\/p>\n
       (    ((left is not null) and (left <> 0)) <\/p>\n
        and ((right is not null) and (right <> 0)) <\/p>\n
       )<\/font><\/p>\n
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.
\n  
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 \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5,9],"tags":[],"class_list":["post-506","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/506","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=506"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/506\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=506"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=506"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=506"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}