Comparisons with Nulls in SQL

How to compare Null values from the database column

SET @Name = NULL;

UPDATE emp
SET name="gaurav"
WHERE    (@Name IS NULL     AND name IS NULL)
      OR (@Name IS NOT NULL AND name = @Name)

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

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.

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

sql compare fields with some nulls

Also include if field2 is NULL. You know the null isn't a match but want to show it.

SELECT * FROM table 
WHERE field1 != field2
    OR (field1 IS NULL AND field2 IS NOT NULL)
    OR (field1 IS NOT NULL AND field2 IS NULL)

Additionally, you can use COALESCE to assert the null as another value. I used 0 in this case. Only use 0 if there is no 0 for field1 or field2. Basically choose a value that won't happen in your table.

select * from table where COALESCE(field1, 0) != COALESCE(field2, 0)

Edit: OP asserted that field1 could be null, so I changed the first query. Query 1 is a bit more clear on what's going on, and Query 2 is a more concise way to achieve the same end result.

Read more here: COALESCE Function in TSQL

You can also use NVL() to deal with NULL like COALESCE, however it is less efficient to my understanding. More info: Oracle Differences between NVL and Coalesce