Comparison Operators

Definition

Comparison operators (=, <>/!=, <, >, <=, >=) compare two values and produce TRUE, FALSE, or UNKNOWN (if either side is NULL) — the basic building blocks of any WHERE condition.

Running example — a products(id, name, category, price, stock) table, where stock can be NULL (not yet counted):

sql
SELECT name FROM products WHERE price >= 500;
SELECT name FROM products WHERE category <> 'Books';

<> is the standard SQL "not equal" operator; != is a widely-supported but non-standard alternative spelling (most dialects accept both). Comparisons work on numbers, strings (compared according to the column's collation — usually alphabetical), and dates (chronologically).

Edge Cases and Pitfalls

• = NULL and <> NULL NEVER evaluate to TRUE, for either value — comparing anything to NULL with an ordinary comparison operator always produces UNKNOWN (Chapter 8's NULL Semantics); only IS NULL/IS NOT NULL (12.10, 12.11) correctly test for NULL.
• Comparing values of different types (e.g. a number to a string) often triggers an implicit type conversion, which can produce a confusing or unexpected result — being explicit about types avoids relying on conversion rules that vary by dialect.
• String comparison honors the column's collation setting, which determines case-sensitivity and locale-specific ordering rules — 'apple' = 'Apple' may be TRUE or FALSE depending entirely on the collation, not on the = operator itself.

Key Takeaways

• The six comparison operators (=, <>/!=, <, >, <=, >=) are the atomic building blocks of WHERE conditions.
• Comparing to NULL with these operators always yields UNKNOWN, never TRUE — use IS NULL/IS NOT NULL instead.
• String comparison behavior (case sensitivity, ordering) depends on collation, not just the operator.