sql - MySQL - different behaviour for = "" and != "" with regards to null -

this may silly question, null seems neither equal nor unequal empty string "".

i have table following values:

id field1 field2 field3 1          b     c 2   null     b     c 3            b     c 4          b     c 

my query

select * table field1 = "" 

does not return row 2 value of field1 null. makes absolute sense, null not equal empty string.

but,

select * table field1 != ""  

doesn't return row 2 either.

does have explanation historic origin of this? because value null means not know value , hence unknown whether field1 equal or unequal empty string row 2?

is because value null means not know value , hence unknown whether field1 equal or unequal empty string row 2?

you correct. whenever perform comparison against null, result null.

you can think of null meaning "unknown". when null stored in column record, doesn't mean doesn't have value, means hasn't been entered in database.

for example, might have "person" record, "date of birth" column. if value null, doesn't mean person wasn't born. means hasn't been entered, so, according database, person's birth date "unknown".

if don't know person's date of birth, can't answer either of these questions:

was person born on april 1st?

was person not born on april 1st?

the answer both "unknown".

you can't answer:

was person born after april 1st?

was person not born before april 1st?

whenever compare known value against "unknown", answer going "unknown".

further, if 2 people's dates of birth both null or "unknown", can't answer these:

were 2 people born on same day?

were 2 people not born on same day?

was person 1 born after person two?

was person 2 born after person one?

whenever compare "unknown" value "unknown" value, answer "unknown".

comparing against "unknown" value yields "unknown".

you can, however, answer following:

do know person's date of birth?

do not know person's date of birth?

to ask question in mysql, use is not null , is null.