In SQL, NULL indicates that a value doesn't exist in the database. In other words, A field with a NULL value is a field with no value which is used to represent a missing value.
It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.
When a NULL is involved in a comparison operation, the result is considered to be UNKNOWN. Hence, SQL use three types of logic valued True, False, and Unknown
What is a SQL NULL value?
In terms of the RDMS model, a NULL value indicates an unknown value. If you widen this theoretical explanation, the NULL value points to an unknown value but this unknown value does not equivalent to a field that contains spaces or zero value. Due to this structure of the NULL values, it is not possible to use traditional comparison ( and <, >, <>,=) operators in the queries. As a matter of fact, in the SQL Standards using the WHERE clause as the below will lead to returning empty result sets.
Importance of NULL value:
- The zero value is not a NULL value.
- A NULL value is used to represent a missing value, but it usually has one of three different interpretations as given below.
1. The value unknown
2. Value not available
3. Attribute not applicable
- It is often not possible to determine which of the meanings is intended. Hence, SQL does not distinguish between the different meanings of NULL.
Principles of NULL values:
- When a value would not be meaningful when the actual value is unknown.
- Zero value is not a NULL value or a Null is not equivalent to a ZERO.
- A NULL value can be inserted into columns of any data type.
- A NULL value will evaluate NULL in any expression.
- Suppose if any column has a NULL value, then UNIQUE, FOREIGN key, CHECK constraints will ignore by SQL.
In general, each NULL value is considered to be different from every other NULL in the database. When a NULL is involved in a comparison operation, the result is considered to be UNKNOWN.
Syntax
The basic syntax of NULL while creating a table.
CREATE TABLE EmployeeInfo(
EmployeeId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR (100) NOT NULL,
LastName VARCHAR (100) NOT NULL,
Address VARCHAR (100), Age INT NOT NULL
);
Here, NOT NULL signifies that the column should always accept an explicit value of the given data type. There is one column where you did not use NOT NULL, which means this column could be NULL.
A field with a NULL value is the one that has been left blank during the record creation
Selecting data with the NULL value can cause problems when. However, when comparing an unknown value to any other value, the result is always unknown and not included in the results. We must use the IS NULL or IS NOT NULL operators to check for a NULL value.
Consider the following EmployeeInfo table having the records as given below.
The IS NOT NULL condition is used to return the rows that contain non-NULL values in a column. The given query will retrieve the rows from the EmployeeInfo table which are Address column value that is not equal to NULL values.
SELECT EmployeeId, FirstName, LastName, Address, Age
FROM EmployeeInfo
WHERE Address IS NOT NULL;
This would produce the following result as given below.
IS NULL Condition
The IS NULL condition is used to return rows that contain the NULL values in a column like as given below.
Syntax
SELECT <ColumnName>, <ColumnName1>, <ColumnName2>, ... , <ColumnName3>..
FROM <YourTableName>
WHERE <ColumnName> IS NULL
The given query will retrieve the rows from the EmployeeInfo table which are Address column values are equal to NULL
SELECT FirstName,LastName,Address,Age
FROM EmployeeInfo
WHERE Address IS NULL;
Handling SQL NULL Values with Functions
Some functions help to handle NULL values in SQL Server.
ISNULL(): The ISNULL() function takes two parameters and it enables us to replace NULL values with a specified value.
ISNULL (expression, replacement)
The expression parameter indicates the expression which you want to check NULL values.
The replacement parameter indicates the value which you want to replace the NULL values.
For example, in the given below query, the ISNULL() function replaces the NULL values in the row with the specified value.
SELECT Address,
ISNULL(Address,'Replace Value') AS ReplaceValue,
FirstName,LastName
FROM EmployeeInfo WHERE EmployeeId IN (2,7,10)
COALESCE(): The COALESCE() function takes unlimited parameters and returns the first non-null expression in a list.
COALESCE(val1, val2, ...., val_n)
In the given query, the COALESCE() function returns the SQLShack.com because it is the first non-null value in the list
SELECT COALESCE(NULL, NULL, 'findandsolve.com', NULL, 'Best Blog')
Count SQL NULL values in a column?
The COUNT() function is used to obtain the total number of rows in the result set. When you use this function with the star sign it counts all rows from the table regardless of NULL values. Such as, when you count the EmployeeInfo table through the given below query, it will return 3.
SELECT COUNT(*) AS TotalNullRows
FROM EmployeeInfo
WHERE Address IS NULL
| TotalNullRows |
| 3 |
What is the difference between ‘IS NULL’ and ‘= NULL’?
The equal (=) operator is used to comparing two expressions values and if the compared values are equal the comparison result will be true. On the other hand, when you try to use an equal (=)operator to find the rows that contain the null values, the result set will return nothing. For example, the given below query will not return any rows.
SELECT FirstName, LastName ,Address
FROM EmployeeInfo WHERE
Address = NULL
