MySQL WHERE Clause

Overview

The WHERE clause filters records based on specified conditions. It extracts only the records that fulfill a specified condition, making queries more precise and efficient.

WHERE Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Comparison Operators

Basic Comparisons

-- Equal
SELECT * FROM users WHERE status = 'active';

-- Not equal
SELECT * FROM products WHERE price <> 100;
SELECT * FROM products WHERE price != 100;

-- Greater than
SELECT * FROM orders WHERE total > 100;

-- Greater than or equal
SELECT * FROM products WHERE price >= 50;

-- Less than
SELECT * FROM products WHERE stock < 10;

-- Less than or equal
SELECT * FROM products WHERE stock <= 5;

BETWEEN

-- Between (inclusive)
SELECT * FROM products WHERE price BETWEEN 10 AND 100;

-- Date range
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- NOT BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 100;

IN

-- In list
SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');

-- With subquery
SELECT * FROM products WHERE category_id IN (
    SELECT id FROM categories WHERE name IN ('Electronics', 'Books')
);

-- NOT IN
SELECT * FROM users WHERE country NOT IN ('USA', 'UK');

Logical Operators

AND

-- Multiple conditions (all must be true)
SELECT * FROM users WHERE status = 'active' AND country = 'USA';

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

-- Combine with other operators
SELECT * FROM orders 
WHERE total > 100 
    AND status = 'completed' 
    AND created_at > '2024-01-01';

OR

-- Any condition true
SELECT * FROM users WHERE country = 'USA' OR country = 'UK';

-- Multiple OR conditions
SELECT * FROM products 
WHERE category = 'Electronics' 
    OR category = 'Books' 
    OR category = 'Clothing';

AND with OR

-- AND has higher precedence
SELECT * FROM products 
WHERE (category = 'Electronics' OR category = 'Books') 
    AND price > 50;

-- Parentheses for clarity
SELECT * FROM orders 
WHERE (status = 'completed' OR status = 'shipped') 
    AND created_at > '2024-01-01';

NOT

-- NOT equal
SELECT * FROM users WHERE NOT status = 'active';

-- NOT IN
SELECT * FROM users WHERE country NOT IN ('USA', 'UK');

-- NOT BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 100;

-- NOT LIKE
SELECT * FROM users WHERE name NOT LIKE 'A%';

-- NOT NULL
SELECT * FROM users WHERE email IS NOT NULL;

-- Complex NOT
SELECT * FROM users WHERE NOT (status = 'active' AND country = 'USA');

NULL Conditions

IS NULL / IS NULL

-- Find NULL values
SELECT * FROM users WHERE phone IS NULL;

-- Find missing relationships
SELECT * FROM orders WHERE user_id IS NULL;

-- Find incomplete records
SELECT * FROM products WHERE description IS NULL;

IS NOT NULL / IS NOT NULL

-- Exclude NULL values
SELECT * FROM users WHERE phone IS NOT NULL;

-- Only completed records
SELECT * FROM orders WHERE completed_at IS NOT NULL;

Pattern Matching

LIKE / LIKE

-- Starts with
SELECT * FROM users WHERE name LIKE 'J%';

-- Ends with
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Contains
SELECT * FROM products WHERE name LIKE '%laptop%';

-- Single character wildcard
SELECT * FROM users WHERE name LIKE 'J_n';

-- Multiple wildcards
SELECT * FROM products WHERE name LIKE '_a%a_';

NOT LIKE / NOT LIKE

-- Does not start with
SELECT * FROM users WHERE name NOT LIKE 'A%';

-- Does not contain
SELECT * FROM products WHERE name NOT LIKE '%test%';

ILIKE (Case-Insensitive)

-- MySQL LIKE is case-insensitive by default
SELECT * FROM users WHERE name LIKE 'john';  -- Matches 'John', 'JOHN', 'john'

-- For case-sensitive comparison
SELECT * FROM users WHERE name LIKE BINARY 'john';  -- Only 'john'

REGEXP

-- Starts with
SELECT * FROM users WHERE name REGEXP '^J';

-- Ends with
SELECT * FROM users WHERE email REGEXP 'com$';

-- Contains any of characters
SELECT * FROM users WHERE name REGEXP '[aeiou]';

