SQL XOR Operator

Exclusive OR, commonly known as XOR is one of the most popular and useful logical operators in SQL and other programming languages. XOR returns true when only one of the provided operands is true and false if otherwise.

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

SQL XOR Operator

In SQL, the XOR operator allows us to perform 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 Boolean false if otherwise.

MySQL does support the XOR operator, allowing us to write complex conditional statements based on this logic.

The basic syntax is as shown:

expression1 XOR expression2

Let us explore some basic usage of this functionality.

Basic Usage

Consider the example below that demonstrates how the XOR operator behaves in MySQL when evaluating two Boolean expressions.

select 1 xor 1 as res;

In this case, MySQL will treat 1 as true and 0 as false. Hence, since both expressions are true, the operator returns false as shown:

res|
---+
0|

The functionality of the operator is preserved when one of the expression or operand is true. An example is as shown:

select 1 xor 0 as res;

In this case, since only exactly one value is true, the operator returns true as shown:

res|
---+
1|

Advanced Usage

Let us look at a more advanced example usage of the XOR operator using a database table. For this one, we will 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 nonactive status is represented by the value 0.

We can use this in conjunction with the XOR operator to achieve this. Consider the example query shown below:

SELECT customer_id, first_name, email, active
FROM customer
WHERE (active XOR NOT active) = 1 limit 3;

This should return the matching records.

And there you have it.

Conclusion

In this tutorial, we learned how to work 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.