Filtering tables is an essential skill that every database user needs to master. In this guide, we’ll walk through various ways to filter data in MySQL Workbench, from basic WHERE clauses to more advanced filtering techniques.

Basic Filtering with WHERE Clause

The WHERE clause is your primary tool for filtering data. Here’s how to use it:

SELECT * FROM employees
WHERE salary > 50000;

This simple query shows all employees with salaries above $50,000. But let’s explore more practical examples:

Multiple Conditions

Combine conditions using AND and OR:

SELECT first_name, last_name, department, salary
FROM employees
WHERE department = 'Sales'
AND salary >= 60000
AND hire_date >= '2023-01-01';

Pattern Matching with LIKE

Search for specific patterns in text:

SELECT product_name, category, price
FROM products
WHERE product_name LIKE '%iPhone%'
OR description LIKE '%Apple%';

Advanced Filtering Techniques

Using IN Operator

Filter by multiple possible values:

SELECT * FROM orders
WHERE status IN ('Pending', 'Processing', 'Shipped')
AND order_date BETWEEN '2024-01-01' AND '2024-12-31';

NULL Value Filtering

Find or exclude NULL values:

SELECT customer_id, email
FROM customers
WHERE phone_number IS NULL
OR secondary_email IS NOT NULL;

Best Practices for Filtering

1. Always use appropriate indexing on filtered columns for better performance
2. Use parameterized queries when filtering with variable values
3. Consider using EXPLAIN to analyze query performance
4. Be careful with wildcards at the start of LIKE patterns as they can slow down queries

Practical Examples

Filter Date Ranges

SELECT 
    order_id,
    customer_name,
    order_date,
    total_amount
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
ORDER BY order_date DESC;

 Complex Filtering with Subqueries

SELECT 
    p.product_name,
    p.price,
    p.stock_quantity
FROM products p
WHERE p.category_id IN (
    SELECT category_id 
    FROM categories 
    WHERE parent_category = 'Electronics'
)
AND p.price > (
    SELECT AVG(price) 
    FROM products
);

Troubleshooting Common Issues

• If your filter isn’t working, check for:
  • Case sensitivity in string comparisons
  • Date format mismatches
  • Trailing spaces in text fields
  • NULL vs. empty string differences

Performance Tips

When filtering large tables:

1. Use indexes on commonly filtered columns
2. Avoid using functions on filtered columns
3. Consider using LIMIT with ORDER BY for pagination
4. Use EXISTS instead of IN for better performance with subqueries

Read also: MySQL Interview Questions and Answers