MySQL WHERE Clause Statements

Introduction

The WHERE clause in MySQL is used to filter data based on specified conditions. It allows you to selectively retrieve rows from a table that meet specific criteria. In this article, we will explore the usage of the WHERE clause in MySQL with examples to illustrate its functionality.

Basic WHERE Clause

The basic syntax of a SELECT statement with a WHERE clause is as follows:

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

Here's an example that retrieves employees with a salary greater than 50000:

SELECT * FROM employees WHERE salary > 50000;

Comparison Operators

You can use various comparison operators in the WHERE clause to specify conditions. Here are some commonly used comparison operators:

• =: Equal to
• <> or !=: Not equal to
• <: Less than
• <=: Less than or equal to
• >: Greater than
• >=: Greater than or equal to

Here's an example that retrieves employees with an age greater than or equal to 30:

SELECT * FROM employees WHERE age >= 30;

Logical Operators

You can use logical operators (AND, OR, NOT) to combine multiple conditions in the WHERE clause. Here's an example that retrieves employees with a salary between 40000 and 60000:

SELECT * FROM employees WHERE salary >= 40000 AND salary <= 60000;

IN Operator

The IN operator allows you to specify multiple values in a condition. Here's an example that retrieves employees with specific IDs:

SELECT * FROM employees WHERE id IN (1, 3, 5);

LIKE Operator

The LIKE operator is used for pattern matching. It allows you to match a value against a pattern using wildcard characters (% and _). Here's an example that retrieves employees with names starting with 'J':

SELECT * FROM employees WHERE name LIKE 'J%';

NULL Values

You can use the IS NULL or IS NOT NULL operators to check for NULL (empty) values in a column. Here's an example that retrieves employees with no assigned department:

SELECT * FROM employees WHERE department_id IS NULL;

Conclusion

The WHERE clause is a powerful feature in MySQL for filtering data based on specific conditions. In this article, we explored the usage of the WHERE clause, including comparison operators, logical operators, the IN operator, the LIKE operator, and handling NULL values. By mastering the WHERE clause, you can efficiently retrieve and manipulate data in your MySQL databases based on your specific criteria.