Comparison Operators

Learning Focus

Use this lesson to master Comparison Operators — the foundation of every WHERE clause.

Available Operators

Operator	Meaning
=	Equal to
!= or <>	Not equal to
<	Less than
>	Greater than
<=	Less than or equal
>=	Greater than or equal
IS DISTINCT FROM	Not equal, NULL-safe
IS NOT DISTINCT FROM	Equal, NULL-safe

Examples

-- Equality
SELECT * FROM employees WHERE department = 'Engineering';

-- Not equal
SELECT * FROM employees WHERE department != 'HR';

-- Numeric comparison
SELECT * FROM employees WHERE salary >= 80000;

-- Between (inclusive)
SELECT * FROM employees WHERE salary BETWEEN 70000 AND 90000;

-- Date comparison
SELECT * FROM employees WHERE hire_date > DATE '2021-01-01';

-- NULL-safe comparison (IS DISTINCT FROM handles NULLs correctly)
SELECT * FROM employees WHERE hire_date IS DISTINCT FROM NULL;
-- same as: WHERE hire_date IS NOT NULL

NULL-Safe Comparison

-- Standard = fails with NULL
SELECT NULL = NULL;        -- NULL (not true!)

-- NULL-safe equality
SELECT NULL IS NOT DISTINCT FROM NULL;  -- true
SELECT 1   IS NOT DISTINCT FROM 1;      -- true
SELECT 1   IS NOT DISTINCT FROM NULL;   -- false

Common Pitfalls

Pitfall	Consequence	Prevention
col = NULL	Always returns nothing	Use IS NULL or IS DISTINCT FROM NULL
Comparing text with >	Lexicographic (not numeric) comparison	Cast to numeric first: col::INTEGER > 100
Using = for approximate float comparison	Float precision errors	Use abs(a - b) < 0.0001 for floats

Quick Reference

WHERE col = val
WHERE col != val
WHERE col > val
WHERE col BETWEEN a AND b
WHERE col IS DISTINCT FROM NULL
WHERE col IS NOT DISTINCT FROM NULL

What's Next

• Next: IN, BETWEEN, LIKE - Range and pattern filters.
• Module Overview - Return to this module index.