Why in SQL NULL can't match with NULL?

I'm new to SQL concepts, while studying NULL expression I wonder why NULL can't match with NULL can anyone tell me a real world example to simply this concept?

Answers


Rule : Not even a NULL can be equal to NULL.

A Non-Technical aspect

If you ask two girls, how old they are? may be you would hear them to refuse to answer your question, Both girls are giving you NULL as age and this doesn't mean both have similar age. So there is nothing can be equal to null.


NULL indicates an absence of a value. The designers of SQL decided that it made sense that, when asked whether A (for which we do not know its value) and B (for which we do not know its value) are equal, the answer must be UNKNOWN - they might be equal, they might not be. We do not have adequate information to decide either way.

You might want to read up on Three valued logic - the possible results of any comparison in SQL are TRUE, FALSE and UNKNOWN (mysql treats UNKNOWN and NULL as synonymous. Not all RDBMSs do)


NULL is an unknown value. Therefore it makes little sense to judge NULL == NULL. That's like asking "is this unknown value equal to that unknown value" - no clue..

See why is null not equal to null false for a possibly better explaination


NULL is the absence of data in a field.

You can check NULL values with IS NULL

See IS NULL

mysql> SELECT NULL IS NULL;
+--------------+
| NULL IS NULL |
+--------------+
|            1 |
+--------------+

1 row in set (0.00 sec)

If you need to make a comparison where NULL does indeed equal NULL, you can use a pair of coalesce methods with a special default value. The easiest example is on a nullable string column.

coalesce(MiddleName,'')=coalesce(@MiddleName,'')

This comparison returns true if @MiddleName variable has a null value and the MiddleName column also has a null value. Of course, it also matches empty strings too. If that is an issue, you could change the special value to something silly, like

coalesce(MiddleName,'<NULL_DEFAULT>')=coalesce(@MiddleName,'<NULL_DEFAULT>')

You cannot use = for NULL instead you can use IS NULL

http://www.w3schools.com/sql/sql_null_values.asp


Please folllow the link

The NULL value is never true in comparison to any other value, even NULL.

To check we can use

Is Null or Not Null operators ..

Document

Correct me if 'm wrong


In SQL the WHERE clause only includes the value if the result of the expression is equal to TRUE. (This is not the same as "if the result of the expression is not FALSE")

Any binary operation in SQL that has NULL on one side evaluates to NULL (think of NULL as being a synonym for unknown)

so

Select ... Where null = null

Select ... Where field = null

Select ... Where null = field

Will all return no rows as in each case the where class evaluates to NULL which is not TRUE


Actually NULL means UNKNOWN value So how we compare two UNKNOWN values.


Need Your Help

How to get MAC address of client using PHP?

php ip

How can I get MAC Address using PHP or javascript...

Why does 'top | grep > file' not work?

linux shell io-redirection

I tested the following command, but it doesn't work.