Course contentsShow
← Data Science
Lesson 864 of 2,14522. SQL FoundationsFree lesson

Best Practices for WHERE Clauses

Write efficient filters by using indexed columns, avoiding functions on columns, and writing clear conditions.

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.