Could someone please explain the following behavior in SQL?
SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results)
SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results)
SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)
<>
is Standard SQL-92; !=
is its equivalent. Both evaluate for values, which NULL
is not -- NULL
is a placeholder to say there is the absence of a value.
Which is why you can only use IS NULL
/IS NOT NULL
as predicates for such situations.
This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.
Note: To compare if your value is not null, you use IS NOT NULL
, while to compare with not null value, you use <> 'YOUR_VALUE'
. I can't say if my value equals or not equals to NULL, but I can say if my value is NULL or NOT NULL. I can compare if my value is something other than NULL.
<>
that is in the 92 spec but most vendors support !=
and/or it is included in a later spec like 99 or 03.
!=
until ~9i as I understand, which brought in a lot of ANSI-92 syntax. My belief is MySQL is similar, starting support in 4.x.
Commented
Apr 14, 2011 at 4:46
!=
might have been included in a later spec as an alternate to <>
. Don't have my hands on new specs so I can't say for sure.
WHERE MyColumn != NULL
or WHERE MyColumn = NULL
deterministic? Or in other words, is it guaranteed to always return 0 rows, no matter if MyColumn
is nullable in the database or not?
!=
only evaluates for values, doing something like WHERE MyColumn != 'somevalue'
will not return the NULL records.
NULL has no value, and so cannot be compared using the scalar value operators.
In other words, no value can ever be equal to (or not equal to) NULL because NULL has no value.
Hence, SQL has special IS NULL and IS NOT NULL predicates for dealing with NULL.
'a' != null
NOT returning a value (true
/1
) is counter intuitive and catches me out from time to time! I'd have thought "some value compared to no value" would always be "not equal", but maybe that's just me?!?
Commented
Jun 23, 2014 at 14:56
SELECT * FROM MyTable WHERE coalesce(MyColumn, 'x') <> 'x'
to assign a constant if it is NULL value, providing you give an appropriate datatype for the sentinel value x (in this case a string/char). This is TSQL syntax but Oracle and other engines have similar features.
Commented
Jun 27, 2017 at 20:25
Note that this behavior is the default (ANSI) behavior.
If you:
SET ANSI_NULLS OFF
http://msdn.microsoft.com/en-us/library/ms188048.aspx
You'll get different results.
SET ANSI_NULLS OFF
will apparently be going away in the future...
create unique index UK_MyTable on MyTable (Column) where Column is not null
): msdn.microsoft.com/en-us/library/cc280372.aspx
Commented
May 10, 2014 at 23:14
SET ANSI_NULLS
is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL
returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL
returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value
returns all rows that are not XYZ_value and that are not NULL. IMHO, this last statement seems a little odd in it's exclusion of nulls from the results!
Commented
Jun 23, 2014 at 15:06
We use
SELECT * FROM MyTable WHERE ISNULL(MyColumn, ' ') = ' ';
to return all rows where MyColumn is NULL or all rows where MyColumn is an empty string. To many an "end user", the NULL vs. empty string issue is a distinction without a need and point of confusion.
In SQL, anything you evaluate / compute with NULL
results into UNKNOWN
This is why SELECT * FROM MyTable WHERE MyColumn != NULL
or SELECT * FROM MyTable WHERE MyColumn <> NULL
gives you 0 results.
To provide a check for NULL
values, isNull function is provided.
Moreover, you can use the IS
operator as you used in the third query.
The only test for NULL is IS NULL or IS NOT NULL. Testing for equality is nonsensical because by definition one doesn't know what the value is.
Here is a wikipedia article to read:
null
represents no value or an unknown value. It doesn’t specify why there is no value, which can lead to some ambiguity.
Suppose you run a query like this:
SELECT *
FROM orders
WHERE delivered=ordered;
that is, you are looking for rows where the ordered
and delivered
dates are the same.
What is to be expected when one or both columns are null?
Because at least one of the dates is unknown, you cannot expect to say that the 2 dates are the same. This is also the case when both dates are unknown: how can they be the same if we don’t even know what they are?
For this reason, any expression treating null
as a value must fail. In this case, it will not match. This is also the case if you try the following:
SELECT *
FROM orders
WHERE delivered<>ordered;
Again, how can we say that two values are not the same if we don’t know what they are.
SQL has a specific test for missing values:
IS NULL
Specifically it is not comparing values, but rather it seeks out missing values.
Finally, as regards the !=
operator, as far as I am aware, it is not actually in any of the standards, but it is very widely supported. It was added to make programmers from some languages feel more at home. Frankly, if a programmer has difficulty remembering what language they’re using, they’re off to a bad start.
NULL
we mean that we are comparing a value to 'having a NULL
value', not the value to "the undetermined value that the underlaying NULL
is ¿having? but that we don't know", which obviously we are not going to be able to ever know. That would really ease things up.
IS NULL
is much more arduous than writing = NULL
. I think it would be more consistent if WHERE columnA = columnB
has the same interpretation as WHERE columnA = NULL
, rather than treat the latter as a special case. Remember that NULL
is not a value. In programming languages where it is legitimate to test variable == null
it is because null
has a different meaning; it doesn’t represent something unknown, but a deliberate resetting of a value. Not so with SQL.
IS NULL
AND =NULL
in your latest example. But take a look at Hover's last one. I'm tired of experiencing it again and again, having to do loads of ¿unnecessary? extra checking...
NULL Cannot be compared to any value using the comparison operators. NULL = NULL is false. Null is not a value. The IS operator is specially designed to handle NULL comparisons.
null = null
where one might use 1=0
in some ad-hoc query. And if they complain, I change it to null != null
:)
I would like to suggest this code I made to find if there is a change in a value,
i
being the new value and d
being the old (although the order does not matter). For that matter, a change from value to null or vice versa is a change but from null to null is not (of course, from value to another value is a change but from value to the same it is not).
CREATE FUNCTION [dbo].[ufn_equal_with_nulls]
(
@i sql_variant,
@d sql_variant
)
RETURNS bit
AS
BEGIN
DECLARE @in bit = 0, @dn bit = 0
if @i is null set @in = 1
if @d is null set @dn = 1
if @in <> @dn
return 0
if @in = 1 and @dn = 1
return 1
if @in = 0 and @dn = 0 and @i = @d
return 1
return 0
END
To use this function, you can
declare @tmp table (a int, b int)
insert into @tmp values
(1,1),
(1,2),
(1,null),
(null,1),
(null,null)
---- in select ----
select *, [dbo].[ufn_equal_with_nulls](a,b) as [=] from @tmp
---- where equal ----
select *,'equal' as [Predicate] from @tmp where [dbo].[ufn_equal_with_nulls](a,b) = 1
---- where not equal ----
select *,'not equal' as [Predicate] from @tmp where [dbo].[ufn_equal_with_nulls](a,b) = 0
The results are:
---- in select ----
a b =
1 1 1
1 2 0
1 NULL 0
NULL 1 0
NULL NULL 1
---- where equal ----
1 1 equal
NULL NULL equal
---- where not equal ----
1 2 not equal
1 NULL not equal
NULL 1 not equal
The usage of sql_variant makes it compatible for variety of types
NULL is not anything...it is unknown. NULL does not equal anything. That is why you have to use the magic phrase IS NULL instead of = NULL in your SQL queries
You can refer this: http://weblogs.sqlteam.com/markc/archive/2009/06/08/60929.aspx