One of the key ingredients to writing effective SQL queries is the ability to articulate a wide variety of conditions using SQL syntax. One condition that gives both newbies and experienced database developers pause for thought is the Exclusive OR. Software programmers tend to be more familiar with the syntax for the Exclusive OR condition, probably because most programming languages support the XOR logical operator, whereas many databases do not.
In simple terms, the Exclusive OR condition is similar to the regular OR, except that, in the case of the Exclusive OR, only one of the compared operands may be true, and not both. In this blog, we'll learn how to express an Exclusive OR condition for a variety of databases, whether they support the XOR operator, or not.
Using the XOR Operator
Some popular relational databases, such as MySQL, support the XOR operator, making writing Exclusive OR conditions fairly trivial. To illustrate, let's consider a scenario where we need to find customers that reside within a specific city, or whose account was created after a specific date, but not both. More specifically, say that we want to see customers who reside in Lethbridge, Alberta, OR, if they do not reside in Lethbridge, whose account was created after Jan 1st, 2020.
Here's just such a query, executed against the Sakila Sample Database using Navicat Premium 16:
Looking at the results, we can see that the first customer, whose account was created on 2020-07-07, has a store_id of 2, while the rest of the customers, all have a store_id of 1 (the Lethbridge store).
Meanwhile, if we replace the XOR to a regular OR, we now see customers who shop at store #1 whose accounts were also created after 2020-01-01:
Allowing both operands to evaluate to TRUE is what differentiates OR from XOR.
Writing an Exclusive OR Condition Where XOR Is Not Supported
Luckily, it's not that hard to formulate an Exclusive OR condition without the XOR operator; you just need to think about it a bit more. Mathematically speaking, x XOR y is equal to:
(x AND (NOT y)) OR ((NOT x) AND y)
We can simplify the above formula to the following for the purposes of SQL writing:
(A OR B) AND NOT (A AND B)
We'll try out this formula by rewriting the first query for SQL Server. If we try to execute it against that database, we get the following error, which states that SQL Server did not recognize the XOR operator:
Using the above formula, we can rewrite the XOR condition as:
WHERE (ci.city = 'Lethbridge' OR c.create_date > '2020-01-01') AND NOT (ci.city = 'Lethbridge' AND c.create_date > '2020-01-01')
Here are the results in SQL Server (note that the data in both databases is not identical):
Conclusion
In today's blog, we learned how to articulate an Exclusive OR condition in a variety of databases, both using the XOR operator, and without.
If you'd like to give Navicat 16 a test drive, you can download a 14 day trial here.
