Issues with SQL Comparison and Null Values

SQL Server Compare to NULL

I encountered the same problem with you when taking comparison with nullable value, NULL always returns unknown as far away of our desired only between TRUE or FALSE

I ended up with declare a Scalar-valued functions with these logics like other SQL(s) dealing with null as

Ordinary comparison operators yield null (signifying "unknown"), not
true or false, when either input is null. For example, 7 = NULL yields
null, as does 7 <> NULL. When this behavior is not suitable, use the
IS [ NOT ] DISTINCT FROM constructs:

a IS DISTINCT FROM b => a != b
a IS NOT DISTINCT FROM b => a == b

Which a IS NOT DISTINCT FROM b could be rewritten as

(a IS NOT NULL AND b IS NOT NULL AND a=b) OR (a IS NULL AND b is NULL)

I use sql_variant for these basic parameters: int, datetime, varchar,...

create function IsEqual(
@a sql_variant,
@b sql_variant
)
returns bit
as
begin
return (CASE WHEN (@a IS NOT NULL AND @b IS NOT NULL AND @a=@b) OR (@a IS NULL AND @b is NULL) THEN 1 ELSE 0 END);
end

create function IsNotEqual(
@a sql_variant,
@b sql_variant
)
returns bit
as
begin
return 1-dbo.IsEqual(@a,@b);
end

To use

select dbo.IsEqual(null, null) Null_IsEqual_Null,
dbo.IsEqual(null, 1) Null_IsEqual_1,
dbo.IsEqual(1, null) _1_IsEqual_Null,
dbo.IsEqual(1, 1) _1_IsEqual_1,
dbo.IsEqual(CAST('2017-08-25' AS datetime), null) Date_IsEqual_Null,
dbo.IsEqual(CAST('2017-08-25' AS datetime), CAST('2017-08-25' AS datetime)) Date_IsEqual_Date

Result

For your cases

select dbo.IsNotEqual(123,123) _123_IsNotEqual_123,
dbo.IsNotEqual(5,123) _5_IsNotEqual_123,
dbo.IsNotEqual(Null,123) Null_IsNotEqual_123,
dbo.IsNotEqual(123,Null) _123_IsNotEqual_Null,
dbo.IsNotEqual(Null,Null) Null_IsNotEqual_Null

Sample Image

Comparing empty string with null value - SQL Server

Your first example returns fail because you have the wrong operator. If you want to see if something equals something else you use =, not !=

Here is the code that proves that NULL can be compared to '':

DECLARE @EmptyString VARCHAR(20) = '',
@Null VARCHAR(20) = Null;

SELECT
CASE WHEN ISNULL(@EmptyString, '') = ISNULL(@Null, '')
THEN 'Pass' ELSE 'Fail'
END AS EmptyStringVsNull

It returns pass because you use =, not !=

Comparing a parameter against null values

This should work:

SELECT * FROM ApplicationData
WHERE (ApplicationId IS NULL AND @AppID IS NULL) OR ApplicationId = @AppID

This is an alternate approach:

SELECT * FROM ApplicationData 
WHERE ISNULL(ApplicationId, -1) = ISNULL(@AppID, -1)

MySQL comparison with null value

In MySQL, NULL is considered as a 'missing, unknown value', as opposed to no value. Take a look at this MySQL Reference on NULL.

Any arithmetic comparison with NULL does not return true or false, but returns NULL instead., So, NULL != 'C' returns NULL, as opposed to returning true.

Any arithmetic comparison with 'NULL' will return false. To check this in SQL:

SELECT IF(NULL=123,'true','false') 

To check NULL values we need to use IS NULL & IS NOT NULL operator.

How to compare values which may both be null in T-SQL

Use INTERSECT operator.

It's NULL-sensitive and efficient if you have a composite index on all your fields:

IF      EXISTS
(
SELECT MY_FIELD1, MY_FIELD2, MY_FIELD3, MY_FIELD4, MY_FIELD5, MY_FIELD6
FROM MY_TABLE
INTERSECT
SELECT @IN_MY_FIELD1, @IN_MY_FIELD2, @IN_MY_FIELD3, @IN_MY_FIELD4, @IN_MY_FIELD5, @IN_MY_FIELD6
)
BEGIN
goto on_duplicate
END

Note that if you create a UNIQUE index on your fields, your life will be much simpler.

SQL Server: Why does comparison null=value return true for NOT IN?

Common problem, canned answer:

The behavior of NOT IN clause may be confusing and as such it needs some explanations. Consider the following query:

SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL)

Although there are more than a thousand distinct last names in AdventureWorks.Person.Contact, the query returns nothing. This may look counterintuitive to a beginner database programmer, but it actually makes perfect sense. The explanation consist of several simple steps. First of all, consider the following two queries, which are clearly equivalent:

SELECT LastName, FirstName FROM Person.Contact

WHERE LastName IN('Hedlund', 'Holloway', NULL)

SELECT LastName, FirstName FROM Person.Contact

WHERE LastName='Hedlund' OR LastName='Holloway' OR LastName=NULL

Note that both queries return expected results. Now, let us recall DeMorgan's theorem, which states that:

not (P and Q) = (not P) or (not Q)

not (P or Q) = (not P) and (not Q)

I am cutting and pasting from Wikipedia (http://en.wikipedia.org/wiki/De_Morgan_duality). Applying DeMorgan's theorem to this queries, it follows that these two queries are also equivalent:

SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL)

SELECT LastName, FirstName FROM Person.Contact

WHERE LastName<>'Hedlund' AND LastName<>'Holloway' AND LastName<>NULL

This last LastName<>NULL can never be true

Comparisons with NULLs in SQL

Here is a nice comparison of null handling in SQLite, PostgreSQL, Oracle, Informix, DB2, MS-SQL, OCELOT, MySQL 3.23.41, MySQL 4.0.16, Firebird, SQL Anywhere, and Borland Interbase

Why does NULL = NULL evaluate to false in SQL server

Think of the null as "unknown" in that case (or "does not exist"). In either of those cases, you can't say that they are equal, because you don't know the value of either of them. So, null=null evaluates to not true (false or null, depending on your system), because you don't know the values to say that they ARE equal. This behavior is defined in the ANSI SQL-92 standard.

EDIT:
This depends on your ansi_nulls setting. if you have ANSI_NULLS off, this WILL evaluate to true. Run the following code for an example...

set ansi_nulls off

if null = null
print 'true'
else
print 'false'

set ansi_nulls ON

if null = null
print 'true'
else
print 'false'


Related Topics



Leave a reply



Submit