How to use the IS NOT NULL operator in SQL

Overview

We use the IS NOT NULL operator in SQL to filter data that is not null. We use this operator in conjunction with the WHERE keyword.

A NULL value represents a missing or unknown value.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IS NOT NULL;

Code

Let’s assume we have a table called Person with the columns ID, name, age, state, and gender.

Now, we want to obtain the names of all people whose ages are not missing in the Person table.

How do we get this information from our table?

The following code shows how to do this using the IS NOT NULL operator in SQL.

CREATE TABLE Person (
    ID int,
    name varchar(100),
    age int,
    gender varchar(10),
    state varchar(15)
);
-- Insert data
INSERT INTO Person
VALUES (1,'Sharon Peller','16','Female','Kogi');
INSERT INTO Person
VALUES (2,'Paul Dons', NULL,'Male','Lagos');
INSERT INTO Person
VALUES (3,'Ameera Abedayo','28','Female','Imo');
INSERT INTO Person
VALUES (4,'Maria Elijah',NULL,'Female','Lagos');
INSERT INTO Person
VALUES (5,'David Hassan',NULL,'Male','Abuja');
INSERT INTO Person
VALUES (6,'Niniola Disu','28','Female','Lagos');
INSERT INTO Person
VALUES (7,'Praise Dominion','16','Female','Ibadan');
INSERT INTO Person
VALUES (8,'Joe Smith','16','Male','Lagos');
-- Query 
SELECT name
FROM Person
WHERE age IS NOT NULL;

Explanation

  • Lines 1–7: We create a table called Person with the columns ID, name, age, gender, and state.
  • Lines 10–25: We insert data into the Person table.
  • Lines 28–30: We filter the non-missing data using the IS NOT NULL operator with the WHERE keyword.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)