-- Multiple options
SELECT * FROM products WHERE name REGEXP 'book|tablet|laptop';

-- Complex patterns
SELECT * FROM users WHERE phone REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';

-- NOT REGEXP
SELECT * FROM users WHERE name NOT REGEXP '[0-9]';

String Comparisons

-- Exact match
SELECT * FROM users WHERE name = 'John';

-- Case-insensitive comparison
SELECT * FROM users WHERE STRCMP(name, 'JOHN') = 0;

-- Length comparison
SELECT * FROM users WHERE LENGTH(name) > 10;

-- String functions in WHERE
SELECT * FROM users WHERE SUBSTRING(name, 1, 1) = 'A';

-- Concatenation comparison
SELECT * FROM users WHERE CONCAT(first_name, ' ', last_name) = 'John Doe';

Date and Time Filtering

-- Specific date
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';

-- Date range
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

-- Time comparison
SELECT * FROM logs WHERE TIME(created_at) > '12:00:00';

-- Year filter
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Month filter
SELECT * FROM orders WHERE MONTH(created_at) = 1;

-- Day of week
SELECT * FROM orders WHERE DAYOFWEEK(created_at) = 1;  -- Sunday

-- Date functions
SELECT * FROM users WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY);
SELECT * FROM orders WHERE created_at >= CURDATE() - INTERVAL 7 DAY;

Numerical Filtering

-- Aggregate functions in WHERE (using HAVING)
SELECT category, AVG(price) 
FROM products 
GROUP BY category 
HAVING AVG(price) > 50;

-- Subquery with aggregate
SELECT * FROM users 
WHERE id IN (
    SELECT user_id FROM orders 
    GROUP BY user_id 
    HAVING COUNT(*) > 10
);

-- Mathematical operations
SELECT * FROM products WHERE price * quantity > 1000;
SELECT * FROM orders WHERE ROUND(total, 2) > 99.99;

Combined Conditions

-- Complex query
SELECT * FROM users 
WHERE 
    (status = 'active' OR status = 'pending')
    AND country IN ('USA', 'Canada', 'UK')
    AND created_at > '2023-01-01'
    AND email LIKE '%@company.com'
    AND id NOT IN (SELECT user_id FROM blocked_users);

-- Using CASE in WHERE (advanced)
SELECT * FROM products 
WHERE price > CASE 
    WHEN category = 'Electronics' THEN 100
    WHEN category = 'Books' THEN 20
    ELSE 50
END;

Performance Considerations

Index Usage

-- Use indexed columns in WHERE
SELECT * FROM users WHERE email = 'john@example.com';  -- Good if indexed

-- Function on column prevents index use
SELECT * FROM users WHERE YEAR(created_at) = 2024;  -- Cannot use index

-- Rewrite to use index
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Query Optimization

-- Put most selective condition first
SELECT * FROM users 
WHERE status = 'active'  -- Small result set first
    AND country = 'USA';

-- Avoid SELECT *
SELECT user_id, name, email FROM users WHERE status = 'active';

-- Use LIMIT for testing
SELECT * FROM users WHERE status = 'active' LIMIT 10;

Troubleshooting

Common Issues

-- Unexpected results
-- Check: Operator precedence, parentheses

-- No results returned
-- Check: NULL comparisons (use IS NULL, not = NULL)

-- Case sensitivity
-- Check: String comparisons, collation settings

-- Date format
-- Check: Date format matches MySQL expectations (YYYY-MM-DD)

Debugging

-- Add EXPLAIN to see query plan
EXPLAIN SELECT * FROM users WHERE status = 'active';

-- Test conditions separately
SELECT COUNT(*) FROM users WHERE status = 'active';
SELECT COUNT(*) FROM users WHERE country = 'USA';

-- Check data types
SELECT * FROM users WHERE status = 1;  -- May not match if VARCHAR

Summary

WHERE clause filtering includes:

• Comparison Operators: =, <>, >, <, >=, <=
• Range Operators: BETWEEN, IN
• Logical Operators: AND, OR, NOT
• NULL Handling: IS NULL, IS NOT NULL
• Pattern Matching: LIKE, REGEXP
• **Date
• Performance: Index usage

---

Previous: SELECT Data

Next: UPDATE