MySQL Tutorial/Logic Operator/XOR

Logical XOR (XOR)

"a XOR b" is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b).

Returns NULL if either operand is NULL.

For non-NULL operands, evaluates to 1 if an odd number of operands is non-zero, otherwise 0 is returned.

   <source lang="sql">

mysql> mysql> SELECT 1 XOR 1; +---------+ | 1 XOR 1 | +---------+ | 0 | +---------+ 1 row in set (0.00 sec) mysql> SELECT 1 XOR 0; +---------+ | 1 XOR 0 | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) mysql> SELECT 0 XOR 0; +---------+ | 0 XOR 0 | +---------+ | 0 | +---------+ 1 row in set (0.00 sec) mysql> SELECT 1 XOR 1 XOR 1; +---------------+ | 1 XOR 1 XOR 1 | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec)</source>

Using Logical XOR operator with NULL value

   <source lang="sql">

mysql> mysql> mysql> SELECT 1 XOR NULL; +------------+ | 1 XOR NULL | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql> SELECT NULL XOR NULL; +---------------+ | NULL XOR NULL | +---------------+ | NULL | +---------------+ 1 row in set (0.00 sec) mysql> mysql></source>

Using XOR to link two conditions

   <source lang="sql">

mysql> mysql> mysql> CREATE TABLE Books(

   ->    BookID SMALLINT NOT NULL PRIMARY KEY,
   ->    BookName VARCHAR(40) NOT NULL,
   ->    Category VARCHAR(15),
   ->    InStock SMALLINT NOT NULL,
   ->    OnOrder SMALLINT NOT NULL
   -> );

Query OK, 0 rows affected (0.03 sec) mysql> mysql> mysql> INSERT INTO Books VALUES (101, "Java", "Nonfiction", 12, 13),

   ->                          (102, "MySQL",          "Fiction",    17, 20),
   ->                          (103, "Oracle",         "Nonfiction", 23, 33),
   ->                          (104, "VB.net",         "Nonfiction", 32, 12),
   ->                          (105, "www.sqle.ru", "Fiction",    6,  35),
   ->                          (106, "Perl",           "Fiction",    28, 14),
   ->                          (107, "Php",             NULL,        46, 3);

Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> select * from Books; +--------+----------------+------------+---------+---------+ | BookID | BookName | Category | InStock | OnOrder | +--------+----------------+------------+---------+---------+ | 101 | Java | Nonfiction | 12 | 13 | | 102 | MySQL | Fiction | 17 | 20 | | 103 | Oracle | Nonfiction | 23 | 33 | | 104 | VB.net | Nonfiction | 32 | 12 | | 105 | www.sqle.ru | Fiction | 6 | 35 | | 106 | Perl | Fiction | 28 | 14 | | 107 | Php | NULL | 46 | 3 | +--------+----------------+------------+---------+---------+ 7 rows in set (0.00 sec) mysql> mysql> mysql> SELECT BookName, Category, InStock, OnOrder

   -> FROM Books
   -> WHERE Category="Fiction" XOR InStock IS NULL
   -> ORDER BY BookName;

+----------------+----------+---------+---------+ | BookName | Category | InStock | OnOrder | +----------------+----------+---------+---------+ | MySQL | Fiction | 17 | 20 | | Perl | Fiction | 28 | 14 | | www.sqle.ru | Fiction | 6 | 35 | +----------------+----------+---------+---------+ 3 rows in set (0.00 sec) mysql> drop table Books; Query OK, 0 rows affected (0.00 sec) mysql></source>