MySQL WHERE Clause

The WHERE clause in MySQL is used to filter the results of a SELECT, UPDATE, or DELETE statement based on specified conditions. It allows you to specify one or more conditions that must be met in order for a row to be included in the result set.

Here’s an example of using the WHERE clause in a SELECT statement to retrieve only the rows that meet certain criteria:

Suppose we have a table called employees with the following columns: id, name, age, gender, salary.

To retrieve all the employees whose age is greater than 30, we can use the following SQL query:

SELECT * FROM employees WHERE age > 30;

This query will return all the rows from the employees table where the age column is greater than 30.

We can also use the WHERE clause with other operators like =, <, <=, >=, <> (not equal), and BETWEEN. For example, to retrieve all the female employees with a salary greater than 50000, we can use the following query:

SELECT * FROM employees WHERE gender = 'female' AND salary > 50000;

This query will return all the rows from the employees table where the gender column is ‘female’ and the salary column is greater than 50000.

Operators in The WHERE Clause

The WHERE clause in MySQL uses different operators to compare the values of columns with the specified conditions. These operators include:

Comparison operators

Comparison operators are used to compare the values in a column with a specified value. The available comparison operators in MySQL are:

= (equal to)
<> or != (not equal to)
< (less than)
> (greater than)
<= (less than or equal to)
>= (greater than or equal to)

For example, the following query selects all the employees whose age is less than 30:

SELECT * FROM employees WHERE age < 30;

Logical operators

Logical operators are used to combine multiple conditions in the WHERE clause. The available logical operators in MySQL are:

AND: returns rows where all conditions are true
OR: returns rows where at least one condition is true
NOT: negates the result of a condition

For example, the following query selects all the employees whose age is less than 30 and whose salary is greater than 50000:

SELECT * FROM employees WHERE age < 30 AND salary > 50000;

IN operator

The IN operator is used to specify multiple values in a single condition. It is used to test if any value in a set of values matches a specified value.

For example, the following query selects all the employees whose age is either 25, 30, or 35:

SELECT * FROM employees WHERE age IN (25, 30, 35);

LIKE operator

The LIKE operator is used for pattern matching of a string value. It is used to search for a specified pattern in a column.

For example, the following query selects all the employees whose name starts with “J”:

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

These are some of the common operators used in the WHERE clause in MySQL.

Share on Facebook

Save