SQL Comparison Operators

Comparison operators evaluate two values and return TRUE, FALSE, or NULL. They’re the building blocks of WHERE clauses, HAVING clauses, and CASE WHEN expressions.

Equality and Inequality

-- Equal to
WHERE status = 'active'
WHERE quantity = 10
WHERE created_at = '2025-06-15'

-- Not equal to (both forms work the same)
WHERE status != 'cancelled'
WHERE status <> 'cancelled'

String comparison: case-sensitive in PostgreSQL, case-insensitive in MySQL by default.

-- PostgreSQL: 'Active' != 'active'
WHERE LOWER(status) = 'active'   -- case-insensitive in PostgreSQL
WHERE status ILIKE 'active'       -- alternative for strings

Greater Than and Less Than

-- Numeric comparisons
WHERE price > 100          -- strictly greater than
WHERE price >= 100         -- greater than or equal (includes 100)
WHERE price < 500          -- strictly less than
WHERE price <= 499.99      -- less than or equal

-- Date comparisons
WHERE order_date > '2025-01-01'
WHERE order_date >= '2025-01-01'   -- includes Jan 1st
WHERE created_at < NOW()

-- String comparisons (lexicographic/alphabetical)
WHERE name > 'M'   -- names starting with N or later
WHERE status < 'z' -- technically works but not idiomatic

BETWEEN: Range Comparison

BETWEEN is shorthand for >= AND <= — both endpoints are inclusive:

WHERE price BETWEEN 50 AND 200
-- Exactly equivalent to:
WHERE price >= 50 AND price <= 200

-- Date range (includes both endpoints)
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'

-- NOT BETWEEN
WHERE price NOT BETWEEN 50 AND 200

Caution with TIMESTAMP: BETWEEN '2025-01-01' AND '2025-01-31' includes only up to midnight on Jan 31 — it won’t catch orders placed during Jan 31. Better to use:

WHERE order_date >= '2025-01-01' AND order_date < '2025-02-01'

LIKE and Wildcards

LIKE matches string patterns:

WHERE name LIKE 'A%'          -- starts with 'A' (any number of chars after)
WHERE name LIKE '%son'        -- ends with 'son'
WHERE name LIKE '%Smith%'     -- contains 'Smith' anywhere
WHERE code LIKE 'ABC-___'     -- 'ABC-' followed by exactly 3 characters
WHERE email LIKE '__%@%.%'    -- basic email pattern

% = any number of characters (including zero) _ = exactly one character

-- NOT LIKE
WHERE email NOT LIKE '%@gmail.com'

-- ESCAPE: treat wildcard as literal
WHERE filename LIKE '50\%_discount' ESCAPE '\'
-- matches '50%_discount' literally

Case sensitivity: LIKE is case-sensitive in PostgreSQL. Use ILIKE for case-insensitive matching:

WHERE name ILIKE '%smith%'        -- PostgreSQL
WHERE LOWER(name) LIKE '%smith%'  -- portable version

IS NULL and IS NOT NULL

NULL represents “unknown value” — not zero, not empty. Standard comparison operators never match NULL:

-- Correct: check for NULL
WHERE phone IS NULL
WHERE phone IS NOT NULL

-- Wrong: these never match
WHERE phone = NULL    -- always FALSE (NULL = NULL is NULL, not TRUE)
WHERE phone != NULL   -- always FALSE

-- Find rows with missing data
SELECT * FROM customers WHERE phone IS NULL;

-- Find rows with complete data
SELECT * FROM customers
WHERE phone IS NOT NULL AND email IS NOT NULL;

IN: Membership Test

IN checks whether a value appears in a list:

WHERE status IN ('active', 'pending', 'processing')

-- NOT IN
WHERE country NOT IN ('US', 'CA', 'MX')

-- IN with subquery
WHERE category_id IN (SELECT category_id FROM featured_categories)

IN is equivalent to multiple OR conditions but more concise.

NULL behavior with NOT IN: If any value in the list is NULL, NOT IN returns no rows for all outer rows (because x != NULL is NULL). Avoid NOT IN with subqueries that might return NULLs — use NOT EXISTS instead.

Combining Comparison Operators

-- Range with exclusion
WHERE price >= 50 AND price < 500 AND price != 99.99

-- Multiple conditions
WHERE category = 'electronics'
  AND price BETWEEN 100 AND 1000
  AND status IN ('active', 'featured')
  AND last_sold_date IS NOT NULL

-- Negated conditions
WHERE NOT (status = 'cancelled' OR amount < 0)
-- Same as:
WHERE status != 'cancelled' AND amount >= 0

Comparison Operators in CASE WHEN

SELECT
    product_id,
    name,
    price,
    CASE
        WHEN price > 1000 THEN 'Premium'
        WHEN price BETWEEN 100 AND 1000 THEN 'Mid-range'
        WHEN price < 100 THEN 'Budget'
        ELSE 'Unknown'
    END AS tier
FROM products;

Comparison Operators with Different Data Types

Type       | = and != | > and < | BETWEEN | LIKE | IS NULL
───────────────────────────────────────────────────────────
Numbers    | Yes      | Yes     | Yes     | No   | Yes
Strings    | Yes      | Yes*    | Yes*    | Yes  | Yes
Dates      | Yes      | Yes     | Yes     | No   | Yes
Timestamps | Yes      | Yes     | Yes**   | No   | Yes
Boolean    | Yes      | No      | No      | No   | Yes
NULL       | Never    | Never   | Never   | Never| IS (NOT) NULL

* String comparisons are alphabetical/lexicographic
** See BETWEEN timestamp caution above

Practical Examples

Filter by date range:

SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2025-07-01';

Find records in a specific status with a minimum amount:

SELECT order_id, customer_id, amount
FROM orders
WHERE status IN ('pending', 'processing')
  AND amount >= 100
ORDER BY amount DESC;

Exclude test accounts:

SELECT * FROM users
WHERE email NOT LIKE '%@test.com'
  AND email NOT LIKE '%@example.com'
  AND is_internal = FALSE;