WHERE Clause in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

WHERE Clause in MySQL with Examples

In this article, I am going to discuss How to use the WHERE Clause in MySQL with Examples. Please read our previous article where we discussed the SELECT Statement in MySQL. At the end of this article, you will understand the following pointers in detail.

1. What are clauses in MySQL?
2. Why we need Clauses in MySQL?
3. WHERE Clause in MySQL
4. How to use Where Clause in MySQL?
5. Where clause with a Single condition
6. Where Clause Using AND Condition
7. Where Clause using OR Condition
8. Where Clause using both AND & OR Conditions in MySQL
9. Where clause with Update Statement
10. Where clause with Delete Statement in MySQL

What are clauses in MySQL?

Before understanding the WHERE Clause, let us first discuss what are clauses in MySQL. Clauses are in-built functions available in SQL which is used to deal with data easily stored in the table. It helps us to filter and analyze the data quickly. Clauses are used to get the required data by the user when we have a large number of data stored in the database. That means if you want to provide some additional functionalities such as filtering the records, sorting the records, fetching the records, and grouping the records then you need to use the Clause with the query.

Why we need Clauses in MySQL?

MySQL Clauses are used for database access and data extraction by applying a filter that describes the conditions. If the condition is satisfied, we will get the particular result rows and the information that does not satisfy the condition is not used. So, SQL Clauses helps us to access proper info and is manageable to deal with different tables in a database.

WHERE Clause in MySQL

The where clause is an optional clause used in SQL statements. The WHERE clause acts as a filter on the rows of the result set produced by the FROM clause. It extracts only those records that fulfill the specified condition. The WHERE clause mainly depends upon a condition that evaluates as either be true, false, or unknown. So, if you want to limit the number of rows to be affected by your DML statement (SELECT, INSERT, UPDATE, or DELETE), then you need to use the Where Clause in MySQL.

Examples to Understand WHERE Clause in MySQL:

We are going to use the following Employees table to understand the need and use of the MySQL Where clause with Examples.

Please use the below SQL Script to create the database company and employees table with the required data.

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
  Id INT PRIMARY KEY,
  Name VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL,
  City VARCHAR(45) NOT NULL
);

INSERT INTO employees (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employees (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employees (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'Delhi');
INSERT INTO employees (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO employees (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO employees (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employees (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employees (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO employees (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO employees (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');

Once you execute the above SQL statements, then it will create the company database and inside it will create the Employees table as shown in the below image.

How to use Where Clause in MySQL?

The following is the syntax to use Where Clause in MySQL Database.
Syntax: WHERE conditions;
Conditions: The conditions that must be met for rows to be selected.

Where clause with a Single condition in MySQL

Let us start with the very basic example i.e. using only a single condition in the where clause. In the below example, we used the where clause to filter the results from the Employees table. The below Select statement will return all the records from the Employees table whose Department is HR. As we use * before the FROM clause, the Select Statement will return all the columns from the Employees table. While executing the Select statement, please select the company database.

SELECT * FROM employees WHERE Department = ‘HR’;

When you execute the above SELECT statement, it will return all the employees of the HR Department as shown in the below image.

Where Clause Using AND Condition in MySQL:

Now we will see how to use the WHERE clause with AND condition in MySQL. Our requirement is to fetch all the employees from the employees table whose Gender is Male AND Salary is greater than 35000. That means we need to write an SQL Statement that should return the following records as output.

In order to get the above output, we need to use the WHERE clause along with the AND Operator. We generally use the AND operator to define multiple conditions. In our case, we are going to define two conditions (Gender = Male and Salary > 3500) using the AND operator as shown in the below SQL Statements.

SELECT * FROM employees WHERE Gender = ‘Male’ AND Salary > 35000;

Note: When using AND operator, if both the conditions are satisfied then only it returns the records. If one of the conditions is false then it will not include that records in the result set.

Where Clause using OR Condition in MySQL:

Now we will see how to use the WHERE clause with the OR condition. The following WHERE clause example uses OR Operator to define multiple conditions. The following SQL Query will return all the employees whose Gender is Female or Department is IT.

SELECT * FROM employees WHERE Gender = ‘Female’ OR Department = ‘IT’;

When you execute the above SELECT Statement, then it will return the following result set which includes all Female Employees as well as all Employees who belong to IT Department.

Where Clause using both AND & OR Conditions in MySQL

It is also possible to use both “AND” & “OR” conditions in the WHERE in a single SQL statement. The following example uses the WHERE clause to define multiple conditions using AND & OR conditions. The following SQL Query will return all employees either whose department is IT or Gender is Female and Salary is greater than 35000.

SELECT * FROM employees WHERE (Gender = ‘Female’ OR Department = ‘IT’) AND Salary > 35000;

When you execute the above SQL statement, it will return the following result set.

Where clause with Update Statement in MySQL:

It is also possible in the MySQL database to use the Where clause in the UPDATE Statement i.e. to update data based on some conditions. Let us understand this with an example. Our requirement is to increase the Salary of All IT department employees by 2000. Before updating the Salary of IT Department employees, let’s first have a look at the current Salaries of those employees by executing the below SQL Statement.

SELECT * FROM employees WHERE Department = ‘IT’;

It will give you the following result.

Now, execute the below UPDATE SQL Statement along with the WHERE Clause and see the result.

UPDATE employees SET Salary = Salary + 2000 WHERE Department = ‘IT’;

It might be possible that while executing the above UPDATE statement, you may get the following error.

As you can see in the above image, it is complaining that you are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. That means you are updating the table without using the primary key column in the where clause. To avoid the above error, we need to SET SQL_SAFE_UPDATES=0; So, execute the UPDATE statement as shown below.

SET SQL_SAFE_UPDATES=0;
UPDATE employees SET Salary = Salary + 2000 WHERE Department = ‘IT’;

Now, you will not get any error and it will update the data as expected in the Employees table. To verify the same, again execute the below SELECT Statement.

SELECT * FROM employees WHERE Department = ‘IT’;

As you can see in the above image, the Salaries of IT Department employees are increased by 2000 as expected.

Where clause with Delete Statement in MySQL:

It is also possible in the MySQL database to use the Where clause in DELETE Statement i.e. to delete data based on some conditions. Let us understand this with an example. Our requirement is to delete all the HR department employees. The following DELETE SQL statement will do the same.

DELETE FROM employees WHERE Department = ‘HR’;

Once you execute the above statement, then it will delete all the HR Department employees from the Employees table. To verify the same, execute the below SQL Statement.

SELECT * FROM employees;

Once you execute the above SQL SELECT Statement, you will get the following result and you can see all the HR Department employees are deleted from the table.

In the next article, I am going to discuss Group by Clause in MySQL with Examples. Here, in this article, I try to explain the How to use the WHERE Clause in MySQL with Examples. I hope you enjoy this article. Please give your feedback and suggestions about this WHERE Clause in the MySQL article.