SQL WHERE IS NOT NULL for SELECT, INSERT, UPDATE and DELETE

By: Aubrey Love   |   Updated: 2023-03-13   |   Comments   |   Related: > TSQL

Problem

I am new to SQL and have some basic understanding of how to use the SQL WHERE clause. I know I can filter out certain data from columns and rows using this clause, and I can also filter out any rows that have NULL values. Somehow, I can't help but wonder, is there more I can do with the SQL WHERE IS NOT NULL statement?

Solution

Absolutely. Using the IS NOT NULL statement as an additional parameter setting in the WHERE clause will allow us to filter out very specific data.

In this SQL tutorial, we will discuss how to use the WHERE IS NOT NULL operator in SQL Server and explore some examples of using it in real-world scenarios. We will show, by examples, how to use the SQL WHERE IS NOT NULL statement to alter data in a test table in the following ways:

• WHERE IS NOT NULL in the SELECT statement
• WHERE IS NOT NULL in the INSERT statement
• WHERE IS NOT NULL in the UPDATE statement
• WHERE IS NOT NULL in the DELETE statement

But first, let's make sure you understand the difference between NULL, NOT NULL, IS NULL, and IS NOT NULL. In the next paragraph, we will take a high-level look at the basic differences between these four.

Understanding NULL, NOT NULL, IS NULL, and IS NOT NULL

This is just a quick overview of the aforementioned NULL settings.

Let's start with NULL. NULL means that there is no value assigned to a particular tuple in a database table. This is different from writing the word "NULL" in that tuple. If you write the word "NULL" in a tuple, it is no longer a null value. The typed word "NULL" is a string value, like "EMPTY." In short, a NULL value means that that cell (tuple) is empty, but a placeholder is set to reserve a logical space for a true value to be inserted at a later date.

Here is a quick breakdown in a simple form to differentiate between the NULL settings that we are discussing here:

• The SQL NULL condition is used to test for a NULL value. It returns TRUE if a NULL value is found and FALSE otherwise.
• The SQL NOT NULL condition is used to test for a non-NULL value. It returns TRUE if a non-NULL value is found and FALSE otherwise.
• The SQL IS NULL condition is used to test for a NULL value (similar to the NULL definition above). It returns TRUE if a NULL value is found and FALSE otherwise.
• The SQL IS NOT NULL condition is used to test for a non-NULL value (similar to the NOT NULL definition above). It returns TRUE if a non-NULL value is found and FALSE otherwise.

In this article, we will be focusing on how to use SQL WHERE IS NOT NULL.

Prerequisites

To run the code samples below, we need to create a sample database and table to work with. If you have a sample database to work with, you can skip Part 1 below and move on to Part 2.

Part 1

The basic SQL syntax for creating a sample database to work with:

USE master;
GO
CREATE DATABASE MyTestDataBase;
GO

Run the script above to create a test database if you do not have one already.

Part 2

Creating our test table:

USE myTestDataBase;
GO
IF OBJECT_ID('MyEmployeeTable') IS NOT NULL
DROP TABLE MyEmployeeTable;
GO
CREATE TABLE MyEmployeeTable (
   colID INT IDENTITY NOT NULL,
   firstName VARCHAR(20),
   lastName VARCHAR(20),
   hireDate DATE,
   email VARCHAR(50),
   promote VARCHAR(10)
   );
GO

Next, we will insert some generic SQL data while intentionally setting some tuples as NULL.

INSERT INTO MyEmployeeTable(firstName, lastName, hireDate, email, promote)
VALUES('Anne', 'Rubio', '2011-08-05', '[email protected]', NULL),
('Jordyn', 'Russell', '2014-04-14', NULL, NULL),
('Brandi', 'Martin', '2013-09-06', '[email protected]', NULL),
('Whitney', 'Sara', '2014-05-25', NULL, NULL),
('Philip', 'Sanz', '2014-03-02', '[email protected]', NULL),
('Maria', 'Barnes', '2013-12-29', '[email protected]', NULL),
('Lydia', 'Weber', '2013-11-29', '[email protected]', NULL),
('Kayla', 'Alexander', '2012-04-25', NULL, NULL),
('Troy', 'Raman', '2014-01-07', NULL, NULL),
('George', 'Patel', '2013-07-09', '[email protected]', NULL);
GO

Verify that your table is properly populated.

SELECT *
FROM MyEmployeeTable;
GO

Result set:

