Using exclusive or in mysql

Question

I have a table below

Foobar table

|foo|bar| --------- | 1 | 1 | | 1 | 0 | | 0 | 1 | | 0 | 0 | 

I need to do something like this

 select * from foobar where foo = 1 or bar = 1 

To return below

 |foo|bar| --------- | 1 | 0 | | 0 | 1 | 

The value of that only 1 is returned for the value. Is there such a thing as XOR in mysql that works like this?

Answer 1

Yes, XOR. The MySQL XOR statement checks two operands (or expressions) and returns TRUE if one or the other, but not both, is TRUE.

 select * from foobar where foo = 1 XOR bar = 1 

The actual mathematical representation of the XOR equation " A XOR B " is " (A AND (NOT B)) OR ((NOT A) AND B) ".

Answer 2

try like this:

 select * from test where foo = 1 XOR bar = 1; 

Sql Fiddle: http://sqlfiddle.com/#!2/079cc/4

Answer 3

The fact is that SQL does not offer XOR by default. This is great what MySQL does because it is such a common feature. There is a simple way to “implement” XOR when you have two booleans:

 ... WHERE (foo = 1) = (bar = 0) 

This gives you the following:

 +-----+-----+-----+ | foo | bar | = | +-----+-----+-----+ | 0 | 0 | 0 | | 1 | 0 | 1 | | 0 | 1 | 1 | | 1 | 1 | 0 | +-----+-----+-----+ 

To invert the results of the = column, use the opposite <> operator as follows:

 ... WHERE (foo = 1) <> (bar = 0) 

Now you have the XNOR :

 +-----+-----+-----+ | foo | bar | <> | +-----+-----+-----+ | 0 | 0 | 1 | | 1 | 0 | 0 | | 0 | 1 | 0 | | 1 | 1 | 1 | +-----+-----+-----+ 

You can also configure the values ​​as in:

 ... WHERE (foo = 1) = (bar = 1) 

Therefore, if you use a cleaner SQL database such as PostgreSQL that does not offer logical XOR (as of 2019), this simple solution for you is much simpler than the full one:

 (A AND (NOT B)) OR ((NOT A) AND B) 

where you have to repeat A and B and this can be very expensive in terms of filtering your results, so it was probably decided not to include the XOR operator in SQL.