How to filter data with WHERE clause

· Category: SQL & Databases

Short answer

The WHERE clause follows FROM and filters rows by evaluating a boolean expression for each row.

Steps

  1. Basic equality: SELECT * FROM products WHERE category = 'Electronics';
  2. Range checks: SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';
  3. List membership: SELECT * FROM customers WHERE country IN ('USA', 'Canada');
  4. Pattern matching: SELECT * FROM users WHERE email LIKE '%@example.com';
  5. Combine conditions: SELECT * FROM products WHERE price > 100 AND stock > 0;

Tips

  • Place indexed columns first in composite conditions to help the optimizer.
  • Use IS NULL and IS NOT NULL for null checks; = NULL does not work as expected.

Common issues

  • Using OR without parentheses can produce unexpected row sets due to operator precedence.
  • Functions on indexed columns in WHERE prevent index usage and cause full table scans.r