How To Use Comparison and IS NULL Operators in SQL

Introduction

When filtering rows in SQL, the WHERE clause controls which records an operation reads, updates, or deletes by applying a search condition. A search condition is composed of one or more predicates — expressions that evaluate to true, false, or unknown — and only rows for which every predicate evaluates to true are included. This article explains SQL comparison operators and the SQL IS NULL operator, showing how to use them in WHERE clause predicates to filter data reliably.

Prerequisites

• A relational database (MySQL, PostgreSQL, SQLite, etc.) where you can run SQL queries.
• A basic understanding of SELECT, FROM, and WHERE clauses.
• A sample table to practice SQL WHERE clause operators and SQL predicates, such as the examples below.

Sample table and data

The examples use a simple table named running_goals with three columns: name (varchar), goal (int), and result (int). Some result values are NULL to demonstrate SQL null check behavior.

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

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

Understanding WHERE clause predicates

Predicates are the building blocks of search conditions. A value expression inside a predicate is often a column name. The database evaluates the predicate for each row; if it returns true, the row passes the filter. Predicates include comparison predicates, null predicates, range and membership predicates, and pattern-matching predicates. This guide focuses on SQL comparison operators and the SQL IS NULL operator.

SQL comparison operators

Comparison predicates compare two value expressions. The common comparison operators are:

• = (equality)
• <> or != (inequality)
• < (less than)
• > (greater than)
• <= (less than or equal)
• >= (greater than or equal)

These SQL comparison operators are used frequently in WHERE clauses to implement search conditions. Here are examples that demonstrate how to use comparison operators in SQL and how they behave with numeric and string values.

Numeric comparisons

-- rows where goal equals 40
SELECT name, goal
FROM running_goals
WHERE goal = 40;

-- rows where goal is not equal to 40
SELECT name, goal
FROM running_goals
WHERE goal <> 40;

-- rows where goal is less than 40
SELECT name, goal
FROM running_goals
WHERE goal < 40;

-- rows where goal is greater than or equal to 40
SELECT name, goal
FROM running_goals
WHERE goal >= 40;

String comparisons and alphabetical ordering

Equality and inequality work with strings, for example WHERE name = 'Leslie'. The relational operators < and > compare strings alphabetically in many RDBMSs, so they are useful when you need to compare textual values based on lexicographical order.

-- names that come before 'M' alphabetically
SELECT name
FROM running_goals
WHERE name < 'M';

Null predicates and the SQL IS NULL operator

SQL uses NULL to represent missing or unknown values. NULL is not the same as zero or an empty string. You cannot reliably test NULL values with = NULL; instead use the SQL IS NULL operator or IS NOT NULL for null checks in WHERE clauses.

Filtering NULL values

-- find rows where result is NULL
SELECT name, result
FROM running_goals
WHERE result IS NULL;

-- find rows where result is not NULL
SELECT name, result
FROM running_goals
WHERE result IS NOT NULL;

Using = NULL yields UNKNOWN in SQL’s three-valued logic and will not return the rows you expect. Always use IS NULL to filter NULL values.

Practical tips and differences

• Use comparison operators in the WHERE clause to filter numeric ranges and exact matches: these SQL WHERE clause operators are fast when indexed appropriately.
• When checking for NULL, prefer IS NULL and IS NOT NULL rather than equality operators; this avoids pitfalls with SQL null predicate behavior and SQL three-valued logic.
• Remember that different RDBMSs (MySQL, PostgreSQL, SQLite) follow the SQL standard but may have subtle differences in collation and string comparison behavior. Consult your DBMS documentation for specifics.

Conclusion

Mastering SQL comparison operators and the SQL IS NULL operator lets you construct precise WHERE clause predicates to filter data. Whether you are performing SELECTs, UPDATEs, or DELETEs, clear use of comparison and null predicates ensures the intended rows are affected. For deeper learning, practice with examples like those above and review your DBMS documentation for engine-specific notes.

Keywords integrated: SQL comparison operators, SQL WHERE clause operators, SQL IS NULL, SQL null check, SQL predicates, MySQL comparison operators, SQL operators list, SQL where clause examples.