WHERE Clause

Overview

The WHERE clause restricts which rows a query returns. Combine conditions with AND, OR, and NOT. Handle NULL with IS NULL / IS NOT NULL—never use = NULL.

Syntax / Usage

SELECT * FROM products
WHERE price >= 10
  AND category = 'books'
  AND stock > 0;

-- IN and BETWEEN
WHERE status IN ('active', 'pending')
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'

-- NULL checks
WHERE deleted_at IS NULL

-- Pattern match
WHERE email LIKE '%@example.com'
WHERE name ILIKE 'john%'   -- case-insensitive (Postgres)

-- Subquery
WHERE user_id IN (SELECT id FROM users WHERE country = 'US')

Examples

Active subscriptions expiring soon:

SELECT *
FROM subscriptions
WHERE status = 'active'
  AND expires_at <= NOW() + INTERVAL '30 days'
  AND canceled_at IS NULL;

Optional filters (dynamic query pattern):

SELECT * FROM events
WHERE ( :city IS NULL OR city = :city )
  AND ( :min_price IS NULL OR price >= :min_price );

Common Mistakes

• WHERE column = NULL always false—use IS NULL
• Operator precedence bugs—use parentheses with mixed AND/OR
• Applying functions to indexed columns (WHERE YEAR(date) = 2024) preventing index use
• SQL injection from string concatenation—use parameterized queries

See Also

select joins group-by crud