Exploring SQL Server WHERE IS NOT NULL Statement

Are you familiar with the SQL WHERE clause and how it can be used to filter data in SQL Server? If so, you may have come across the WHERE IS NOT NULL statement. But do you know how powerful this statement can be in filtering out specific data?

In this SQL tutorial, we will dive into the details of the WHERE IS NOT NULL operator in SQL Server and explore some real-world examples of how it can be used. We will cover its usage in the SELECT, INSERT, UPDATE, and DELETE statements.

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

Before we delve into the WHERE IS NOT NULL statement, let’s quickly review the differences between NULL, NOT NULL, IS NULL, and IS NOT NULL in SQL Server.

NULL represents the absence of a value in a database table. It is different from the string value “NULL” itself. When a tuple has the word “NULL” written in it, it is no longer considered a null value. NULL indicates that a cell (tuple) is empty, but a placeholder is reserved for a true value to be inserted later.

Here’s a breakdown of the four NULL settings:

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. 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. It returns TRUE if a non-NULL value is found and FALSE otherwise.

Using WHERE IS NOT NULL in SQL Server

Now, let’s explore how to use the WHERE IS NOT NULL statement in SQL Server.

Prerequisites

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

Part 1: Creating a Sample Database

Here’s the basic SQL syntax for creating a sample database:

USE master;
GO
CREATE DATABASE MyTestDatabase;
GO

Run the above script to create a test database if you don’t have one already.

Part 2: Creating a Test Table

Let’s create a test table called “MyEmployeeTable” with some sample data:

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 into the table, 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

Now, let’s verify that the table is properly populated:

SELECT * FROM MyEmployeeTable;
GO

Notice that some rows have NULL values in the email column, indicating that the employee did not provide an email address.

Using WHERE IS NOT NULL in the SELECT Statement

If you want to select all rows from a table where a specific column is not NULL, you can use the WHERE IS NOT NULL constraint with the SELECT statement. For example, to retrieve all rows from the MyEmployeeTable where the email column is not NULL, you can use the following SQL statement:

SELECT * FROM MyEmployeeTable
WHERE email IS NOT NULL;
GO

This will return all rows from the MyEmployeeTable where the email column is not NULL.

You can also use multiple constraints in a single query. For instance, the following query 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';
GO

Using WHERE IS NOT NULL in the UPDATE Statement

The WHERE IS NOT NULL constraint can be used with the UPDATE statement to update only records that are not NULL. For example, let’s update the “promote” column to “Yes” for all employees who have provided their email addresses:

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

Now, let’s query the employee table to see which rows were updated:

SELECT * FROM MyEmployeeTable;
GO

Using WHERE IS NOT NULL in the INSERT Statement

When inserting data into a table, the WHERE IS NOT NULL constraint can be used to ensure that all required fields are populated. Let’s copy all the rows and columns from the MyEmployeeTable to a new table called “empEmail” 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 SELECT query will verify that the new table has been populated with only the rows where the employee email address is not NULL:

SELECT * FROM empEmail;
GO

Using WHERE IS NOT NULL in the DELETE Statement

The 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. For example, let’s delete the records from the MyEmployeeTable where the email column is not NULL:

DELETE FROM MyEmployeeTable
WHERE email IS NOT NULL;
GO

Running a SELECT query on the MyEmployeeTable will show the updated results:

SELECT * FROM MyEmployeeTable;
GO

Wrap Up

The WHERE clause in SQL Server is a powerful tool for filtering data from a database table. By using the WHERE IS NOT NULL statement, you can further refine your queries to exclude rows with NULL values in specific columns. This can be useful in various scenarios, such as selecting, updating, inserting, or deleting data based on the absence of NULL values.

Now that you have a better understanding of the WHERE IS NOT NULL statement, you can leverage its power to manipulate data more effectively in SQL Server.

Article Last Updated: 2023-03-13

Click to rate this post!

[Total: 0 Average: 0]

Comprehensive 360 Degree Assessment

Data Replication

Performance Optimization

Data Security

Database Migration

Expert Consultation

Cloud Migration Made Easy

Considering a move to the cloud? Axial SQL brings you proven migration strategies to streamline your transition. Our expert team ensures a smooth, efficient shift, keeping your data safe and accessible. Start your journey to the cloud with confidence!

SQL Performance Optimization

Is your SQL running slower than expected? Don't let sluggish performance hinder your business. Our optimization experts at Axial SQL specialize in tuning your databases for peak performance. Speed up your SQL and supercharge your data processing today!

Database Stability Solutions

Tired of frequent database outages? Discover stability with Axial SQL! Our comprehensive analysis identifies and resolves your database vulnerabilities. Enhance reliability, reduce downtime, and keep your operations running smoothly with our expert guidance.

Expert Database Team Evaluation

Questioning your database team's efficiency? Let Axial SQL provide an expert, unbiased analysis. We assess your team's strategies and workflows, offering insights and improvements to boost productivity. Elevate your database management to new heights!

Data Security Assurance

Concerned about your database security? Axial SQL is here to fortify your data defenses. Our specialized security assessments identify potential risks and implement robust protections. Keep your sensitive data secure and your peace of mind intact with our expert services.

Published on