MySql not returning results as expected

delete FROM tua开发者_StackOverflown_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 ;

---

null is never included in these comparisons, use is null instead.

---

http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html

You can also add the or condition in your query