Best Practices for WHERE Clauses
You'll learn how to write efficient, maintainable WHERE clauses that make your queries faster and easier to understand.
Now that you know how to filter data using comparison operators, wildcards, and date functions, it's time to learn how to write filters that perform well and remain readable as your queries grow more complex.
Use Indexed Columns When Possible
Think of indexed columns like a book's index—they let the database jump directly to matching rows instead of reading every single record. When you filter on indexed columns (usually primary keys, foreign keys, or commonly-searched fields), your queries run much faster.
-- Good: Filtering on indexed customer_id
SELECT * FROM orders WHERE customer_id = 1001;
-- Slower: Filtering on non-indexed description text
SELECT * FROM orders WHERE LOWER(notes) LIKE '%urgent%';
Avoid Functions on Filtered Columns
Wrapping columns in functions (like UPPER(), YEAR(), or math operations) prevents the database from using indexes efficiently. Instead, transform the comparison value.
-- Bad: Function wraps the column
SELECT * FROM orders WHERE YEAR(order_date) = 2023;
-- Better: Use date range
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
Write Clear, Readable Conditions
Use simple, explicit comparisons. Avoid complex nested logic when possible—it's harder for both humans and databases to process.
-- Clear and direct
SELECT * FROM products WHERE price > 100 AND category = 'Electronics';
Key Takeaway: Write WHERE clauses that filter on indexed columns, avoid wrapping filtered columns in functions, and keep conditions simple and readable—your queries will run faster and be easier to maintain.