MySql does not return results as expected

delete FROM tuan_details where tuan_id<>14

This should keep only those rows where tuan_id equals 14, but the rows where tuan_id is null are also being preserved.

Why is it so?

---

NULL is special in SQL. The condition you have: tuan_id <> 14 will be TRUE only for values that are different than 14. For rows that tuan_id is NULL, the:

tuan_id <> 14

will be:

NULL <> 14

which evaluates to:

UNKNOWN

so these rows are not deleted. WHERE conditions are satisfied when they evaluate to TRUE. They are rejected when they evaluate to FALSE or UNKNOWN.

SQL uses a 3-valued logic

---

So, if you want to delete Nulls as well, you can use this statement:

DELETE
FROM tuan_details
WHERE tuan_id <> 14
   OR tuan_id IS NULL ;