15

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?

Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155
Joe Norman
  • 153
  • 1
  • 1
  • 4

10 Answers10

55

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.

Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155
17

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)

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
4

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

Community
  • 1
  • 1
Prescott
  • 7,312
  • 5
  • 49
  • 70
4

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)
Quicksilver
  • 2,546
  • 3
  • 23
  • 37
2

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>')
raider33
  • 1,633
  • 1
  • 19
  • 21
0

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

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

Shabarinath Volam
  • 789
  • 5
  • 19
  • 48
0

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

thar45
  • 3,518
  • 4
  • 31
  • 48
0

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

Stephen Connolly
  • 13,872
  • 6
  • 41
  • 63
0

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

bimal
  • 41
  • 1
0

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.

vekinox
  • 41
  • 4