Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT title, price
FROM books
WHERE price >= 15 AND genre <> 'Poetry';

Try it

SQL The six operators ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL NULL is never equal to anything ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Text comparisons are by character code in SQLite ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Row values compare field by field ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
  • <> and NULL. col <> 'x' excludes rows where col is NULL. If you want them, write col <> '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 exact DECIMAL types.
  • 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.