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 the 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', 'anne21@adventure-works.com', NULL),
('Jordyn', 'Russell', '2014-04-14', NULL, NULL),
('Brandi', 'Martin', '2013-09-06', 'brandi0@adventure-works.com', NULL),
('Whitney', 'Sara', '2014-05-25', NULL, NULL),
('Philip', 'Sanz', '2014-03-02', 'philip19@adventure-works.com', NULL),
('Maria', 'Barnes', '2013-12-29', 'maria26@adventure-works.com', NULL),
('Lydia', 'Weber', '2013-11-29', 'lydia3@adventure-works.com', NULL),
('Kayla', 'Alexander', '2012-04-25', NULL, NULL),
('Troy', 'Raman', '2014-01-07', NULL, NULL),
('George', 'Patel', '2013-07-09', 'george9@adventure-works.com', 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 who 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
