Comparison Operators

Compare values using =, <>, <, >, <=, >= and SQL-specific operators.

Overview

Comparison operators return TRUE, FALSE, or NULL and are the building blocks of WHERE clauses. PostgreSQL supports the standard operators (=, <>, <, >, <=, >=) plus SQL-specific extensions like IS DISTINCT FROM (null-safe equality), and the ~ family of regular expression operators. A key rule: any comparison involving NULL produces NULL (not TRUE or FALSE). This means col = NULL always returns NULL you must use IS NULL. The IS DISTINCT FROM operator is "null-safe" it returns TRUE even when comparing NULL values.

Key Points

•= equality, <> or != for inequality
•< > <= >= work for numbers, dates, and strings (alphabetically)
•NULL comparisons with = always return NULL use IS NULL
•IS DISTINCT FROM is null-safe: NULL IS DISTINCT FROM NULL is FALSE
•IS NOT DISTINCT FROM is null-safe equality: NULL IS NOT DISTINCT FROM NULL is TRUE

Examples

Common comparison operators

SELECT 5 = 5 AS equal;          -- TRUE
SELECT 5 <> 6 AS not_equal;    -- TRUE
SELECT 10 > 5 AS greater;      -- TRUE
SELECT 3 <= 3 AS lte;          -- TRUE

-- String comparison (alphabetical)
SELECT 'apple' < 'banana';     -- TRUE

NULL-safe comparisons

SELECT NULL = NULL;                      -- NULL (not TRUE!)
SELECT NULL IS NULL;                     -- TRUE
SELECT NULL IS DISTINCT FROM NULL;       -- FALSE (they are the same)
SELECT NULL IS NOT DISTINCT FROM NULL;   -- TRUE (null-safe equality)

Comparison Operators

Overview

Key Points

Examples

Exercises

Knowledge Check

Which of these is the correct way to check for NULL in a WHERE clause?