SQL - IS NOT NULL

SQL - The IS NOT NULL Operator

Understanding NULL: The Concept of ‘Unknown’

In SQL, NULL is a special marker used to indicate that a data value does not exist in the database. It is crucial to understand that NULL is not the same as zero (0), an empty string (''), or a boolean FALSE. NULL represents a missing, unknown, or inapplicable value.

Because NULL means ‘unknown’, you cannot test for it using standard comparison operators like = or !=. The result of some_column = NULL is not true or false; it is also NULL (unknown). To specifically check for the presence of a value, we must use the IS NOT NULL operator.

The SQL IS NOT NULL Operator

The IS NOT NULL operator is used in a WHERE clause to filter for rows where a specific column contains an actual value (i.e., it is not NULL). This is essential for working with datasets that may have incomplete records.

Syntax

select column_names
from table_name
where column_name is not null;

Example Setup

Let’s create a customers table with some NULL values to demonstrate.

create table customers(
    id INT primary key,
    name VARCHAR(50) not null,
    age INT,
    shipping_address VARCHAR(255),
    last_login_date DATE
);

insert into customers values
(1, 'Ramesh', 32, '123 Maple St', '2023-10-15'),
(2, 'Khilan', 25, NULL, '2023-11-01'),
(3, 'Kaushik', NULL, '456 Oak Ave', NULL),
(4, 'Chaitali', 25, '789 Pine Ln', '2023-09-20'),
(5, 'Hardik', 27, NULL, NULL);

IS NOT NULL in a SELECT Statement

The following query retrieves all customers who have a known shipping_address.

select id, name, shipping_address
from customers
where shipping_address is not null;

Output

id	name	shipping_address
1	Ramesh	123 Maple St
3	Kaushik	456 Oak Ave
4	Chaitali	789 Pine Ln

Using IS NOT NULL with Aggregate Functions

Aggregate functions like COUNT(), SUM(), and AVG() behave differently with NULL values. COUNT(*) will count all rows, but COUNT(column_name) will only count rows where column_name is not NULL.

Example

Let’s count how many customers have a recorded age.

-- This will count only the non-NULL values in the 'age' column.
select count(age) as customers_with_known_age
from customers;

Output

customers_with_known_age
4

IS NOT NULL in DELETE and UPDATE Statements

You can also use IS NOT NULL to target rows for modification or deletion.

UPDATE Example

Let’s imagine we want to give a 10% discount to all customers who have a recorded age. We create a discount column and update it.

alter table customers add column discount DECIMAL(3, 2) default 0.00;

update customers
set discount = 0.10
where age is not null;

After this query, customers with IDs 1, 2, 4, and 5 would have their discount set to 0.10.

DELETE Example

Let’s delete all customer records that have no last login date, perhaps to clean up inactive accounts.

-- Note: This is a permanent action!
delete from customers
where last_login_date is null;

This query would remove customers with IDs 3 and 5 from the table.

Best Practice: Enforcing Integrity with NOT NULL Constraints

While IS NOT NULL is useful for querying, a better approach is to prevent NULL values from being inserted in the first place where they are not allowed. You can do this by adding a NOT NULL constraint to a column when you create a table.

This enforces data integrity at the database level, ensuring that essential data is always present.

create table users (
    id INT primary key,
    email VARCHAR(255) not null, -- The database will reject any insert/update that tries to set email to NULL.
    username VARCHAR(50) not null,
    phone_number VARCHAR(20) -- This column CAN be null, as a user might not have a phone number.
);

By using NOT NULL constraints proactively, you make your data cleaner and your queries simpler, as you won’t have to constantly check for NULLs in columns that should never be empty.