MySQL WHERE
Filtering data with conditions
🎯 What is WHERE?
The WHERE clause filters records based on specific conditions. It lets you retrieve only the data you need by specifying criteria. WHERE works with SELECT, UPDATE, and DELETE statements for precise data operations.
-- Filter records with WHERE
SELECT * FROM products WHERE price > 100;
WHERE Clause Operators
Comparison
Use =, !=, <, >, <=, >= to compare values. These operators filter numeric, text, and date data. Equal (=) finds exact matches while not equal (!=) excludes specific values from results.
LIKE Pattern
LIKE searches for patterns in text. Use % for multiple characters and _ for single character. Perfect for partial matches like finding names starting with 'A' or emails containing specific domains.
IN & BETWEEN
IN checks if value matches any in a list. BETWEEN filters values within a range. These operators simplify queries by replacing multiple OR conditions, making code cleaner and more readable.
AND & OR
Combine multiple conditions with AND (all must be true) or OR (any can be true). Use parentheses to control logic order. These logical operators create complex filters for precise data retrieval.
🔹 Basic WHERE Conditions
WHERE uses comparison operators to filter data. You can compare numbers, text, and dates. The condition follows the WHERE keyword and determines which rows appear in your results. Only matching rows are returned.
-- Equal to
SELECT * FROM employees WHERE department = 'Sales';
-- Greater than
SELECT * FROM products WHERE price > 50;
-- Less than or equal
SELECT * FROM orders WHERE quantity <= 10;
-- Not equal
SELECT * FROM customers WHERE city != 'New York';Output (price > 50):
| product_id | product_name | price |
|---|---|---|
| 1 | Laptop | 899.99 |
| 3 | Monitor | 299.99 |
🔹 AND & OR Operators
Combine multiple conditions using AND and OR. AND requires all conditions to be true. OR requires at least one condition to be true. Use parentheses to control the order of evaluation for complex logic.
-- AND: Both conditions must be true
SELECT * FROM products
WHERE price > 100 AND category = 'Electronics';
-- OR: Either condition can be true
SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing';
-- Combining AND & OR with parentheses
SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Computers')
AND price < 1000;Output:
| product_name | category | price |
|---|---|---|
| Laptop | Electronics | 899.99 |
🔹 LIKE Pattern Matching
LIKE searches for patterns in text columns. The percent sign (%) matches any number of characters. The underscore (_) matches exactly one character. This is essential for flexible text searches and partial matching.
-- Starts with 'A'
SELECT * FROM customers WHERE name LIKE 'A%';
-- Ends with 'son'
SELECT * FROM employees WHERE last_name LIKE '%son';
-- Contains 'tech'
SELECT * FROM products WHERE description LIKE '%tech%';
-- Second letter is 'a'
SELECT * FROM cities WHERE city_name LIKE '_a%';Output (name LIKE 'A%'):
| customer_id | name |
|---|---|
| 2 | Alice Johnson |
| 5 | Andrew Smith |
🔹 IN Operator
IN checks if a value matches any value in a list. It's a shorthand for multiple OR conditions. This makes queries cleaner and easier to read when checking against multiple possible values.
-- Using IN
SELECT * FROM employees
WHERE department IN ('Sales', 'Marketing', 'HR');
-- Equivalent to:
SELECT * FROM employees
WHERE department = 'Sales'
OR department = 'Marketing'
OR department = 'HR';
-- IN with numbers
SELECT * FROM products
WHERE product_id IN (1, 5, 10, 15);Output:
| emp_id | name | department |
|---|---|---|
| 1 | John Doe | Sales |
| 3 | Jane Smith | Marketing |
🔹 BETWEEN Operator
BETWEEN filters values within a range (inclusive). It works with numbers, dates, and text. This operator simplifies range queries and makes your SQL more readable than using >= and <= operators together.
-- Numbers between range
SELECT * FROM products
WHERE price BETWEEN 100 AND 500;
-- Date range
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
-- Equivalent to:
SELECT * FROM products
WHERE price >= 100 AND price <= 500;Output:
| product_name | price |
|---|---|
| Monitor | 299.99 |
| Keyboard | 149.99 |
🔹 IS NULL & IS NOT NULL
Check for NULL (empty) values using IS NULL. NULL represents missing or unknown data. Use IS NOT NULL to find records with actual values. Regular comparison operators don't work with NULL values.
-- Find records with NULL values
SELECT * FROM employees
WHERE phone_number IS NULL;
-- Find records with values (not NULL)
SELECT * FROM customers
WHERE email IS NOT NULL;
-- Combining with other conditions
SELECT * FROM products
WHERE description IS NOT NULL AND price > 50;Output:
3 rows returned with non-NULL email addresses