SQL WHERE Clause Filtering Techniques

SQL WHERE Clause Filtering Techniques

The WHERE clause filters rows based on specified conditions, allowing you to retrieve only the data that meets your criteria. It is applied before grouping and aggregation.

Basic Comparison Operators

SELECT * FROM products
WHERE price > 100
  AND category = 'Electronics'
  AND stock <> 0;

Supported operators: =, <> or !=, <, >, <=, >=.

Range Filtering with BETWEEN

SELECT * FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';

BETWEEN is inclusive on both ends. It works with numbers, dates, and strings.

Set Membership with IN

SELECT * FROM employees
WHERE department IN ('Engineering', 'Design', 'Product');

IN is cleaner than multiple OR conditions and can also accept a subquery.

Pattern Matching with LIKE

SELECT * FROM users
WHERE email LIKE '%@gmail.com'
  AND name LIKE 'J___';

% matches any sequence of characters; _ matches exactly one character.

NULL Handling

SELECT * FROM contacts
WHERE phone IS NOT NULL
  AND fax IS NULL;

Never use = NULL or <> NULL. Always use IS NULL or IS NOT NULL.

Compound Conditions

Use AND, OR, and NOT with parentheses to control evaluation order:

SELECT * FROM products
WHERE (category = 'Books' OR category = 'Music')
  AND NOT discontinued
  AND price BETWEEN 10 AND 50;

Performance Tips

• Avoid wrapping indexed columns in functions (WHERE YEAR(date_col) = 2025) as it prevents index usage
• Use EXISTS instead of IN with large subqueries for better performance
• Be mindful of implicit type conversions that can prevent index usage
• Place the most selective conditions first when possible

The WHERE clause is the primary mechanism for filtering rows and is essential for writing efficient, precise SQL queries.