What is the MySQL WHERE Clause?
The WHERE
clause in MySQL is used to filter rows based on specific conditions. It allows you to retrieve only the rows that meet the criteria defined in the condition.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
column1, column2, ...
: Columns to select.table_name
: The name of the table.condition
: The condition that filters the rows.
Examples of Using WHERE Clause
1. Filter Rows Based on a Single Condition
Query:
SELECT * FROM employees WHERE salary > 50000;
Output Example:
+----+---------+--------+
| id | name | salary |
+----+---------+--------+
| 2 | Bob | 55000 |
| 3 | Charlie | 60000 |
+----+---------+--------+
2. Using Multiple Conditions with AND
Combine multiple conditions using the AND
keyword.
Query:
SELECT * FROM employees WHERE salary > 50000 AND department = 'HR';
Output Example:
+----+---------+--------+-------------+
| id | name | salary | department |
+----+---------+--------+-------------+
| 3 | Charlie | 60000 | HR |
+----+---------+--------+-------------+
3. Using Multiple Conditions with OR
Retrieve rows that meet at least one of the specified conditions using the OR
keyword.
Query:
SELECT * FROM employees WHERE salary > 50000 OR department = 'Finance';
4. Using Comparison Operators
The WHERE
clause supports various comparison operators:
- Equals (
=
):
SELECT * FROM employees WHERE department = 'HR';
- Not Equals (
!=
or<>
):
SELECT * FROM employees WHERE department != 'HR';
- Greater Than (
>
), Less Than (<
):
SELECT * FROM employees WHERE salary > 40000;
5. Using LIKE for Pattern Matching
Find rows based on a pattern using LIKE
.
Query:
SELECT * FROM customers WHERE name LIKE 'A%';
%
: Matches zero or more characters._
: Matches a single character.
Output Example:
+----+-------+-------+
| id | name | city |
+----+-------+-------+
| 1 | Alice | NY |
| 2 | Adam | TX |
+----+-------+-------+
6. Using IN to Match Multiple Values
Filter rows based on a list of values.
Query:
SELECT * FROM employees WHERE department IN ('HR', 'Finance');
7. Using BETWEEN for Range Filtering
Retrieve rows within a specific range.
Query:
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;
8. Using IS NULL or IS NOT NULL
Filter rows with NULL
values.
Query:
SELECT * FROM employees WHERE department IS NULL;
Using WHERE Clause in MySQL Workbench
- Open MySQL Workbench.
- Write a query with a
WHERE
clause in the SQL editor. - Click Execute to run the query.
- View the filtered results in the result grid.
Best Practices
Index the Filtered Columns
Index the Filtered Columns
Columns frequently used in WHERE
clauses should be indexed to improve query performance.
Use Specific Conditions
Use Specific Conditions
Avoid vague or overly broad conditions that retrieve unnecessary rows..
Avoid Using Functions on Indexed Columns
Avoid Using Functions on Indexed Columns
Using functions in the WHERE
clause can negate the benefit of indexes.
Example: Avoid this:
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
Instead, use:.
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';
Use Logical Operators Carefully
Use Logical Operators Carefully
Ensure proper use of AND
and OR
to avoid unintended results.
Limit the Result Set
Limit the Result Set
Combine WHERE
with LIMIT
to retrieve only the required number of rows.
Common Issues and Troubleshooting
Incorrect Results:
Ensure the condition is written accurately and uses the correct operators.Slow Queries:
Use indexes and avoid unnecessary conditions to improve performance.NULL Handling:
Remember that=
does not matchNULL
. UseIS NULL
instead.
MySQL WHERE Clause
Quiz-summary
0 of 5 questions completed
Questions:
- 1
- 2
- 3
- 4
- 5
Information
Quiz: Test Your Knowledge
You have already completed the quiz before. Hence you can not start it again.
Quiz is loading...
You must sign in or sign up to start the quiz.
You have to finish following quiz, to start this quiz:
Results
0 of 5 questions answered correctly
Your time:
Time has elapsed
You have reached 0 of 0 points, (0)
Categories
- Not categorized 0%
- 1
- 2
- 3
- 4
- 5
- Answered
- Review
- Question 1 of 5
1. Question
What does the WHERE clause do in MySQL?
CorrectIncorrect - Question 2 of 5
2. Question
How do you retrieve rows where the salary is greater than 50000?
CorrectIncorrect - Question 3 of 5
3. Question
Which operator is used to filter rows with a range of values?
CorrectIncorrect - Question 4 of 5
4. Question
What does IS NULL do in a WHERE clause?
CorrectIncorrect - Question 5 of 5
5. Question
Which clause would retrieve rows with names starting with ‘A’?
CorrectIncorrect