Null Values in SQL Server

In SQL Server, a NULL value represents an unknown or missing value. It is different from an empty or zero value. A field with a NULL value has no value and is not equal to any other value, including another NULL value.

To test for NULL values in SQL Server, you can use the IS NULL and IS NOT NULL operators. The IS NULL operator is used to test for empty values (NULL values), while the IS NOT NULL operator is used to test for non-empty values (NOT NULL values). Here’s an example:

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

This SQL statement selects all records from the table_name table where the column_name column has a NULL value.

Note: –

  • A field with a NULL value is a field with no value.
  • If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

How to Test for NULL Values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

Syntax: –

SELECT column_namesFROM table_name
WHERE column_name IS NULL;

The IS NULL Operator

The IS NULL operator is used to test for empty values (NULL values).

  • The following SQL lists all customers with a NULL value in the \”Address\” field:

Example,

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;

The IS NOT NULL Operator

The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).

  • The following SQL lists all customers with a value in the \”Address\” field:

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;