default null values not get selected when != conditional operator is used

Question

I have below data with some null values present.
flag column has default values as NULL

test_table

Id flag
1   0
2   1
3   1
4   NULL
5   0
6   NULL

Now, when I write select query like

select Id from test_table where flag!=1

then it selects only data of ids 1 and 5 actually, it should select ids 1,4,5,6

why does this happen? whats the problem?

Answer 1

The correct query is

SELECT * FROM test_table WHERE flag != 1 OR flag IS NULL

This is simply due to the fact that you cant use the != operator (or similar ones like ==, <=, >=, etc.) with NULL values. You need to use the IS operator for this. Your query will only check the rows that have a value in the flag column, since it will simply ignore the NULL values, since those will not match anyway.

Answer 2

This question have already been asked and users give some good answer on this.

Please check below link:-

MySQL: selecting rows where a column is null

SQL NULL's special, and you have to do WHERE field IS NULL, as NULL cannot be equal to anything,