Filtering

WHERE column = | <> | < | > | <= | >= value

SELECT product_name, price
FROM products
WHERE price >= 50.00 AND price < 200.00;

-- Products priced from 50 up to (not including) 200

Note Use <> for not-equal (ANSI standard). != works in MySQL and PostgreSQL but is not part of the standard.

WHERE condition1 AND condition2
WHERE condition1 OR condition2
WHERE NOT condition

SELECT first_name, city, is_active
FROM users
WHERE (city = 'Seattle' OR city = 'Portland')
  AND NOT is_active = false;

-- Active users in Seattle or Portland

Note AND binds tighter than OR. Always use parentheses to make precedence explicit. WHERE a OR b AND c means WHERE a OR (b AND c), which trips people up.

WHERE column IN (value1, value2, ...)

SELECT order_id, status
FROM orders
WHERE status IN ('pending', 'processing', 'shipped');

-- Orders with any of the three listed statuses

Note IN is shorthand for multiple OR conditions. If the list contains a NULL, it will not match NULL rows — use IS NULL separately. You can also use a subquery inside IN.

WHERE column NOT IN (value1, value2, ...)

SELECT product_name
FROM products
WHERE category_id NOT IN (5, 12, 18);

-- Products not in categories 5, 12, or 18

Note Dangerous with NULLs: if any value in the list is NULL, NOT IN returns no rows at all. This is because NULL comparisons yield UNKNOWN, and NOT UNKNOWN is still UNKNOWN.

WHERE column BETWEEN low AND high

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';

-- Orders placed during the entire year 2025

Note BETWEEN is inclusive on both ends. For timestamps, BETWEEN '2025-01-01' AND '2025-12-31' misses anything after midnight on Dec 31. Use < '2026-01-01' instead for date ranges with times.

WHERE column LIKE 'pattern'
-- % = any number of characters
-- _ = exactly one character

SELECT first_name, email
FROM users
WHERE email LIKE '%@gmail.com';

-- All users with Gmail addresses

Note LIKE is case-sensitive in PostgreSQL but case-insensitive in MySQL (with default collation). PostgreSQL offers ILIKE for case-insensitive matching. A leading % prevents index usage.

WHERE column IS NULL
WHERE column IS NOT NULL

SELECT first_name, phone
FROM users
WHERE phone IS NOT NULL;

-- Users who have provided a phone number

Note Never use = NULL or <> NULL. These always evaluate to UNKNOWN and return no rows. NULL is not a value — it represents the absence of a value, so only IS NULL / IS NOT NULL work.

WHERE EXISTS (subquery)

SELECT u.first_name, u.email
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
  AND o.total_amount > 500
);

-- Users who have at least one order over 500

Note EXISTS stops scanning as soon as it finds one matching row, making it efficient. It generally outperforms IN with large subquery results. The SELECT list inside EXISTS is irrelevant — SELECT 1 is conventional.

WHERE column > ANY (subquery)
WHERE column > ALL (subquery)

SELECT product_name, price
FROM products
WHERE price > ALL (
  SELECT AVG(price)
  FROM products
  GROUP BY category_id
);

-- Products priced above every category's average

Note ANY means the condition must be true for at least one value from the subquery. ALL means it must be true for every value. If the subquery returns an empty set, ALL conditions are true and ANY conditions are false.

WHERE CASE WHEN condition THEN result ... END

SELECT order_id, total_amount, customer_type
FROM orders
WHERE total_amount > CASE customer_type
  WHEN 'wholesale' THEN 1000
  WHEN 'retail' THEN 100
  ELSE 50
END;

-- Filters with different thresholds per customer type

Note You can use CASE in WHERE to apply different filter logic per row. However, this prevents index usage. Consider restructuring into separate OR conditions if performance matters.