2

Why select * from [table] where [table].[column] != null is not working for my query and result is empty.

But select * from [table] where [table].[column] is not null is working and find records that [table].[column] is fill with null!

What is the difference between != null and is not null in Microsoft SQL Server?

JJJ
  • 32,902
  • 20
  • 89
  • 102
Mohamad Shiralizadeh
  • 8,329
  • 6
  • 58
  • 93
  • 1
    `is not null` return records **fill with `null`**? `is not null` is standard, and you cannot compare `null` with `!=` – Prisoner Oct 31 '16 at 07:06
  • 1
    You can't compare to something that is not a value. NULL stands for the absence of a value. You can't compare something with nothing. The `IS NULL` or `IS NOT NULL` stands for `has a value` or `does not have a value`. – TT. Oct 31 '16 at 07:06

2 Answers2

5

NULL in a database is not a value. It means something like "unknown" or "data missing".

You cannot tell if something where you don't have any information about is equal to something else where you also don't have any information about (=, != operators). But you can say whether there is any information available (IS NULL, IS NOT NULL).

IMHO, in practice it is not very useful. But it is how it is and has a long history.

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
2

You cannot compare with NULL using = operator because NULL means unknown value and so you cannot equate something to some unknown value. That is why IS NULL clause is provided to filter out rows having column as null.

Read more about THREE VALUED LOGIC in SQL Server.

Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133