SQL Server Null Comparison between VARCHAR and CHAR

Question

Problem:

I have a table with 2 columns , one char(1) and another varchar(1).
I insert a row with NULLS
I compare the 2 columns values
I get that they are not equal
Why?

Code:

CREATE TABLE ss_1
    (
      [char] CHAR(1) ,
      [varchar] VARCHAR(3)
    )



INSERT  INTO TimeCurrent..ss_1
        ( char, varchar )
VALUES  ( NULL,-- char - char(1)
          NULL  -- varchar - varchar(3)
          )

SELECT  CASE WHEN S.char = S.varchar THEN 'yes'
             ELSE 'no'
        END AS eq2
FROM    ss_1 AS S

FIDDLE:

http://sqlfiddle.com/#!6/84bc4/2/0

NULL != NULL See this: http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server — n8wrl, May 13 '16 at 17:52
You could do this with 2 INTs and they are not equal too. UNKNOWN = UNKNOWN results in NOT EQUAL because it is unknown — juergen d, May 13 '16 at 17:53
@juergend, it is not because of unknown = unknown is not equal. — Giorgi Nakeuri, May 13 '16 at 17:56
Oh got it, sorry for the silly question. Got confused with Javascript where null==null is TRUE — Sajjan Sarkar, May 13 '16 at 18:06
One solution: *SELECT CASE WHEN **EXISTS(SELECT S.char INTERSECT SELECT S.varchar)** THEN 'yes' ELSE 'no' END AS eq2 FROM ss_1 AS S* — Bogdan Sahlean, May 14 '16 at 05:16

Giorgi Nakeuri · Accepted Answer · 2016-05-13T18:10:09.583

2

Actually this is because SQL is based on three valued logic where a predicate can be evaluated to 3 different values: TRUE, FALSE and UNKNOWN. When any part of the comparison is NULL the predicate evaluates to UNKNOWN. This is the formal definition. Example:

1 = 1 => TRUE
1 = 2 => FALSE
1 = NULL => UNKNOWN
NULL = NULL => UNKNOWN

Now when you write:

CASE WHEN predicate THEN

It will go to THEN only when predicate evaluates to TRUE. But in your case it evaluates to UNKNOWN. Thats why it goes to ELSE part.

edited May 13 '16 at 18:10

answered May 13 '16 at 18:07

Giorgi Nakeuri

35,155
8
47
75

Got it, thanks..I should have googled a bit more before posting. – Sajjan Sarkar May 13 '16 at 18:09

user2366842 · Answer 2 · 2016-05-13T18:14:20.500

CREATE TABLE ss_1
(
  [char] CHAR(1) ,
  [varchar] VARCHAR(3)
)



INSERT  INTO TimeCurrent..ss_1
    ( char, varchar )
VALUES  ( NULL,-- char - char(1)
      NULL  -- varchar - varchar(3)
      )

SELECT  CASE WHEN coalesce(S.char, '') = coalesce(S.varchar, '') THEN 'yes'
         ELSE 'no'
    END AS eq2
FROM    ss_1 AS S

Something like that might work for what you're after. As was mentioned, a direct comparison of null against another null value will never return as equal, as null simply means the absence of a value, so there's no value for the comparison to compare against. The input types have no bearing on this (this would be the same if both were char(1) or char(3), or some other data type for that matter).

EDIT: One thing to note, you'll want to replace the '' with some other (otherwise not legal) value if blank is a valid input in your database for that column, else you'll end up matching blank values in the database against null values, which may or may not be okay...

EDIT 2: This actually might be slightly better, and more reflective of your actual intentions:

CREATE TABLE ss_1
(
  [char] CHAR(1) ,
  [varchar] VARCHAR(3)
)



INSERT  INTO TimeCurrent..ss_1
    ( char, varchar )
VALUES  ( NULL,-- char - char(1)
      NULL  -- varchar - varchar(3)
      )

SELECT  CASE WHEN S.char = S.varchar THEN 'yes'
        CASE When S.char IS NULL and S.varchar IS NULL Then 'yes'

         ELSE 'no'
    END AS eq2
FROM    ss_1 AS S

thanks, i was just testing for myself, i got around it using isnull(col,'') on both sides.. but like the other posters said, in SQL NULL is unknown, unlike Javascript — Sajjan Sarkar, May 13 '16 at 18:08

SQL Server Null Comparison between VARCHAR and CHAR

2 Answers2