Notice that we have four rows where the employee has not provided an email address; thus, a NULL was applied to those tuples. We also set the promote column as NULL for all rows. We will use the email and promotion columns' NULL values to work through the article sections below.

You may also notice that, in our INSERT statement, we gave some tuples a value of NULL by using the value NULL, not the string value "NULL." We did this by NOT surrounding the word NULL with quote marks. We could have also used "DEFAULT" as well. Either will provide the same results.

Using the SQL WHERE IS NOT NULL Constraint With SELECT

If you want to select all rows from a table where a specific column is not NULL, you can use the SQL WHERE IS NOT NULL constraint with the SELECT statement. For example, let's say we want to send out a promotional email to employees who have shared their email addresses with the company. We would use the following SQL statement:

SELECT *
FROM MyEmployeeTable
WHERE email IS NOT NULL;
GO

Result set:

This will return all rows from the employees table where the email column IS NOT NULL. If you ran the same query to return all rows where the "promote" column IS NOT NULL, then, as you would guess, none of the rows will be returned.

Remember, you can use multiple constraints in a single query. The following query is an example that returns all rows with a hire date older than 2013, and the email column IS NOT NULL:

SELECT *
FROM MyEmployeeTable
WHERE email IS NOT NULL
AND hireDate < '2013';

Result set:

Using the SQL WHERE IS NOT NULL Constraint with UPDATE

The SQL WHERE IS NOT NULL constraint can be used with the UPDATE statement to ensure that only records that are not NULL are updated. In our test table, we can add a "Yes" value to the "promote" column for all employees that have provided their email addresses.

To use the SQL WHERE IS NOT NULL constraint with UPDATE, you need to add the IS NOT NULL constraint to the UPDATE statement. For example:

UPDATE MyEmployeeTable 
SET promote = 'Yes'
WHERE email IS NOT NULL;
GO

Now, let's query our employee table to see which rows were updated:

SELECT * FROM MyEmployeeTable;

Result set:

Using the SQL WHERE IS NOT NULL Constraint with INSERT

When inserting data into a table, the SQL WHERE IS NOT NULL constraint can be used to ensure that all required fields are populated. This is especially important when working with foreign keys, as they must always have a value to be valid.

In our next sample, we will copy all the rows and columns from the "MyEmployeeTable" to a new table that we will name "empEmail." We will do this by using the WHERE IS NOT NULL statement.

IF OBJECT_ID('empEmail') IS NOT NULL
DROP TABLE empEmail;
GO

SELECT 
   firstName,
   lastName, 
   hireDate, 
   email, 
   promote
INTO empEmail
FROM MyEmployeeTable
WHERE email IS NOT NULL;
GO

Running a quick SELECT query will verify that the new table has been populated with only the rows where the employee email address IS NOT NULL, and they now have a value of "Yes" in the "Promote" column. Remember, this column was updated in the previous section, "Using the SQL WHERE IS NOT NULL Constraint with UPDATE."

SELECT * FROM empEmail;

Result set:

Using the SQL WHERE IS NOT NULL Constraint with DELETE

The SQL WHERE IS NOT NULL constraint can also be used with the DELETE statement to delete only those records that do not contain a value in a specified column. Like the example in the "Using the SQL WHERE IS NOT NULL Constraint with UPDATE" section above, we can delete records instead of updating them.

Earlier, we copied all the rows and columns from the "MyEmployeeTable" table, where the employee provided an email address. As such, we no longer need them in the current table, and we can delete them.

DELETE 
FROM MyEmployeeTable
WHERE email IS NOT NULL;
GO

Let's run a simple SELECT * query to see the new results.

SELECT * FROM MyEmployeeTable;

Result set:

Wrap Up

The WHERE clause in SQL is used to specify the criteria for selecting data from a database table. This clause can also filter out data with some conditions, such as specifying only rows where a certain column or columns are not null. In this article, we discussed how to use the SQL IS NOT NULL statement to select, update, insert, and delete rows and tuples based on a tuple NOT having a NULL value.

Next Steps

• SQL WHERE Tutorial
• SQL WHERE Clause Explained
• SQL IS NOT NULL keyword
• IS NULL and IS NOT NULL Conditions

About the author

Aubrey Love is a self-taught DBA with more than six years of experience designing, creating, and monitoring SQL Server databases as a DBA/Business Intelligence Specialist. Certificates include MCSA, A+, Linux+, and Google Map Tools with 40+ years in the computer industry. Aubrey first started working on PC’s when they were introduced to the public in the late 70's.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips