How To Use Comparison and IS NULL Operators in SQL

Introduction

In certain Structured Query Language (SQL) statements, WHERE clauses can be used to limit what rows the given operation will affect. They do this by defining specific criteria that each row must meet for it to be impacted, known as a search condition. Search conditions are made up of one or more predicates, which are special expressions that evaluate to either “true,” “false,” or “unknown,” and operations only affect those rows for which every predicate in the WHERE clause evaluates to “true.”

SQL allows users to write search conditions that include a variety of different types of predicates, each of which use a specific operator to evaluate rows. This guide will outline two types of predicates and the operators they use: comparison operators and the IS NULL operator.

Prerequisites

In order to follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

• A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
• MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a newly-created user, as described in Step 3.
• You’ll also need a database with some tables loaded with sample data which you can use to practice using the comparison and IS NULL operators. We encourage you to go through the following Connecting to MySQL and Setting up a Sample Database section for details on how to connect to a MySQL server and create the testing database used in examples throughout this guide.

Connecting to MySQL and Setting up a Sample Database

If your SQL database system runs on a remote server, SSH into your server from your local machine:

ssh sammy@your_server_ip

Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:

mysql -u sammy -p

From the prompt, create a database named comparison_null_db:

CREATE DATABASE comparison_null_db;

If the database was created successfully, you’ll receive output like this:

Output
Query OK, 1 row affected (0.01 sec)

To select the comparison_null_db database, run the following USE statement:

USE comparison_null_db;

Output
Database changed

After selecting comparison_null_db, create a table within it.

To follow along with the examples used in this guide, imagine that you and a group of your friends all decide to become more physically active and take up running as exercise. To this end, your friends all set a personal goal for how many miles they want to run in the next month. You decide to track your friends’ mileage goals, as well as the number of miles they actually ran, in an SQL table that has the following three columns:

• name: each of your friends’ names, expressed using the varchar data type with a maximum of 15 characters
• goal: each friends’ goal for how many miles they hoped to run over the past month, expressed as an integer using the int data type
• result: the number of miles each friend ultimately ran over the course of the month, again expressed as an int

Run the following CREATE TABLE statement to create a table named running_goals that has these three columns:

CREATE TABLE running_goals (
name varchar(15),
goal int,
result int
);

Output
Query OK, 0 rows affected (0.012 sec)

Then load the running_goals table with some sample data. Run the following INSERT INTO operation to add seven rows of data representing seven of your friends, their running goals, and their results:

INSERT INTO running_goals
VALUES
('Michelle', 55, 48),
('Jerry', 25, NULL),
('Milton', 45, 52),
('Bridget', 40, NULL),
('Wanda', 30, 38),
('Stewart', 35, NULL),
('Leslie', 40, 44);

Output
Query OK, 7 rows affected (0.004 sec)
Records: 7 Duplicates: 0 Warnings: 0

Note that three of these rows’ result values are NULL. For the purposes of this example, assume that these friends just haven’t reported the number of miles they ran over the past month so their result values were entered as NULL.

With that, you’re ready to follow the rest of the guide and begin learning how to use the comparison and IS NULL operators in SQL.

Understanding WHERE Clause Predicates

In any SQL operation that reads data from an existing table, you can follow the FROM clause with a WHERE clause to limit what data the operation will affect. WHERE clauses do this by defining a search condition; any row that doesn’t meet the search condition is excluded from the operation, but any row that does is included.

A search condition is made up of one or more predicates, or expressions that can evaluate one or more value expressions and return a result of either “true,” “false,” or “unknown.” In SQL, a value expression — also sometimes referred to as a scalar expression — is any expression that will return a single value. A value expression can be a literal value, like a string or numeric value, a mathematical expression, or a column name. Note that it’s almost always the case that at least one value expression in a WHERE clause predicate is the name of a column in the table referenced in the operation’s FROM clause.

When running SQL queries that contain a WHERE clause, the DBMS will apply the search condition to every row in the logical table defined by the FROM clause. It will then return only the rows for which every predicate in the search condition evaluates to “true.”

The SQL standard defines 18 types of predicates, although not every RDBMS includes each of them in its implementation of SQL. Here are five of the most commonly used predicate types, as well as a brief explanation of each one and the operators they use:

Comparison: Comparison predicates compare one value expression with another; in queries, it’s almost always the case that at least one of these value expressions is the name of a column. The six comparison operators are:

• =: tests whether the two values are equivalent
• <>: tests whether two values are not equivalent
• <: tests whether the first value is less than the second
• >: tests whether the first value is greater than the second
• <=: tests whether the first value is less than or equal to the second
• >=: tests whether the first value is greater than or equal to the second

Null: Predicates that use the IS NULL operator test whether values in a given column are Null Range: Range predicates use the BETWEEN operator to test whether one value expression falls between two others Membership: This type of predicate uses the IN operator to test whether a value is a member of a given set Pattern Match: Pattern matching predicates use the LIKE operator to test whether a value matches a string pattern

As mentioned in the introduction, this guide focuses on how to use SQL’s comparison and IS NULL operators to filter data. If you’d like to learn how to use the BETWEEN or IN operators with range and membership predicates, respectively, we encourage you to check out this guide on How To Use the BETWEEN and IN Operators in SQL. Alternatively, if you’d like to learn how to use the LIKE operator to filter data based on a string pattern containing wildcard characters, follow our guide on How To Use Wildcards in SQL. Lastly, if you’d like to learn more about WHERE clauses generally, you may be interested in our tutorial on How To Use WHERE Clauses in SQL.

Comparison Predicates

WHERE clause comparison predicates use one of six comparison operators to compare one value expression with another. They typically follow a syntax like this:

SELECT column_list
FROM table_name
WHERE column_name OPERATOR value_expression;

Following the WHERE keyword is a value expression which, in most SQL operations, is the name of a column. Providing a column name as a value expression in a search condition tells the RDBMS to use each row’s value from that column as the value expression for that row’s iteration of the search condition. Because the database system applies search conditions to each row in sequence, the comparison operator will then include or filter out a row based on whether the search condition is true for its value from the specified column.

To illustrate, run the following query which will return values from the running_goals table’s name and goal columns. Note how the WHERE clause uses a comparison predicate that will cause the query to only return rows whose goal value is equal to 40:

SELECT name, goal
FROM running_goals
WHERE goal = 40;

Only two of your friends’ goals were to run exactly 40 miles over the past month, so the query returns just those two rows:

Output
+---------+------+
| name    | goal |
+---------+------+
| Bridget |   40 |
| Leslie  |   40 |
+---------+------+
2 rows in set (0.00 sec)

To illustrate how the other comparison operators work, run the following queries which are identical to the previous example except that each uses a different comparison operator.

The <> operator tests whether two values are not equivalent, so this query returns every row whose goal value is not equal to 40:

SELECT name, goal
FROM running_goals
WHERE goal <> 40;

Output
+----------+------+
| name     | goal |
+----------+------+
| Michelle |   55 |
| Jerry    |   25 |
| Milton   |   45 |
| Wanda    |   30 |
| Stewart  |   35 |
+----------+------+
5 rows in set (0.00 sec)

The < operator tests whether the first value expression is less than the second:

SELECT name, goal
FROM running_goals
WHERE goal < 40;

Output
+---------+------+
| name    | goal |
+---------+------+
| Jerry   |   25 |
| Wanda   |   30 |
| Stewart |   35 |
+---------+------+
3 rows in set (0.00 sec)

The > operator tests whether the first value expression is greater than the second:

SELECT name, goal
FROM running_goals
WHERE goal > 40;

Output
+----------+------+
| name     | goal |
+----------+------+
| Michelle |   55 |
| Milton   |   45 |
+----------+------+
2 rows in set (0.00 sec)

The <= operator tests whether the first value is less than or equal to the second:

SELECT name, goal
FROM running_goals
WHERE goal <= 40;

Output
+---------+------+
| name    | goal |
+---------+------+
| Jerry   |   25 |
| Bridget |   40 |
| Wanda   |   30 |
| Stewart |   35 |
| Leslie  |   40 |
+---------+------+
5 rows in set (0.00 sec)

The >= operator tests whether the first value is greater than or equal to the second:

SELECT name, goal
FROM running_goals
WHERE goal >= 40;

Output
+----------+------+
| name     | goal |
+----------+------+
| Michelle |   55 |
| Milton   |   45 |
| Bridget  |   40 |
| Leslie   |   40 |
+----------+------+
4 rows in set (0.00 sec)

The equivalence (=) and inequivalence (<>) operators work with string values as one might expect. The following query returns every row’s name value that’s equal to 'Leslie':

SELECT name
FROM running_goals
WHERE name = 'Leslie';

Because there’s only one friend in the table named “Leslie,” the query just returns that row:

Output
+--------+
| name   |
+--------+
| Leslie |
+--------+
1 row in set (0.00 sec)

When comparing string values, the <, >, <=, and >= operators all evaluate how the strings relate alphabetically. Put differently, if you write a predicate that tests whether one string is “less than” another, you’re testing whether the first string comes before the second alphabetically. Likewise, if your predicate tests whether one string is “greater than” another, you’re testing whether the first string comes after the second one alphabetically.

To illustrate, run the following query. This will return the name and goal values of every row whose name value is “less than” the letter 'M'. In other words, the search condition will evaluate to “true” for every row whose name value comes before M alphabetically:

SELECT name
FROM running_goals
WHERE name < 'M';

Output
+---------+
| name    |
+---------+
| Jerry   |
| Bridget |
| Leslie  |
+---------+
3 rows in set (0.00 sec)

Notice that this result set doesn’t include Michelle or Milton. This is because, alphabetically, the single letter “M” comes before any string that starts with the letter “M” and has more than one letter, so these two friends are excluded from this result set.

Null Predicates

In SQL, NULL is a reserved keyword used to represent missing or unknown values. Null is a state, rather than an actual value; it does not represent zero or an empty string.

You can use the IS NULL operator to test whether a given value expression is Null:

. . . 
WHERE column_name IS NULL
. . .

With this type of predicate, the database system will look at every row’s value from the specified column and evaluate whether or not each one is Null. If values in the column are indeed Null, the search condition will evaluate to “true” for those rows and they will be included in the result set.

To illustrate, run the following query which returns the name and result columns:

SELECT name, result
FROM running_goals
WHERE result IS NULL;

The search condition in this query’s WHERE clause tests whether each row’s result value is Null. If so, the predicate evaluates to “true” and the row is included in the result set:

Output
+---------+--------+
| name    | result |
+---------+--------+
| Jerry   |   NULL |
| Bridget |   NULL |
| Stewart |   NULL |
+---------+--------+
3 rows in set (0.00 sec)

Because three of your friends haven’t yet reported the number of miles they ultimately ran over the past month, those values were recorded as NULL when you loaded the table with data. Consequently, the search condition in the query evaluates to “true” for these three rows, so they’re the only ones included in the result set.

Conclusion

By following this guide, you learned how to use SQL’s comparison and IS NULL operators in WHERE clauses to limit the rows that an operation will affect. While the commands shown here should work on most relational databases, be aware that every SQL database uses its own unique implementation of the SQL standard. You should consult your DBMS’s official documentation for a more complete description of each command and their full sets of options.

If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.