SQL Reference ยท Operators and Predicates
SQL comparison operators
Reference for SQL comparison operators =, <>, !=, <, >, <= and >=: how they treat NULL, text, case and types, with examples and dialect differences.
Comparison operators compare two values and produce TRUE, FALSE or NULL (unknown). They are the building blocks of WHERE, ON, HAVING, CASE and CHECK conditions.
SQL uses a single = for equality (there is no ==), and <> for "not equal". The result is NULL whenever either side is NULL, which is the source of many bugs: NULL = NULL is not true. To test for NULL, use IS NULL.
SELECT title, price FROM books WHERE price >= 15 AND genre <> 'Poetry';
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
expr = expr -- equal expr <> expr -- not equal (standard) expr != expr -- not equal (common extension) expr < expr expr <= expr expr > expr expr >= expr
| Operator | Meaning |
|---|---|
= |
Equal |
<> |
Not equal. != is accepted by SQLite, PostgreSQL, MySQL, SQL Server and Oracle. |
<, <= |
Less than, less than or equal |
>, >= |
Greater than, greater than or equal |
Returns: TRUE, FALSE, or NULL if either operand is NULL. SQLite and MySQL show true and false as 1 and 0.
Dialect differences
| Topic | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Case of text comparison | Case-sensitive (BINARY) | Case-sensitive (collation) | Case-insensitive by default | Case-insensitive by default | Case-sensitive |
== accepted |
Yes | No | No | No | No |
| Boolean result in select list | 1 / 0 |
true / false |
1 / 0 |
Not allowed; use CASE |
Not allowed before 23ai |
| NULL-safe equality | IS |
IS NOT DISTINCT FROM |
<=> |
IS NOT DISTINCT FROM (2022+) |
DECODE, or LNNVL tricks |
| Comparing different types | Storage-class ordering; text vs number is never equal | Error for incompatible types | Implicit conversion to numbers | Implicit conversion by type precedence | Implicit conversion; may fail |
Usage notes
- Never write
= NULL. It is always unknown and matches nothing. Use IS NULL, or IS DISTINCT FROM for NULL-safe comparison. <>andNULL.col <> 'x'excludes rows wherecolisNULL. If you want them, writecol <> 'x' OR col IS NULL.- Text order depends on collation. Binary collation orders by character code, so all uppercase letters sort before lowercase. Use
LOWER()on both sides or a case-insensitive collation for case-insensitive search. - Numbers stored as text compare as text:
'10' < '9'is true. Cast to a number, or store numbers in numeric columns. - Dates as text. ISO 8601 strings (
'2024-05-09') compare correctly with these operators because the format sorts alphabetically. - Floating-point equality (
price = 0.1 + 0.2) can fail because of rounding. Compare with a tolerance or use exactDECIMALtypes. - Row values:
(a, b) > (x, y)compares the first fields, then the next on ties. SQLite, PostgreSQL, MySQL and Oracle support it; SQL Server does not. - Indexes. A plain comparison on an indexed column can use the index; wrapping the column in a function usually prevents it, see sargable predicates.
Related
- AND, OR and NOT, BETWEEN, IN, IS NULL, NULL and three-valued logic
- Lessons: WHERE, Operators, NULL