DocsFundamentalsWHERE Clause

WHERE Clause

Fundamentals

Concept

The WHERE clause filters rows before they appear in your result set. It evaluates a Boolean expression for every row in the source table and only keeps rows where the expression is true. This is the primary mechanism for narrowing down large datasets to exactly the records you need.

Conditions in WHERE can use comparison operators (=, <>, <, >, <=, >=), pattern matching (LIKE, ILIKE), range checks (BETWEEN), set membership (IN), and NULL checks (IS NULL, IS NOT NULL). You can combine multiple conditions with AND and OR, and negate them with NOT.

Because WHERE runs before GROUP BY and before SELECT aliases are resolved, it operates on raw table columns and cannot reference aggregates or column aliases. If you need to filter grouped results, use HAVING instead.

Syntax

-- Basic comparison
SELECT * FROM orders
WHERE status = 'shipped';

-- Multiple conditions
SELECT * FROM products
WHERE price >= 10
  AND price <= 100
  AND category = 'Electronics';

-- Pattern matching & NULL check
SELECT * FROM customers
WHERE email LIKE '%@gmail.com'
  AND phone IS NOT NULL;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Find high-value orders placed in the last 30 days
SELECT
  order_id,
  customer_id,
  total_amount,
  order_date
FROM orders
WHERE total_amount > 500
  AND order_date >= CURRENT_DATE - INTERVAL '30 days';

-- Products that are either on sale or in a specific category
SELECT name, price, category
FROM products
WHERE category IN ('Books', 'Music')
   OR price < 5.00;

Common Pitfalls & Tips

1Use IS NULL / IS NOT NULL instead of = NULL. Comparing with = NULL always returns unknown, not true or false.
2Be careful with OR precedence — wrap OR groups in parentheses when combining with AND to avoid unexpected results.
3LIKE patterns are case-sensitive in many databases. Use ILIKE (PostgreSQL) or LOWER() for case-insensitive matching.

Practice WHERE filtering