In short words, for two Boolean values, the XOR operator returns true if they are different. It is that simple.
- true XOR false returns true
- false XOR false returns false
- true XOR true returns false
Let us explore what the XOR operator in SQL does and how we can use it. For demonstration purposes, we use MySQL as the base database system.
SQL XOR Operator
In SQL, the XOR operator allows us to perform the logical XOR operations between two Boolean expressions.
Like any XOR operation, the operator returns a Boolean true if exactly one of the expressions is true and returns a Boolean false otherwise.
MySQL supports the XOR operator which allows us to write the complex conditional statements based on this logic.
The basic syntax is as follows:
Let us explore some basic usage of this functionality.
Basic Usage
Consider the following example that demonstrates how the XOR operator behaves in MySQL when evaluating two Boolean expressions:
In this case, MySQL treats 1 as true and 0 as false. Hence, since both expressions are true, the operator returns false as follows:
---+
0|
The functionality of the operator is preserved when one of the expression or operand is true. An example is as follows:
In this case, since only exactly one value is true, the operator returns true as follows:
---+
1|
Advanced Usage
Let us look at a more advanced example usage of the XOR operator using a database table. For this one, we use the “customer” table from the Sakila sample database.
Suppose we want to retrieve a list of customers who are either active or inactive members but not both from the customer table.
In this case, the active status is represented by 1 and the non-active status is represented by the value of 0.
We can use this in conjunction with the XOR operator to achieve this. Consider the following example query:
FROM customer
WHERE (active XOR NOT active) = 1 limit 3;
This should return the matching records as follows:
There you have it!
Conclusion
In this tutorial, we learned how to work with and use the XOR operator in SQL by covering the various functionality and usage. We also looked at how we can use it in a database table to filter for specific records.
