MySQL: SELECT all except when one column contains one particular value and another contains another specific value

Question

So I'm trying to do a select statement from this table:

+--------+-------+--------+
| LETTER | COLOR | NUMBER |
+--------+-------+--------+
| A      | red   | 1      |
| B      | red   | 2      |
| A      | black | 3      |
| C      | red   | 4      |
| B      | black | 5      |
| D      | red   | 6      |
| E      | red   | 7      |
| C      | black | 8      |
| C      | black | 9      |
| C      | red   | 10     |
+--------+-------+--------+

Basically, I want to do a "SELECT *", but skip rows in which the letter is C and the color is "black".

In other words, the results of the SELECT would be the entire table except for those two penultimate rows.

How would I construct that statement?

spencer7593 · Answer 1 · 2018-10-16T23:31:20.227

The most straightforward approach would be to negate the condition, using the NOT operator:

SELECT t.*
  FROM this_table t 
 WHERE NOT ( t.letter = 'C' AND t.color = 'black' )
       ^^^

Note that we enclose the condition in parens, so that the NOT applies to the result from the entire condition.

But this doesn't account for possible NULL values in the columns letter or color.

To more precisely match the specification (us not having information about whether NULL values are allowed) we can use the MySQL null-safe comparison <=> (spaceship) operator in place of the equality = comparison operator.

SELECT t.*
  FROM this_table t 
 WHERE NOT ( t.letter <=> 'C' AND t.color <=> 'black' )
       ^^^

The negation of the AND operator inside the parens would be an OR, so we could also write it like this:

SELECT t.*
  FROM this_table t 
 WHERE NOT ( t.letter <=> 'C' )
    OR NOT ( t.color  <=> 'black' )
       ^^^

If we want to make use of the inequality comparison operator (<>), we can get an equivalent result without using the NOT operator ...

SELECT t.*
  FROM this_table t 
 WHERE ( t.letter <> 'C'     OR t.letter IS NULL )
    OR ( t.color  <> 'black' OR t.color  IS NULL )

MySQL: SELECT all except when one column contains one particular value and another contains another specific value

1 Answers1