sql and null values

Question

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?

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

juergen d · Accepted Answer · 2014-11-03 20:35:39Z

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

answered Nov 3, 2014 at 20:29

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

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.

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.

Community · Accepted Answer · 2017-05-23 12:05:38Z

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