CODEWITHSUNDEEP
mysqlenums
Rajesh
Rajesh

Reputation: 336

MYSQL "not equal to" on enum

I created a table with an enum column as follow

create table test_1(
    id BIGINT NOT NULL AUTO_INCREMENT,
    order_billing_status ENUM ("BILLING_IN_PROGRESS") DEFAULT NULL
);

I insert two values as follow

+-----+----------------------+
| id  | order_billing_status |
+-----+----------------------+
| 100 | NULL                 |
| 200 | BILLING_IN_PROGRESS  |
+-----+----------------------+

Now when I try to query like select * from test_1 where order_billing_status <> "BILLING_IN_PROGRESS";, it is returning empty result, instead of returning the following.

+-----+----------------------+
| id  | order_billing_status |
+-----+----------------------+
| 100 | NULL                 |
+-----+----------------------+

Is this a bug in mysql or am I doing something wrong? If it's a bug, is there a work around or should I just use varchar instead of enum?

Upvotes: 2

Views: 4160

Answers (4)

Man
Man

Reputation: 772

Try this:

select * from  test_1 where order_billing_status != "BILLING_IN_PROGRESS";

Upvotes: 0

stackFan
stackFan

Reputation: 1608

The problem is that <> ignoresnull. Just use keywords like is not null or is null to check for null values.

Upvotes: 0

Mittal Patel
Mittal Patel

Reputation: 2762

For the NULL value check we need to use the IS NULL or IS NOT NULL

= or <> ignores the NULL

select * from  test_1 
where order_billing_status <> "BILLING_IN_PROGRESS"  OR order_billing_status IS NULL

Upvotes: 8

cdaiga
cdaiga

Reputation: 4939

Try this instead:

select * from  test_1 
where order_billing_status<>'BILLING_IN_PROGRESS'
or order_billing_status is null;

Upvotes: 1

Related Questions