Topic 14 of 52
WHERE Clause
Overview
The WHERE clause filters rows before they are returned, grouped, or processed. It is the primary mechanism for retrieving specific data and is critical for query performance when indexed columns are used.
Syntax
sql
-- Basic conditions
SELECT * FROM products WHERE price < 1000;
SELECT * FROM users WHERE email = 'priya@example.com';
SELECT * FROM orders WHERE status != 'cancelled';
-- Multiple conditions
SELECT * FROM products
WHERE
category = 'Electronics'
AND price BETWEEN 5000 AND 50000
AND is_active = TRUE;
-- OR conditions (use parentheses!)
SELECT * FROM users
WHERE (role = 'admin' OR role = 'manager')
AND is_active = TRUE;
-- NULL checks
SELECT * FROM orders WHERE delivered_at IS NULL;
SELECT * FROM orders WHERE delivered_at IS NOT NULL;
-- Pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- IN list
SELECT * FROM products WHERE category IN ('Electronics', 'Laptops', 'Mobile');Common Pitfalls
- WHERE is evaluated BEFORE GROUP BY and HAVING — you cannot use aggregate functions in WHERE (use HAVING instead).
- Using functions on indexed columns in WHERE defeats the index: WHERE YEAR(created_at) = 2025 vs WHERE created_at >= '2025-01-01'.
- Interview tip: NULL comparisons must use IS NULL / IS NOT NULL — column = NULL is always false (NULL is not equal to anything, even itself).
Real-World Example
Complex WHERE filtering for a reporting dashboard:
example
sql
-- Orders report: pending/processing orders from past 7 days above ₹500
SELECT
o.id,
u.name AS customer,
o.status,
o.total_amount,
o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE
o.status IN ('pending', 'processing')
AND o.total_amount >= 500
AND o.created_at >= NOW() - INTERVAL '7 days'
AND u.deleted_at IS NULL
AND o.id NOT IN (
SELECT order_id FROM flagged_orders WHERE reason = 'fraud'
)
ORDER BY o.total_amount DESC, o.created_at DESC;