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?
10 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.
answered Oct 12, 2012 at 7:10
Vishwanath Dalvi
36.9k41 gold badges128 silver badges156 bronze badges
3 Comments
Vishwanath Dalvi
@JoeNorman Glad to help you but consider reading answers by Damien and others, they list the technical aspect.
Vishwanath Dalvi
@JoeGJoseph thanks dude.. well actually a day before i asked similar kinda question to a lady in my office and she refused to answer on her birthday. :P
gloomy.penguin
the way it makes sense in my head: null is a state... not a value. and a value cannot equal a state of being.
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)
answered Oct 12, 2012 at 7:10
Damien_The_Unbeliever
241k28 gold badges358 silver badges470 bronze badges
Comments
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
Comments
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)
Comments
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>')
Comments
You cannot use = for NULL instead you can use IS NULL
Comments
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
Comments
Actually NULL means UNKNOWN value So how we compare two UNKNOWN values.
Comments
In addition to using IS NULL, another way to match NULL in SQL Server, is the function ISNULL, https://learn.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql
SELECT *
FROM TABLE_A t1
INNER JOIN TABLE_B t2 ON ISNULL(t1.somecol, somevalue) = ISNULL(t2.somecol, somevalue)
where somevalue could be 'NULL' or 0 or whatever.
is null?"bla" != NULLis false, even if that NULL is from another column.