3

I mostly have application development background. In programming languages variable == null or variable != null works.

When it comes to SQL, below queries don't give any syntax errors but don't return correct results either.

select SomeColumn from SomeTable where SomeNullableColumn=null
select SomeColumn from SomeTable where SomeNullableColumn<>null

Why do we need to write the queries with is null or is not null to get correct results? 

select SomeColumn from SomeTable where SomeNullableColumn is null
select SomeColumn from SomeTable where SomeNullableColumn is not null

What's the reasons/requirements behind is null or is not null instead of =null or <>null?

Is this ANSI SQL or Microsoft's TSQL standard?

HappyTown
  • 6,036
  • 8
  • 38
  • 51
  • 3
    Because `NULL` is not a value, it means there is no value. You can't compare something with nothing. – TT. Dec 01 '16 at 18:46
  • Possible duplicate of [why is null not equal to null false](http://stackoverflow.com/questions/1833949/why-is-null-not-equal-to-null-false) – EoinS Dec 01 '16 at 18:56
  • 2
    yup duplicate of http://stackoverflow.com/questions/9581745/sql-is-null-and-null – Neo Dec 01 '16 at 18:58

2 Answers2

2

Because null is not equal to null, like NaN ("not a number" produced when, for example, 0 is divided by 0 or raised to the power of 0) is not equal to NaN. Consider null as an unknown value (an unknown value is not necessarily equal to some unknown value).

Kirill Bulygin
  • 3,658
  • 1
  • 17
  • 23
1

Because sql is based on set theory and predicate logic. This two strong mathematical foundations contains mathematical logic - Boolean algebra. In this logic exist three values - TRUE, FALSE and UNKNOWN (=NULL).

Is null is used instead of = null to separate logic. And why it is in ANSI standard? It is to make it more readable and separate three-valued logic.

In common datawarehouses is null replaced by '' to make qry more simple.

Or try SET ANSI_NULLS OFF option.

Deadsheep39
  • 561
  • 3
  • 16