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

  1. Open MySQL Workbench.
  2. Write a query with a WHERE clause in the SQL editor.
  3. Click Execute to run the query.
  4. 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
Avoid vague or overly broad conditions that retrieve unnecessary rows..

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
Ensure proper use of AND and OR to avoid unintended results.

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 match NULL. Use IS NULL instead.

MySQL WHERE Clause

Quiz: Test Your Knowledge