LokiSinclair LokiSinclair - 3 months ago 8x
SQL Question

MySQL where value NOT in SET datatype field

I should start by saying that I know how to check for the existence of a

datatype, however, I'm now trying to negate the lookup, which doesn't seem to work, so I'm assuming I'm doing something stupid.

I've got this field:

| billing_payment_prefs | set('allow_admin','allow_trans','allow_supress','requires_nag') | YES | | NULL | |

And all records are currently NULL for said field. I have 3000+ records in this table, and running the following query:

mysql> select count(id) from customers where billing_payment_prefs not like '%allow_trans%';
| count(id) |
| 0 |
1 row in set (0.00 sec)

... I get
instead of the 3000 plus (which I'd expect, as they're all NULL). Now, I'm obviously unsure of how to look up a
against a SET field, but I had assumed (incorrectly, by the looks of things) that this would work, even though MYSQL DEV doesn't mention it.

Any help, is very much appreciated. Thanks.


The reason you are getting no records back is because MySQL (as well as most RDBMS) treat NULL as a special value. Comparing some text against NULL does not yield a true or false, but rather yields NULL, because comparing an unknown value against a known value yields an unknown result.

To make the logic in your query work, you can add a condition for NULL checks:

FROM customers
WHERE billing_payment_prefs NOT LIKE '%allow_trans%' OR
      billing_payment_prefs IS NULL