MySQL WHERE Clause

The WHERE clause is used to filter records that meet specific conditions. It allows you to extract only those records that fulfill a specified criterion.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Comparison Operators

Example: Equality Operator

SELECT * FROM employees 
WHERE department = 'Sales';

Example: Greater Than

SELECT name, salary 
FROM employees 
WHERE salary > 50000;

String Patterns with LIKE

Use LIKE with wildcards:

-- Names starting with 'A'
SELECT * FROM employees WHERE name LIKE 'A%';

-- Names ending with 'son'
SELECT * FROM employees WHERE name LIKE '%son';

-- Names containing 'Smith'
SELECT * FROM employees WHERE name LIKE '%Smith%';

-- Names with 5 characters
SELECT * FROM employees WHERE name LIKE '_____';

IN Operator

Check if a value matches any value in a list:

SELECT * FROM employees 
WHERE department IN ('Sales', 'Marketing', 'IT');

SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

BETWEEN Operator

Select values within a range:

SELECT * FROM employees 
WHERE salary BETWEEN 40000 AND 60000;

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

NULL Values

Use IS NULL or IS NOT NULL:

-- Find employees without a manager
SELECT * FROM employees WHERE manager_id IS NULL;

-- Find employees with assigned departments
SELECT * FROM employees WHERE department IS NOT NULL;

Multiple Conditions

SELECT * FROM employees 
WHERE department = 'Sales' 
  AND salary > 50000 
  AND hire_date > '2023-01-01';

Practical Examples

-- Find active customers in specific cities who joined recently
SELECT 
    customer_id,
    CONCAT(first_name, ' ', last_name) AS customer_name,
    email,
    city,
    join_date,
    total_purchases
FROM customers
WHERE status = 'active'
  AND city IN ('New York', 'Los Angeles', 'Chicago')
  AND join_date >= '2024-01-01'
  AND total_purchases > 1000
ORDER BY total_purchases DESC;

-- Find products with specific criteria
SELECT 
    product_name,
    category,
    price,
    stock_quantity,
    CASE 
        WHEN price < 50 THEN 'Budget'
        WHEN price BETWEEN 50 AND 200 THEN 'Mid-range'
        ELSE 'Premium'
    END AS price_category
FROM products
WHERE (category = 'Electronics' OR category = 'Gadgets')
  AND price BETWEEN 25 AND 500
  AND stock_quantity > 0
  AND product_name LIKE '%Smart%'
ORDER BY price ASC, stock_quantity DESC;

Performance Tips

• Use indexes on columns frequently used in WHERE clauses
• Avoid using functions on indexed columns in WHERE conditions
• Use the most selective conditions first
• Be careful with NULL comparisons - they require special handling