Introduction to T-SQL IsNull functions

Today in the transfer of data to colleagues, colleagues feedback said data is small, I carefully check, found that the SQL statement conditions are correct, the logic is not right, finally after careful investigation, only to find the problem in the null field

There is a column in the table that is allowed as a null value, such as a record that queries a merchant name that is not a test name

My query for name<> ' test ' is 10 lines,

SELECT * FROM Test name<> ' test '

Check the data name= ' test ' has 90 rows, total record 120 rows, 20 rows do not meet <> ' test ', nor = ' test ', these 20 rows of record name are null

As shown, it is clear from a glance:

In the data table row Records, the data column often has a record of NULL, in fact, the null value represents the unknown, that is, empty, indicating nothing,

But it's not what we're talking about, it's not about the value 0, it's about the unknown data. A point character that can understand unknown data

Null values are handled differently than other values

Null cannot participate in the comparison because it is an unknown value, so it cannot be used after the comparison operator.

Name<>null,name=null, are not correct,

A null value cannot be compared to an operator, so how can the query column value be null?

We must use the is null and is not NULL operator,

If you query a user whose name is null

-- record is null SELECT COUNT (1fromWHEREisNULL

Query the user record whose name is not equal to ' aaaa ':

-- the right name is not equal to ' aaaa ' SELECT *  from WHERE ISNULL (U.name,") <> ' AAAA '

Actually, the IsNull function has been used here.

The IsNull function has two parameters, the first argument is the expression to evaluate, and the second is the substitution value when the first expression value is empty.

Introduction to T-SQL IsNull functions