SQL Standard

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 returns false 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

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:

expression1 XOR expression2

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:

select 1 xor 1 as res;

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


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

select 1 xor 0 as res;

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


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:

SELECT customer_id, first_name, email, active

FROM customer

WHERE (active XOR NOT active) = 1 limit 3;

This should return the matching records as follows:

A close up of a name Description automatically generated

There you have it!


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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list