SQL NULL Values

In SQL, a NULL value is a special marker that indicates that a field has no value. It is important to understand how NULL values work and how to handle them in SQL queries.

What is a NULL Value?

A field with a NULL value is a field that has no data. This is different from fields with a value of zero or fields that contain spaces. NULL values indicate that the field was intentionally left blank.

Note: A NULL value is not the same as zero (0) or an empty string (‘ ‘).

How to Test for NULL Values?

Testing for NULL values requires the use of the IS NULL or IS NOT NULL operators. It is not possible to use comparison operators (such as = or <>) to test for NULL values.

IS NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;
  

IS NOT NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
  

Demo Database

Below is a sample “Customers” table with Indian data:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Rajesh Stores	Rajesh Kumar	12 MG Road	Mumbai	400001	India
2	Sharma Traders	Vikas Sharma	56 Lal Path	Delhi	110001	India

The IS NULL Operator

The IS NULL operator is used to find fields that contain NULL values. Here’s an example:

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

The IS NOT NULL Operator

To find fields that are NOT NULL, use the IS NOT NULL operator. Example:

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

Tip: Always use the IS NULL or IS NOT NULL operators to handle NULL values effectively in SQL queries.