Possible Duplicate:
what is “=null” and “ IS NULL”
Is there any difference between IS NULL and =NULL
What is the difference between
where x is null
and
where x = null
and why does the latter not work?
Asked
Active
Viewed 1.7e+01k times
170
Community
- 1
- 1
Sachin Kainth
- 45,256
- 81
- 201
- 304
-
8Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons - http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html – scibuff Mar 06 '12 at 10:29
-
'is null' works as a function as well. Check out: https://www.w3schools.com/sql/sql_isnull.asp – Channa Jul 14 '19 at 14:32
-
Which dbms are you using? There are several product specific answers below, getting upvotes. – jarlh Jun 02 '22 at 11:54
-
This is already asked question and various solution available , https://www.sqlservercentral.com/articles/understanding-the-difference-between-is-null-and-null – Arqam Rafay Dec 12 '22 at 07:34
4 Answers
187
In SQL, a comparison between a null value and any other value (including another null) using a comparison operator (eg =, !=, <, etc) will result in a null, which is considered as false for the purposes of a where clause (strictly speaking, it's "not true", rather than "false", but the effect is the same).
The reasoning is that a null means "unknown", so the result of any comparison to a null is also "unknown". So you'll get no hit on rows by coding where my_column = null.
SQL provides the special syntax for testing if a column is null, via is null and is not null, which is a special condition to test for a null (or not a null).
Here's some SQL showing a variety of conditions and and their effect as per above.
create table t (x int, y int);
insert into t values (null, null), (null, 1), (1, 1);
select 'x = null' as test , x, y from t where x = null
union all
select 'x != null', x, y from t where x != null
union all
select 'not (x = null)', x, y from t where not (x = null)
union all
select 'x = y', x, y from t where x = y
union all
select 'not (x = y)', x, y from t where not (x = y);
returns only 1 row (as expected):
TEST X Y
x = y 1 1
See this running on SQLFiddle
Bohemian
- 412,405
- 93
- 575
- 722
-
1Given `NULL = NULL` is not `true`, we can conclude that the semantic meaning of `null` is different on either side of the `=` operator. LHS it is a concept, RHS it a predicate. (i.e. "being unknown" vs "is unknown"). These, by nature, are not equal to each other. ▮ – Mardoxx Feb 05 '20 at 21:36
-
1No, the semantic meaning of NULL is just "unknown". It is not a value, and cannot be reasoned about as if it were. The reason NULL = NULL is not true is because you cannot compare things you have no knowledge of, which is what NULL represents. Is the number of stars in the universe equal to the number of grains of sand? Who knows? The answer is NULL (unknown). – bornfromanegg Sep 27 '22 at 09:24
91
It's important to note, that NULL doesn't equal NULL.
NULL is not a value, and therefore cannot be compared to another value.
where x is null checks whether x is a null value.
where x = null is checking whether x equals NULL, which will never be true
Curtis
- 101,612
- 66
- 270
- 352
-
1Null is a value, it's just unknown value. Like in programming languages variable can have value null. Furthermore you can compare if( varA != null). In sql though its treated differently like you pointed out. – broadband Feb 04 '16 at 11:24
-
@broadband: null is not a value, just like infinity is not a number. It's more of a philosophical thing though... Yes, you can assign "null" to a variable, but that just means you are unsetting or resetting that variable. 0 or "" would be a value, but null effectively means "this variable has no value". – Sygmoral Apr 30 '17 at 19:35
-
@Sygmoral internally when you write in c# string a = null, it means that you are setting value `null` to variable `a`. But interpretation for programer is like you said: 'this variable has no value'. Further more in sql null can have multiple interpretations or three-valued logic. – broadband Jul 26 '17 at 06:28
-
@Sygmoral if you look at Bohemian answer it states: `In SQL, a comparison between a null value`. He's also writing about value. – broadband Jul 26 '17 at 06:30
10
First is correct way of checking whether a field value is null while later won't work the way you expect it to because null is special value which does not equal anything, so you can't use equality comparison using = for it.
So when you need to check if a field value is null or not, use:
where x is null
instead of:
where x = null
Sarfraz
- 377,238
- 77
- 533
- 578
4
I think that equality is something that can be absolutely determined. The trouble with null is that it's inherently unknown. Null combined with any other value is null - unknown. Asking SQL "Is my value equal to null?" would be unknown every single time, even if the input is null. I think the implementation of IS NULL makes it clear.
