How to Compare Values Which May Both Be Null in T-Sql

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 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

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

T-SQL - Comparing two variables values with possible NULL

How about the following:

DECLARE @x varchar(10) = 'b',
        @y varchar(10) = 'a';

SELECT CASE WHEN COALESCE(@x,'') = COALESCE(@y,'') THEN 'FALSE'
            ELSE 'True'
       END AS Test

Note that you may want to choose some random value for COALESCE(@x,'')... Something that you'd never find in your data.

Comparing a value to a NULL in t-SQL

You can't compare NULL with any other value, it will result in 'UNKNOWN'.

From msdn source

A value of NULL indicates that the value is unknown. A value of NULL
is different from an empty or zero value. No two null values are
equal. Comparisons between two null values, or between a NULL and any
other value, return unknown because the value of each NULL is unknown.

Easier Way to Compare Variables that might be Null SQL Server

Nothing equals NULL, not even another NULL.

You could do something like

if ISNULL(variableA, -1) <> ISNULL(variableB, -1)

ISNULL returns the second parameter if the first parameter is null; otherwise it returns the first parameter. Just be sure to use something for the second parameter of ISNULL that isn't going to occur in your dataset (i.e. don't use -1 if variableA or variableB could actually be -1).

If you are comparing strings, you might do something like

if isnull(variableA, '#lAsd9082<!') <> isnull(variableB, '#lAsd9082<!')

where #lAsd9082<! is just some garbage text that would be very unlikely to occur in your data.

Update

Nulls are a pain to work with, so why not avoid them? Can you assign a default value to your variables? If they are parameters in a stored procedure, assign them like this:

CREATE PROCEDURE uspYourProcedure @variableA int = -1, @variableB int = -1

If you are defining them yourself, do this:

DECLARE @variableA int = -1
DECLARE @variableB int = -1

Your comparisons become much simpler if you don't have to account for nulls.

Update

Ok, you're going to need to do a few checks of the variables, but you can put it into a function so that your code isn't so cluttered.

create function dbo.udfVariablesAreEqual(@a int, @b int)
returns bit
as
begin
    declare @result bit
    if @a is null and @b is null
        set @result = 1;
    else if @a is not null and @b is not null and @a = @b 
        set @result = 1;
    else
        set @result = 0;
    return @result
end

This function returns 1 only if both variables are null or both have the same non-null value. Otherwise it returns 0. See it in action here: http://sqlfiddle.com/#!3/ef4df/3

You might squeeze a little more performance out of the function if you use Blam's query instead:

create function dbo.udfVariablesAreEqual(@a int, @b int)
returns bit
as
begin
    declare @result bit
    if not ((@a is null and @b is null) or @a = @b )
      set @result = 0
    else 
      set @result = 1
    return @result
end

How to compare two columns with null values in different tables in Hive

select count(*) From tableA a join tableB b on a.ID=b.ID and coalesce(a.columnA,'1')!=B.columnA

It works when I use coalesce

Simplest way to compare column against a value that may (or not) be null

From Operators, and Parse-Affecting Attributes:

The IS and IS NOT operators work like = and != except when one or both
of the operands are NULL. In this case, if both operands are NULL,
then the IS operator evaluates to 1 (true) and the IS NOT operator
evaluates to 0 (false). If one operand is NULL and the other is not,
then the IS operator evaluates to 0 (false) and the IS NOT operator is
1 (true). It is not possible for an IS or IS NOT expression to
evaluate to NULL.

You can use the operator IS to compare non-null values as well:

SELECT * FROM a WHERE col1 IS $someValue;

SQL 2017 - Comparing values between two tables where certain values can be NULL

Well the two problems I see are this:

1. The WHERE clause at the bottom needs to have extra parenthesis to combine your ORs with your ANDs so that the order of precedence is correct:

     select 1 from compare_target t where
   
       s.InstrumentID = t.InstrumentID AND 
     (( s.Col1Source   = t.Col1Target ) OR (ISNULL(s.Col1Source, t.Col1Target) IS NULL))  AND
     (( s.Col2Source   = t.Col2Target ) OR (ISNULL(s.Col2Source, t.Col2Target) IS NULL))  AND
     (( s.Col3Source   = t.Col3Target ) OR (ISNULL(s.Col3Source, t.Col3Target) IS NULL)) 
   

2. When you make that change the one row that is returned has a NULL value in the Col2Source column. So when you try and build the string that you are sending to STRING_AGG it has a NULL in the middle of it. So the entire string will be NULL. So you will need to use ISNULL in either the subquery in your FROM clause or within the STRING_AGG()....or is suppose right where you had it commented out.

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 !=

---

Related Topics