Comparison Operators

Take a closer look at the comparison operators in MySQL, including how they behave when comparing against NULL.

Introduction

Comparison operators have already appeared in almost every WHERE clause in this course so far — = to check equality, > to check "greater than," and so on. This lesson gathers them all in one place, gives you a complete reference table, and covers an important edge case: how comparisons behave when NULL is involved.

Understanding NULL comparisons now will save you from a very common and confusing bug later, when a query that "should" return rows returns none at all.

What You Will Learn

All six comparison operators: =, !=, <, >, <=, >=.
A complete reference table with examples for each.
Why comparing anything to NULL with = or != does not work as expected.
A worked example filtering with several comparison operators together.

The Comparison Operators

A comparison operator compares two values and evaluates to true, false, or — as you will see shortly — unknown. MySQL supports six standard comparison operators.

The students table below is used for the examples in this lesson.

id	name	grade	marks	email
1	Alex	A	92	alex@mail.com
2	Sam	B	74	sam@mail.com
3	Priya	A	88	NULL
4	Jon	C	55	jon@mail.com

Notice that Priya has no email on file — her email column stores NULL, meaning the value is unknown or missing. This will matter later in the lesson.

Comparison Operators Reference

Operator	Meaning	Example	Result
=	Equal to	grade = 'A'	True for Alex and Priya
!= or <>	Not equal to	grade != 'A'	True for Sam and Jon
<	Less than	marks < 80	True for Sam and Jon
>	Greater than	marks > 80	True for Alex and Priya
<=	Less than or equal to	marks <= 74	True for Sam and Jon
>=	Greater than or equal to	marks >= 88	True for Alex and Priya

SELECT name, marks
FROM students
WHERE marks >= 80;

Result

+-------+-------+
| name  | marks |
+-------+-------+
| Alex  | 92    |
| Priya | 88    |
+-------+-------+

Note

Both != and <> mean "not equal to" in MySQL. != is more common and widely recognized, but <> is standard SQL syntax and works identically.

Comparisons with NULL

NULL represents an unknown or missing value — it is not the same as zero, an empty string, or any other regular value. Because of this, comparing anything to NULL using =, !=, or any other comparison operator does not return true or false — it returns NULL (unknown).

null_trap.sql

-- This looks like it should find Priya, but it will not
SELECT name, email
FROM students
WHERE email = NULL;

Result

Empty set (0 rows affected)

Why This Returns Nothing

email = NULL never evaluates to true, even for the row where email genuinely is NULL, because comparing anything to an unknown value produces an unknown result, not true. The same applies to email != NULL — it also never evaluates to true.

To correctly check for NULL values, MySQL provides the dedicated IS NULL and IS NOT NULL operators instead, which are covered in their own upcoming lesson.

correct_null_check.sql

-- Correct way to find rows where email is missing (previewed here, explained fully later)
SELECT name, email
FROM students
WHERE email IS NULL;

Result

+-------+-------+
| name  | email |
+-------+-------+
| Priya | NULL  |
+-------+-------+

Worked Example: Multiple Comparison Operators

Comparison operators are frequently combined with the logical operators from the previous lesson to build precise filters.

worked_example.sql

SELECT name, grade, marks
FROM students
WHERE marks >= 60 AND marks < 90 AND grade != 'C';

Result

+-------+-------+-------+
| name  | grade | marks |
+-------+-------+-------+
| Sam   | B     | 74    |
| Priya | A     | 88    |
+-------+-------+-------+

This query returns students whose marks fall between 60 and 90 (using >= and <), while also excluding grade "C" (using !=). Alex is excluded because his marks (92) are not less than 90, and Jon is excluded on both marks and grade.

Common Mistakes

Avoid These Mistakes

Writing WHERE column = NULL or WHERE column != NULL — neither ever returns true; use IS NULL or IS NOT NULL instead.
Confusing = (comparison) with == , which does not exist in SQL at all.
Mixing up < and > when reading a condition quickly — always double-check the direction.
Forgetting that string comparisons like grade = 'a' may or may not match 'A' depending on the database's collation settings.

Best Practices

Use <= and >= instead of chaining separate conditions when you mean "at least" or "at most."
Never rely on = or != to detect NULL values; always use IS NULL or IS NOT NULL.
Prefer != for readability, unless the project convention already favors <>.
When a range is involved, consider the dedicated BETWEEN operator, covered in the next lesson.

Frequently Asked Questions

Why does WHERE email = NULL return no rows even when NULL emails exist?

Because any comparison involving NULL evaluates to unknown rather than true, so the row is never included. Use IS NULL to correctly match NULL values, as covered in an upcoming lesson.

Is != the same as <>?

Yes, they are functionally identical in MySQL; both mean "not equal to." != is more commonly used in practice.

Can comparison operators be used on text as well as numbers?

Yes. Text is compared based on collation rules (roughly alphabetical order), so 'apple' < 'banana' evaluates to true.

What is the difference between comparison operators and BETWEEN?

BETWEEN, covered next, is essentially shorthand for combining >= and <= into a single range check, making range conditions easier to read.

Key Takeaways

MySQL supports six comparison operators: =, != (or <>), <, >, <=, >=.
Comparison operators evaluate to true or false for ordinary values.
Any comparison involving NULL using = or != returns NULL (unknown), never true.
IS NULL and IS NOT NULL, covered in a dedicated lesson, are the correct way to check for NULL.

Summary

Comparison operators are the foundation of nearly every WHERE and HAVING condition you will write. Knowing all six of them, and understanding the special case of NULL, prevents a very common class of SQL bugs.

In this lesson, you reviewed all six comparison operators with a reference table, saw why comparing to NULL needs special handling, and combined comparison operators in a worked example. Next, you will learn the BETWEEN operator for cleaner range checks.

Lesson 36 Completed

You know all six comparison operators and what each one does.
You can use a reference table to recall their exact meaning.
You understand why NULL requires IS NULL instead of = or !=.
You are ready to learn the BETWEEN operator for range checks.

Next Lesson →