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?
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.
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
You cannot use = for NULL instead you can use IS NULL
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 ..
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)
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.