I had a table that was created like this

CREATE TABLE employee (
  id INT, 
  name VARCHAR(20)
);

And let's say we inserted some data into it.

INSERT INTO employee VALUES(1, 'John');

Later on, we introduced another column of type enum.

ALTER TABLE employee
    ADD COLUMN type1 ENUM ('REGULAR', 'PART_TIME');

Now, we inserted few more rows

INSERT INTO employee VALUES(1, 'Dave', 'REGULAR');
INSERT INTO employee VALUES(1, 'Bob', 'PART_TIME');

Now, the data in the table looks like this

'1', 'John', NULL
'2', 'Dave', 'REGULAR'
'1', 'Bob', 'PART_TIME'

If I run below query, I was thinking I should get rows 1 and 3.

SELECT * FROM employee where type1 != 'REGULAR'

But this returns only one row

'1','Bob','PART_TIME'

I tried this as well

SELECT * FROM employee where type1 != 'REGULAR' or type1 = NULL;

But this gave the same result.

1. Shouldn't the first query work as expected logically?
2. What can we way to write query to include NULL value rows as well?

CodePudding user response：

SELECT * FROM employee where type1 != 'REGULAR' or type1 is null;

SELECT * FROM employee where not(type1 <=> 'REGULAR')