Null Value in the database table)

Null Value

Null indicates that the value is unknown. The null value is different from the blank or zero value. There are no two equal null values. Compare two null values or compare null values with any other values to return unknown results, because each null value is unknown.

NULL values usually indicate unknown, unavailable, or data that will be added later. For example, the customer's initial name may not be known when the customer places an order.

The following information about null values:

• To test the null value in the query, use is null or is not null in the WHERE clause.

• When you view the query results in the SQL query analyzer, the null value is displayed(Null).
• You can insert a null value to a column by explicitly declaring null in the insert or update statement, or not entering the column into the insert statement, or adding a new column to the existing table using the alter table statement.
• NULL values cannot be used to distinguish the information required for the two rows in the table (for example, foreign keys or primary keys ).

InProgramCodeYou can check for null values to perform some calculations only on rows with valid (or non-empty) data. For example, a report can only print columns whose data is not empty in the Social Security column. It is important to eliminate null values when performing calculations because some calculations (such as average values) are inaccurate if a null column is included.

If the data may contain null values, it is a good way to create a query or data modification statement to eliminate null values or convert null values to other values (if you do not want to create null values in the data ).

ImportantTo reduce the maintenance and possible impact on existing queries or reports, we recommend that you use as few null values as possible. Plan query and data modification statements to minimize the impact of null values.

 

If the data has a null value, the logical operator and the comparison operator may return the third unknown result other than true or false. The three-value Logic is the source of many application errors. The following table summarizes the effect of comparing null values.

The following table shows the result of applying the and operator to two Boolean operands.

And	True	Unknown	False
True	True	Unknown	False
Unknown	Unknown	Unknown	False
False	False	False	False

The following table shows the result of applying the OR operator to two Boolean operands.

Or	True	Unknown	False
True	True	True	True
Unknown	True	Unknown	Unknown
False	True	Unknown	False

The following table shows the not operator inversion or inversion method, and the result of the Boolean operator.

Boolean expression with the not operator applied	The value is
True	False
Unknown	Unknown
False	True

The SQL-92 standard introduces the keywords is null and is not null to test null values.

Boolean expression with the is null operator applied	The value is	Boolean expression with the is not null operator applied	The value is
True	False	True	True
Null	True	Null	False
False	False	False	True

Transact-SQL also provides extended null value processing. If the ansi_nulls option is set to off, the comparison between null values (for example, null = NULL) is equal to true. The comparison between a null value and any other data type is false.