1
col1 col2
null null
aaaa null
bbbb null

col1=col2 - no rows returned

not (col1=col2) - following returned

aaaa null
bbbb null

Not (null) = null? so how is this being interpreted?

Paul
  • 608
  • 1
  • 9
  • 23
  • I think , Answer for this question is in: http://stackoverflow.com/a/1843460/4211782 – Iwo Nov 03 '14 at 20:30
  • Here is a good tutorial about `null` values: at http://www.guru99.com/null.html – M. Page Nov 03 '14 at 20:31
  • I just ran your second query in SQL fiddle, and it returns no rows: http://www.sqlfiddle.com/#!6/d41d8/22672. My best guess is that `null` is not `null` but `'null'` -- the string, that is. – Gordon Linoff Nov 03 '14 at 20:35

2 Answers2

1

Comparing with null results in unknown which is false.

Use the is operator

where col1 = col2
or (col1 is null and col2 is null)

I added the () only for readabiliy. and has stronger operator precendence than or.

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • no, null = null is null. null = a is also null. both are false then. I know the is operator, curious why null = null not returned. – Paul Nov 03 '14 at 20:41
  • 2
    Because NULL is not a known value. So if you have two unknowns you can't know if they are equal or not therefore they can't be equal. – Sean Lange Nov 03 '14 at 20:45
0

Please use isnull(col1,0)=isnull(col2,0) because can't compare null with logical operations.

see below link: SQL is null and = null

Community
  • 1
  • 1
Mojtaba Rezaie
  • 98
  • 1
  • 10