Example of SQL Server determining whether a field is null

Determines whether a variable is null:

IF (@VaribleName is NULL)

Select a record for which the field value is null:

WHERE column_name is NULL

ISNULL () function:

ISNULL (@VaribleName, ' defaultvalue ')

ISNULL (column_name, ' Default Value ')

SQL Server: Some things about null

When we design a table, we sometimes argue about whether the field allows null values. Database Cow man Kalen Delaney gave a suggestion.

1, never allow null values in user tables
2, include a NOT null limit in the user table definition
3, do not rely on database properties to control the behavior of NULL values

For the 1th, we say in reverse, if we allow null, what effect it will have on us.
1,sql sets a special bitmap in each record to show which columns that allow null values are stored really empty values. If it is null, SQL Server must decode the bitmap when accessing each row.
2, allowing null also increases the complexity of the application code, adding some special logic to handle the null value, which often leads to bugs.

2nd, to include some default values on columns that contain disallowed nulls, if NULL is not allowed, but no default value has been added, the insert will fail without a column insertion, and SQL Server defaults to insert and null inserts for columns that are not displayed.

The last point is mainly related to the comparison of null values. In our impression, is it null,is not NULL comparison, or with =,<> comparison. Depending on the database options ANSI NULLS, we cannot change the database options (most of us are not DBAs), but we can use the session set ANSI_NULLS equivalent to the database option ANSI NULLS. When this option is true, all comparisons to null values will be false, and the code must use is NULL condition to determine whether it is null, and when this option is false, if the two values that are compared are null values, the TRUE,SQL server allows =null as a synonym for is null , <> null is used as a synonym for not null.
If you forget this option, it is recommended to use is NULL to determine NULL, is not NULL to determine non-null.

First, determine whether the variable is null

if (@VaribleName is null)

Second, select records with null field values

SELECT * From to orders where price is null--price equals null
SELECT * from orders where, not null--price does not equal null

Using the IsNull function to replace null values

The function determines whether an object is empty and, if it is empty, replaces it with the specified default value.
1. Grammar
ISNULL (Check_expression, Replacement_value)
2. Parameter description
Check_expression
An expression that will be checked for null. Check_expression can be of any type.

Replacement_value
The expression to be returned when null. Replacement_value must have the same type as check_expresssion.

3, return value
Returns the value of the expression if check_expression is not NULL, otherwise return replacement_value.
4. Common situation

ISNULL (@VaribleName, ' defaultvalue ')
ISNULL (column_name, ' Default Value ')

Iv. use of SQL Server null values where you need to be aware

1, NULL and any operator operations are null
Normal values are generally possible for operator operations, but for example: the ID column int, so you can: id=id+1, but if the value of a column is Null,null+1=null
For example
Update Testnull
Set b=b+1
where B is null
The query found that the value of B has not changed and is still null.

2, any value and null comparison will return FALSE

Ordinary values can be "=" operations, such as the general appearance of the condition: Susername= ' John ', if the value of sUserName is null, to find all the records with a null name, this can not be used: Susername=null, Because NULL is not a specific value, any value will return false when compared to it. You can now borrow is null or is not NULL.
For example

SELECT * from Testnull where A=null--Returns an empty result set

SELECT * from testnull where B is null--returns result set 2 2 null

Indicates that null cannot be compared with "=" and can be replaced with IS null

3, when the statistics of the record contains a null value, it will ignore the null value

For example, when you count with a statistical function, such as COUNT (ID): The number of statistics records. It ignores null values when the statistics contain null values.

4, for in the impact of different

Example query: Query Testnull records in which the value of B in the table is contained in NULL.
SELECT * from Testnull where B in (null)--No records

In the query, the records of NULL are ignored, and the query can be queried with the IS isn't null

5, the order of the sort is different

When an order by is used, the null value is first rendered. If you sort in descending order with DESC, the null value is finally displayed.

6, there will never be any data equal to NULL

1 is not equal to null,2. But null is not equal to NULL. So we can only compare it to "yes" or "no".